Re: DEFINER / INVOKER conundrum

[walther]

Erik Wienhold:

A single DEFINER function works if you capture current_user with a parameter
and default value.  Let's call it claimed_role.  Use pg_has_role[0] to check
that session_user has the privilege for claimed_role (in case the function is
called with an explicit value), otherwise raise an exception.

Connect as postgres:

CREATE FUNCTION f(claimed_role text default current_user)
  RETURNS TABLE (claimed_role text, curr_user text, sess_user text)
  SECURITY DEFINER
  LANGUAGE sql
  $$ SELECT claimed_role, current_user, session_user $$;


For me, checking whether session_user has the privilege for claimed_role 
is not enough, so I add a DOMAIN to the mix:


CREATE DOMAIN current_user_only AS NAME CHECK (VALUE = CURRENT_USER);

CREATE FUNCTION f(calling_user current_user_only DEFAULT CURRENT_USER)
...
SECURITY DEFINER;

This works, because the domain check is evaluated in the calling context.

Best,

Wolfgang

Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

[Wolfgang Walther]

Dominique Devienne:

I wish for DB-specific ROLEs BTW...


Same here. That would be so useful.

Re: ALTER ROLE ... SET in current database only

[Wolfgang Walther]

Abdul Qoyyuum:
Wouldnt you need to connect to the database first before you can ALTER 
ROLE anything?


Of course, otherwise the notion of "current database" wouldn't make 
sense at all. But that's only before executing the code. I am not 
writing and executing this code at the same time.


In my case I'm seeding a postgres docker container with settings and 
data on first launch. The database name is passed to the container via 
environment variable. But, I'm sure there are other use-cases where code 
should be written once, but be executed in different databases.


Best,

Wolfgang

ALTER ROLE ... SET in current database only

[Wolfgang Walther]

Hi,

I'm trying to set a GUC for a role in the current database only - but 
don't know the name of the database at the time of writing code. Could 
be development, staging, ...


I would basically like to do something like this:

ALTER ROLE a IN CURRENT DATABASE SET b TO c;

Although that syntax doesn't exist (yet).

I think I could wrap it in a DO block and create the statement 
dynamically. Alternatively, I could probably INSERT INTO / UPDATE 
pg_db_role_setting manually?


Any other ideas how to achieve this easily? Does the proposed "IN 
CURRENT DATABASE" syntax sound useful to anyone else?


Best,

Wolfgang

Re: Execution order of CTEs / set_config and current_setting in the same query

[Wolfgang Walther]

Tom Lane:

I think you're on fairly shaky ground here.  Generally speaking, a CTE
will be executed/read only when the parent query needs the next row from
it.  Your examples ensure that the CTE is read before the parent query's
results are computed; but in realistic usage you'd presumably be joining
the CTE with some other table(s), and then the execution order is going
to be a lot harder to predict.  This approach is also going to
fundamentally not work for settings that need to apply during planning
of the query (which, notably, probably includes "role").


Ok, thanks for confirming that.


You'd be far better off to investigate ways to send SET LOCAL first,
without incurring a separate network round trip for that.  If you're
using simple query mode that's easy, you can just do

res = PQexec("SET LOCAL ... ; ");

In extended query mode you can't get away with that, but you might be able
to issue the SET LOCAL without immediately waiting for the result.


Yes, that's what we did so far. We switched to set_config to parametrize 
the query.


Is there any way to not wait for a SELECT? I don't care about the 
resultset, so I need something like PERFORM but for SQL, not plpgsql, I 
think?

Execution order of CTEs / set_config and current_setting in the same query

[Wolfgang Walther]

Hi,

with PostgREST [1] we are translating HTTP requests into SQL queries. 
For each request we are setting some metadata (headers, ...) as GUCs.


We used to do it like this:
SET LOCAL request.headers.x = 'y';
...

Since this is user-provided data, we want to use parametrized/prepared 
statements. This is not possible with SET, so we switched to:


SELECT set_config($1, $2, true), ...;

Both these queries are preceding our main query. The SELECT set_config 
is a bit slower than the SET LOCAL, probably because of more overhead on 
the SELECT.


Now, we are wondering: To reduce overhead, can we move the set_config 
calls to a CTE as part of the main query? The values would need to be 
available with current_setting(...) in the remaining query.


Of course we would need to ensure execution order, so that this CTE will 
always be fully executed, before all the other parts of the query.


Is this possible to do?

We did some basic testing, that seemed to be successful:

WITH set AS (
  SELECT set_config('pgrst.hello', 'world', true)
)
SELECT current_setting('pgrst.hello')
FROM set;

or

WITH set AS (
  SELECT set_config('pgrst.hello', 'world', true)
),
main AS (
  SELECT
current_setting('pgrst.hello') AS hello,

  FROM set, 
)
SELECT
  current_setting('pgrst.hello'),
  main.hello,
  
FROM set, main, ;


Queries like this seem to have set the GUC correctly. But is this 
guaranteed? What would need to be done to guarantee it?


I have a feeling that even though this works for those simple cases, 
there is some risk involved...


Additional question: If this can be guaranteed - what about using 
set_config('role', 'xxx', true) in the same way? Putting this into those 
examples above and checking with CURRENT_USER seems to work as well. How 
likely would this lead to problems with privileges / permissions?


Any input/insight would be helpful.

Thanks

Wolfgang

[1]: https://postgrest.org