public void BuildUp() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
create function rest__post_return_body(_body json) returns json as $$
begin
return _body;
end $$ language plpgsql;
create function rest__post_return_body_additional(_body json, _additional text) returns json as $$
begin
return _body::jsonb || format('{""additional"": ""%s""}', _additional)::jsonb;
end $$ language plpgsql;
create function rest__put_return_body(_body json) returns json as $$
begin
return _body;
end $$ language plpgsql;
create function rest__put_return_body_additional(_body json, _additional text) returns json as $$
begin
return _body::jsonb || format('{""additional"": ""%s""}', _additional)::jsonb;
end $$ language plpgsql;
create function rest__post_return_plain_text(_body text) returns text as $$
begin
return _body;
end $$ language plpgsql;
create function rest__put_return_plain_text(_body text) returns text as $$
begin
return _body;
end $$ language plpgsql;
");
public void TearDown() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
drop function rest__get_values_no_grant();
drop function rest__get_values_with_grant();
drop function rest__get_values_with_grant_param(text);
drop table test_values;
");
public void TearDown() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
drop function rest__get_values_from_params(int, text, timestamp, text);
drop function rest__post_values_from_params(int, text, timestamp, text);
drop function rest__put_values_from_params(int, text, timestamp, text);
drop function rest__delete_values_from_params(int, text, timestamp, text);
");
public void TearDown() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
drop function rest__get_test_route_name();
drop function rest__post_test_route_name();
drop function rest__put_test_route_name();
drop function rest__delete_test_route_name();
");
public void BuildUp() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
create function rest__get_test_route_name() returns json as $$ begin return '{}'; end $$ language plpgsql;
create function rest__post_test_route_name() returns json as $$ begin return '{}'; end $$ language plpgsql;
create function rest__put_test_route_name() returns json as $$ begin return '{}'; end $$ language plpgsql;
create function rest__delete_test_route_name() returns json as $$ begin return '{}'; end $$ language plpgsql;
");
public void TearDown() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
drop function rest__get_test_logging_info();
drop function rest__get_test_logging_notice();
drop function rest__get_test_logging_log();
drop function rest__get_test_logging_warn();
drop function rest__get_test_logging_debug();
drop function rest__get_test_logging_error();
");
public void BuildUp() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
create function rest__get_test_logging_info() returns json as
$$
begin
raise info 'info log';
return '{}';
end
$$ language plpgsql;
create function rest__get_test_logging_notice() returns json as
$$
begin
raise notice 'notice log';
return '{}';
end
$$ language plpgsql;
create function rest__get_test_logging_log() returns json as
$$
begin
set client_min_messages to 'log';
raise log 'log log';
return '{}';
end
$$ language plpgsql;
create function rest__get_test_logging_warn() returns json as
$$
begin
raise warning 'warn log';
return '{}';
end
$$ language plpgsql;
create function rest__get_test_logging_debug() returns json as
$$
begin
set client_min_messages to 'debug';
raise warning 'debug log';
return '{}';
end
$$ language plpgsql;
create function rest__get_test_logging_error() returns json as
$$
begin
raise exception 'error log';
return '{}';
end
$$ language plpgsql;
");
public void TearDown() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
drop function rest__post_return_body(json);
drop function rest__post_return_body_additional(json, text);
drop function rest__put_return_body(json);
drop function rest__put_return_body_additional(json, text);
drop function rest__post_return_plain_text(text);
drop function rest__put_return_plain_text(text);
");
public void TearDown() => DatabaseFixture.ExecuteCommand(ConnectionType.Testing, @"
drop function rest__get_setof();
drop function rest__get_table();
drop function rest__post_setof();
drop function rest__post_table();
drop function rest__put_setof();
drop function rest__put_table();
drop function rest__delete_setof();
drop function rest__delete_table();
drop table test_table;
");
public void TearDown() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
drop function rest__get_return_query(json);
drop function rest__get_return_query_additional(json, text);
drop function rest__post_return_query(json);
drop function rest__post_return_query_additional(json, text);
drop function rest__put_return_query(json);
drop function rest__put_return_query_additional(json, text);
drop function rest__delete_return_query(json);
drop function rest__delete_return_query_additional(json, text);
");
public void BuildUp() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
create function rest__get_route() returns void as $$ begin end $$ language plpgsql;
create function rest__route_is_get() returns void as $$ begin end $$ language plpgsql;
create function rest__post_route() returns void as $$ begin end $$ language plpgsql;
create function rest__route_is_post() returns void as $$ begin end $$ language plpgsql;
create function rest__put_route() returns void as $$ begin end $$ language plpgsql;
create function rest__route_is_put() returns void as $$ begin end $$ language plpgsql;
create function rest__delete_route() returns void as $$ begin end $$ language plpgsql;
create function rest__route_is_delete() returns void as $$ begin end $$ language plpgsql;
");
public void BuildUp() => DatabaseFixture.ExecuteCommand(ConnectionType.Testing, @"
create table test_table(id int, name text);
insert into test_table values (1, 'a'), (2, 'b'), (3, 'c');
create function rest__get_setof() returns setof test_table as $$
begin
return query select * from test_table;
end $$ language plpgsql;
create function rest__get_table() returns table (id int, name text) as $$
begin
return query select * from test_table;
end $$ language plpgsql;
create function rest__post_setof() returns setof test_table as $$
begin
return query select * from test_table;
end $$ language plpgsql;
create function rest__post_table() returns table (id int, name text) as $$
begin
return query select * from test_table;
end $$ language plpgsql;
create function rest__put_setof() returns setof test_table as $$
begin
return query select * from test_table;
end $$ language plpgsql;
create function rest__put_table() returns table (id int, name text) as $$
begin
return query select * from test_table;
end $$ language plpgsql;
create function rest__delete_setof() returns setof test_table as $$
begin
return query select * from test_table;
end $$ language plpgsql;
create function rest__delete_table() returns table (id int, name text) as $$
begin
return query select * from test_table;
end $$ language plpgsql;
");
public void TearDown()
{
string Script(string verb) => string.Format(@"
drop function rest__{0}_json_null();
drop function rest__{0}_jsonb_null();
drop function rest__{0}_jsonb_value();
drop function rest__{0}_void();
drop function rest__{0}_text_null();
drop function rest__{0}_text_value();
drop function rest__{0}_integer_null();
drop function rest__{0}_integer_value();
drop function rest__{0}_timestamp_null();
drop function rest__{0}_timestamp_value();
", verb);
DatabaseFixture.ExecuteCommand(
ConnectionType.PostgresTesting,
$"{Script("get")}\n\n{Script("post")}\n\n{Script("put")}\n\n{Script("delete")}");
}
public void BuildUp() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
create function rest__get_values_from_params(_int int, _text text, _timestamp timestamp, _unbound text) returns json as $$
begin
return json_build_object(
'first', _int,
'second', _text,
'third', _timestamp,
'fourth', _unbound
);
end $$ language plpgsql;
create function rest__post_values_from_params(_int int, _text text, _timestamp timestamp, _unbound text) returns json as $$
begin
return json_build_object(
'first', _int,
'second', _text,
'third', _timestamp,
'fourth', _unbound
);
end $$ language plpgsql;
create function rest__put_values_from_params(_int int, _text text, _timestamp timestamp, _unbound text) returns json as $$
begin
return json_build_object(
'first', _int,
'second', _text,
'third', _timestamp,
'fourth', _unbound
);
end $$ language plpgsql;
create function rest__delete_values_from_params(_int int, _text text, _timestamp timestamp, _unbound text) returns json as $$
begin
return json_build_object(
'first', _int,
'second', _text,
'third', _timestamp,
'fourth', _unbound
);
end $$ language plpgsql;
");
public void BuildUp() => DatabaseFixture.ExecuteCommand(ConnectionType.PostgresTesting, @"
create function rest__get_values_no_grant() returns json as
$$
begin
return (
select json_build_object('values', array[1, 2, 3])
);
end
$$ language plpgsql security definer;
revoke all on function rest__get_values_no_grant() from public;
create table test_values (i int);
insert into test_values values (1), (2), (3);
create function rest__get_values_with_grant() returns json as
$$
begin
return (
select json_build_object('values', (select array_agg(i) from test_values))
);
end
$$ language plpgsql security definer;
revoke all on function rest__get_values_with_grant() from public;
grant execute on function rest__get_values_with_grant() to testing;
create function rest__get_values_with_grant_param(_p text) returns json as
$$
begin
return (
select json_build_object('values', (select array_agg(i) from test_values))
);
end
$$ language plpgsql security definer;
revoke all on function rest__get_values_with_grant_param(text) from public;
grant execute on function rest__get_values_with_grant_param(text) to testing;
");
private void AssertAccessDenied(string sql)
{
bool permissionDenied = false;
try
{
DatabaseFixture.ExecuteCommand(ConnectionType.Testing, sql);
}
catch (PostgresException e)
{
// insufficient_privilege, see: https://www.postgresql.org/docs/11/errcodes-appendix.html
if (e.SqlState == "42501")
{
permissionDenied = true;
}
else
{
throw;
}
}
Assert.True(permissionDenied);
}
public void BuildUp()
{
string Script(string verb) => string.Format(@"
create function rest__{0}_json_null() returns json as
$$
begin
return null;
end
$$ language plpgsql;
create function rest__{0}_jsonb_null() returns jsonb as
$$
begin
return null;
end
$$ language plpgsql;
create function rest__{0}_jsonb_value() returns jsonb as
$$
begin
return '{{""field"": ""value""}}';
end
$$ language plpgsql;
create function rest__{0}_void() returns void as
$$
begin
end
$$ language plpgsql;
create function rest__{0}_text_null() returns text as
$$
begin
return null;
end
$$ language plpgsql;
create function rest__{0}_text_value() returns text as
$$
begin
return 'text';
end
$$ language plpgsql;
create function rest__{0}_integer_null() returns integer as
$$
begin
return null;
end
$$ language plpgsql;
create function rest__{0}_integer_value() returns integer as
$$
begin
return 999;
end
$$ language plpgsql;
create function rest__{0}_timestamp_null() returns timestamp as
$$
begin
return null;
end
$$ language plpgsql;
create function rest__{0}_timestamp_value() returns timestamp as
$$
begin
return '2019-05-19'::timestamp;
end
$$ language plpgsql;
", verb);
DatabaseFixture.ExecuteCommand(
ConnectionType.PostgresTesting,
$"{Script("get")}\n\n{Script("post")}\n\n{Script("put")}\n\n{Script("delete")}");
}