Re: SET ROLE x NO RESET

> On 3 Jan 2024, at 18:22, Jelte Fennema-Nio <postgres(at)jeltef(dot)nl> wrote:
>
>
>> 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

My scripts are actually Liquibase change logs.
I’ve extended Liquibase so that each change set is executed with limited privileges.

While doable with protocol level implementation, it would require support from PgJDBC.

—
Michal