Thread: SET ROLE x NO RESET

SET ROLE x NO RESET

From
Eric Hanson
Date:
Hi,

What do you think of adding a NO RESET option to the SET ROLE command?

Right now Postgres can enforce data security with roles and RLS, but role-per-end-user doesn't really scale:  Db connections are per-role, so a connection pooler can't share connections across users.  We can work around this with policies that use session variables and checks against current_user, but it seems like role-per end user would be more beautiful.  If SET ROLE had a NO RESET option, you could connect through a connection pooler as a privileged user, but downgrade to the user's role for the duration of the session.

Thanks,
Eric

Re: SET ROLE x NO RESET

From
Joe Conway
Date:
On 12/30/23 11:16, Eric Hanson wrote:
> Hi,
> 
> What do you think of adding a NO RESET option to the SET ROLE command?
> 
> Right now Postgres can enforce data security with roles and RLS, but 
> role-per-end-user doesn't really scale:  Db connections are per-role, so 
> a connection pooler can't share connections across users.  We can work 
> around this with policies that use session variables and checks against 
> current_user, but it seems like role-per end user would be more 
> beautiful.  If SET ROLE had a NO RESET option, you could connect through 
> a connection pooler as a privileged user, but downgrade to the user's 
> role for the duration of the session.

+1

I agree this would be useful.

In the meantime, in case it helps, see

   https://github.com/pgaudit/set_user

Specifically set_session_auth(text):
-------------
When set_session_auth(text) is called, the effective session and current 
user is switched to the rolename supplied, irrevocably. Unlike 
set_user() or set_user_u(), it does not affect logging nor allowed 
statements. If set_user.exit_on_error is "on" (the default), and any 
error occurs during execution, a FATAL error is thrown and the backend 
session exits.
-------------

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com




Re: SET ROLE x NO RESET

From
Michał Kłeczek
Date:

On 30 Dec 2023, at 17:16, Eric Hanson <eric@aquameta.com> wrote:

What do you think of adding a NO RESET option to the SET ROLE command?

What I proposed some time ago is SET ROLE … GUARDED BY ‘password’, so that you could later: RESET ROLE WITH ‘password'


MIchal

Re: SET ROLE x NO RESET

From
Joe Conway
Date:
On 12/30/23 17:19, Michał Kłeczek wrote:
> 
>> On 30 Dec 2023, at 17:16, Eric Hanson <eric@aquameta.com> wrote:
>>
>> What do you think of adding a NO RESET option to the SET ROLE command?
> 
> What I proposed some time ago is SET ROLE … GUARDED BY ‘password’, so 
> that you could later: RESET ROLE WITH ‘password'

I like that too, but see it as a separate feature. FWIW that is also 
supported by the set_user extension referenced elsewhere on this thread.

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com




Re: SET ROLE x NO RESET

From
Robert Haas
Date:
On Sun, Dec 31, 2023 at 2:20 PM Joe Conway <mail@joeconway.com> wrote:
> On 12/30/23 17:19, Michał Kłeczek wrote:
> >> On 30 Dec 2023, at 17:16, Eric Hanson <eric@aquameta.com> wrote:
> >>
> >> What do you think of adding a NO RESET option to the SET ROLE command?
> >
> > What I proposed some time ago is SET ROLE … GUARDED BY ‘password’, so
> > that you could later: RESET ROLE WITH ‘password'
>
> I like that too, but see it as a separate feature. FWIW that is also
> supported by the set_user extension referenced elsewhere on this thread.

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.

--
Robert Haas
EDB: http://www.enterprisedb.com



Re: SET ROLE x NO RESET

From
Michał Kłeczek
Date:

> On 2 Jan 2024, at 18:36, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Sun, Dec 31, 2023 at 2:20 PM Joe Conway <mail@joeconway.com> wrote:
>> On 12/30/23 17:19, Michał Kłeczek wrote:
>>>> On 30 Dec 2023, at 17:16, Eric Hanson <eric@aquameta.com> wrote:
>>>>
>>>> What do you think of adding a NO RESET option to the SET ROLE command?
>>>
>>> What I proposed some time ago is SET ROLE … GUARDED BY ‘password’, so
>>> that you could later: RESET ROLE WITH ‘password'
>>
>> I like that too, but see it as a separate feature. FWIW that is also
>> supported by the set_user extension referenced elsewhere on this thread.
>
> 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.

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.

Thanks,
Michal


Re: SET ROLE x NO RESET

From
Jelte Fennema-Nio
Date:
On Tue, 2 Jan 2024 at 23:23, Michał Kłeczek <michal@kleczek.org> wrote:
> > On 2 Jan 2024, at 18:36, Robert Haas <robertmhaas@gmail.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



Re: SET ROLE x NO RESET

From
Nico Williams
Date:
On Sat, Dec 30, 2023 at 10:16:59AM -0600, Eric Hanson wrote:
> What do you think of adding a NO RESET option to the SET ROLE command?

I've wanted this forever.  Consider using this to implement user
authentication mechanisms in user-defined SQL functions that use `SET
ROLE` with `NO RESET` to "login" the user.  One could implement JWT (or
whatever bearer token schemes) on the server side in PlPgSQL w/ pgcrypto
this way, with zero changes to PG itself, no protocol changes, etc.

For bearer token schemes one could acquire the token externally to the
client and then just `SELECT login(?)`, bind the token, and execute to
login.

Nico
-- 



Re: SET ROLE x NO RESET

From
Nico Williams
Date:
On Tue, Jan 02, 2024 at 12:36:38PM -0500, Robert Haas 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.

But this requires upgrading clients too.

IMO `SET ROLE .. NO RESET` would be terribly useful.  One could build:

 - login systems (e.g., bearer tokens, passwords) in SQL / PlPgSQL / etc

 - sudo-like things

Though maybe `NO RESET` isn't really needed to build these, since after
all one could use an unprivileged role and a SECURITY DEFINER function
that does the `SET ROLE` following some user-defined authentication
method, and so what if the client can RESET the role, since that brings
it back to the otherwise unprivileged role.

Who needs to RESET roles anyways?  Answer: connection pools, but not
every connection is used via a pool.  This brings up something: attempts
to reset a NO RESET session need to fail in such a way that a connection
pool can detect this and disconnect, or else it needs to fail by
terminating the connection altogether.

Nico
-- 



Re: SET ROLE x NO RESET

From
Michał Kłeczek
Date:

> On 3 Jan 2024, at 18:22, Jelte Fennema-Nio <postgres@jeltef.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




Re: SET ROLE x NO RESET

From
Eric Hanson
Date:
On Sat, Dec 30, 2023 at 11:50 AM Joe Conway <mail@joeconway.com> wrote:
In the meantime, in case it helps, see

   https://github.com/pgaudit/set_user

Specifically set_session_auth(text):
-------------
When set_session_auth(text) is called, the effective session and current
user is switched to the rolename supplied, irrevocably. Unlike
set_user() or set_user_u(), it does not affect logging nor allowed
statements. If set_user.exit_on_error is "on" (the default), and any
error occurs during execution, a FATAL error is thrown and the backend
session exits.

This helps, but has the downside (of course) of being a compiled extension which limits its use on hosted services and such unless they decide to support it.

Would be really great if pooling could co-exist with per-user roles somehow, I'm not the best to weigh in on how, but it's bottlenecking the whole space of using roles per-user, and AFAICT this pattern would otherwise be totally feasible and awesome, with all the progress that's been made in this space.

Eric