Обсуждение: SET ROLE x NO RESET
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
Eric
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
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?
—
MIchal
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
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
> 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
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
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 --
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 --
> 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
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