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?
