Interesting security context issue

From: Erik Brandsberg <erik(at)heimdalldata(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Interesting security context issue
Date: 2019-07-22 20:58:41
Message-ID: CAFcck8HNgFQmqBZgmy6xA3XNL6UNCAFXy7+ae5bMV2bCpynSLw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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?

Browse pgsql-sql by date

  From Date Subject
Next Message Rick Vincent 2019-07-23 11:22:25 Implicit typecast behavior
Previous Message David G. Johnston 2019-07-22 03:14:56 How do I alter an existing column and add a foreign key which is a Primary key to a table?