Thread

Interesting security context issue

Erik Brandsberg <erik@heimdalldata.com> — 2019-07-22T20:58:41Z

I am working to create a wrapper function that encapsulates a query into
the context of a given user.  In this case, I want to set the owner of the
function to "test" and execute as that user.  Here is the test SQL:

CREATE OR REPLACE FUNCTION test(rs refcursor, sql text)
   RETURNS refcursor
AS $function$
begin
   open rs for execute sql;
   return rs;
end
$function$ language 'plpgsql' volatile security definer;

CREATE USER test;
alter function test owner to test;

begin;
   SELECT test('cursor', 'select CURRENT_USER');
   fetch all in "cursor";
commit;

In this case, the result will be the original user, NOT the user "test" as
expected.  Any thoughts on why this may be?  Even if I create a cursor
inside the function, vs. passing in the name via the parameters, the result
is the same.  It appears from this test that the context the SQL is
executed in is actually the fetch, not the context the cursor is opened
in.  You can test this by using (formatting as json to make it easier to
read the result)--at the moment the "execute" should be executed, the query
that is active should be the select specified as the parameter.  Instead,
it is reporting the active query is the fetch, indicating that the
execution was delayed until fetch was called, AND the security context of
the fetch was used to execute the cursor instead:

begin;
SELECT test('cursor', 'SELECT json_agg(t) from (SELECT * FROM
pg_stat_activity WHERE query != ''<IDLE>'' AND query NOT ILIKE
''%pg_stat_activity%'' and state != ''idle'') t ');
fetch all in "cursor";
commit;

And the result:

[
  {
    "datid": "13125",
    "datname": "postgres",
    "pid": 8218,
    "usesysid": "10",
    "usename": "postgres",
    "application_name": "DBeaver 6.1.3 - Main",
    "client_addr": "127.0.0.1",
    "client_hostname": null,
    "client_port": 47568,
    "backend_start": "2019-07-22T15:30:31.741976-04:00",
    "xact_start": "2019-07-22T16:53:32.42198-04:00",
    "query_start": "2019-07-22T16:53:36.19897-04:00",
    "state_change": "2019-07-22T16:53:36.198971-04:00",
    "wait_event_type": null,
    "wait_event": null,
    "state": "active",
    "backend_xid": null,
    "backend_xmin": "9942509",
    "query": "fetch all in \"cursor\"",
    "backend_type": "client backend"
  }
]

Thoughts on how to ensure the SQL is executed in the proper context?