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
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?
What I proposed some time ago is SET ROLE … GUARDED BY ‘password’, so that you could later: RESET ROLE WITH ‘password'
/messages/by-id/F9428C6E-4CCC-441D-A148-67BF36526D45@kleczek.org
—
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
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">On 12/31/23 1:19 PM, Joe Conway wrote:<br>
</div>
<blockquote type="cite"
cite="mid:f261000a-f18d-40fd-aeee-0f5d548b4332@joeconway.com">On
12/30/23 17:19, Michał Kłeczek wrote:
<br>
<blockquote type="cite">
<br>
<blockquote type="cite">On 30 Dec 2023, at 17:16, Eric Hanson
<a class="moz-txt-link-rfc2396E" href="mailto:eric@aquameta.com"><eric@aquameta.com></a> wrote:
<br>
<br>
What do you think of adding a NO RESET option to the SET ROLE
command?
<br>
</blockquote>
<br>
What I proposed some time ago is SET ROLE … GUARDED BY
‘password’, so that you could later: RESET ROLE WITH ‘password'
<br>
</blockquote>
<br>
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.
</blockquote>
That means you'd need to manage that password. ISTM a better
mechanism to do this in SQL would be a method of changing roles that
returns a nonce that you'd then use to reset your role. I believe
that'd also make it practical to do this server-side in the various
PLs.<br>
</body>
</html>
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:
/messages/by-id/CAGECzQR=1t1TL-eS9HAjoGysdprPci5K7-C353PnON6W-_s9uw@mail.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
--
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<div class="moz-cite-prefix">On 1/3/24 5:47 PM, Nico Williams wrote:<br>
</div>
<blockquote type="cite" cite="mid:ZZXyB3aTBKagISrc@ubby">
<pre>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.</pre>
</blockquote>
<p>An unprivileged role that has the ability to assume any other
role ;p</p>
<p>Also, last I checked you can't do SET ROLE in a security definer
function.<br>
</p>
<blockquote type="cite" cite="mid:ZZXyB3aTBKagISrc@ubby">
<pre>
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.
</pre>
</blockquote>
<p>RESET ROLE is also useful for setting up a "superuser role" that
DBAs have access to via a NO INHERIT role. It allows them to do
things like...</p>
<p>SET ROLE su;</p>
<p>-- Do some superuserly thing</p>
<p>RESET ROLE;</p>
<p>Admittedly, the last step could be just to set their role back to
themselves, but RESET ROLE removes the risk of typos.<br>
</p>
<pre class="moz-signature" cols="72">--
Jim Nasby, Data Architect, Austin TX</pre>
</body>
</html>
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