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_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_values_no_grant();
            drop function rest__get_values_with_grant();
            drop function rest__get_values_with_grant_param(text);
            drop table test_values;

            ");
Example #4
0
            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();

            ");
Example #5
0
            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;

            ");
Example #6
0
            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();

            ");
Example #7
0
            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);

            ");
Example #9
0
            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;

            ");
Example #10
0
            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;

            ");
Example #11
0
            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);

            ");
Example #12
0
            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 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 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_return_query_applied(_query json) returns json as $$
            begin
                return _query;
            end $$ language plpgsql;

            create function rest__get_return_query_additional_applied(_query json, _additional text) returns json as $$
            begin
                return _query::jsonb || format('{""additional"": ""%s""}', _additional)::jsonb;
            end $$ language plpgsql;

            create function rest__delete_return_query_applied(_query json) returns json as $$
            begin
                return _query;
            end $$ language plpgsql;

            create function rest__delete_return_query_additional_applied(_query json, _additional text) returns json as $$
            begin
                return _query::jsonb || format('{""additional"": ""%s""}', _additional)::jsonb;
            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")}");
            }