From: Michał Kłeczek <michal(at)kleczek(dot)org>
To: Jelte Fennema-Nio <postgres(at)jeltef(dot)nl>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Joe Conway <mail(at)joeconway(dot)com>, Eric Hanson <eric(at)aquameta(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: SET ROLE x NO RESET
Date: 2024-01-05 06:10:39
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

> 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.


In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Nathan Bossart 2024-01-05 06:11:44 Re: verify predefined LWLocks have entries in wait_event_names.txt
Previous Message Peter Smith 2024-01-05 05:25:44 Re: Documentation to upgrade logical replication cluster