Re: SET ROLE x NO RESET

On Tue, 2 Jan 2024 at 23:23, Michał Kłeczek <michal(at)kleczek(dot)org> wrote:
> > On 2 Jan 2024, at 18:36, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > IMHO, the best solution here would be a protocol message to change the
> > session user. The pooler could use that repeatedly on the same
> > session, but refuse to propagate such messages from client
> > connections.
>
> I think that is a different use case and both are needed.

FYI I implemented something just now that's pretty much what Robert
was talking about:
https://www.postgresql.org/message-id/flat/CAGECzQR%253D1t1TL-eS9HAjoGysdprPci5K7-C353PnON6W-_s9uw%2540mail.gmail.com

> In my case I have scripts that I want to execute with limited privileges
> and make sure the scripts cannot escape the sandbox via RESET ROLE.

Depending on the desired workflow I think that could work for you too.
Because it allows you to do this (and use -f script.sql instead of -c
'select ...):

❯ psql "user=postgres _pq_.protocol_managed_params=role options='-c
role=pg_read_all_data'" -c 'select current_user; set role postgres'
current_user
──────────────────
pg_read_all_data
(1 row)

ERROR: 42501: parameter can only be set at the protocol level "role"
LOCATION: set_config_with_handle, guc.c:3583
Time: 0.667 ms