Preventing non-superusers from altering session authorization

On Wed, Jun 21, 2023 at 04:28:43PM -0400, Joseph Koshakow wrote:

Currently, a user is allowed to execute SET SESSION AUTHORIZATION [1]
if the role they connected to PostgreSQL with was a superuser at the
time of connection. Even if the role is later altered to no longer be a
superuser, the session can still execute SET SESSION AUTHORIZATION, as
long as the session isn't disconnected. As a consequence, if that role
is altered to no longer be a superuser, then the user can use SET
SESSION AUTHORIZATION to switch to another role that is a superuser and
regain superuser privileges. They can even re-grant themselves the
superuser attribute.

I suspect most users aren't changing the superuser attribute on roles very
often, so it's unlikely to be a problem. But it might still be worth
revisiting.

It is possible that the user had already run SET SESSION AUTHORIZATION
to set their session to a superuser before their connecting role lost
the superuser attribute. In this case there's not much we can do.

Right.

Also, from looking at the code and documentation, it looks like SET
SESSION AUTHORIZATION works this way intentionally. However, I'm unable
to figure out why we'd want it to work this way.

I found a brief mention in the archives about this implementation decision
[0]: /messages/by-id/Pine.LNX.4.30.0104182119290.762-100000@peter.localdomain

I've attached a patch that would fix this issue by checking the catalog
to see if the connecting role is currently a superuser every time SET
SESSION AUTHORIZATION is run. However, according to the comment I
deleted there's something invalid about reading the catalog from that
function, though I wasn't able to understand it fully.

This comment was added in e5d6b91. I see that RESET SESSION AUTHORIZATION
with a concurrently dropped role will FATAL with your patch but succeed
without it, which could be part of the reason.

One downside is that if a user switches their session authorization to
some role, then loses the superuser attribute on their connecting role,
they may be stuck in a that role with no way to reset their session
authorization without disconnecting and reconnecting.

It looks like SetSessionAuthorization() skips the privilege checks if the
target role is the authenticated role, so I don't think they'll get stuck.

[0]: /messages/by-id/Pine.LNX.4.30.0104182119290.762-100000@peter.localdomain

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Wed, Jun 21, 2023 at 04:28:43PM -0400, Joseph Koshakow wrote:

+	roleTup = SearchSysCache1(AUTHOID, ObjectIdGetDatum(AuthenticatedUserId));
+	if (!HeapTupleIsValid(roleTup))
+		ereport(FATAL,
+				(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),
+						errmsg("role with OID %u does not exist", AuthenticatedUserId)));
+	rform = (Form_pg_authid) GETSTRUCT(roleTup);

I think "superuser_arg(AuthenticatedUserId)" would work here.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Wed, Jun 21, 2023 at 11:48 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

On Wed, Jun 21, 2023 at 04:28:43PM -0400, Joseph Koshakow wrote:

+ roleTup = SearchSysCache1(AUTHOID,

ObjectIdGetDatum(AuthenticatedUserId));

+     if (!HeapTupleIsValid(roleTup))
+             ereport(FATAL,
+

(errcode(ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION),

+ errmsg("role with OID

%u does not exist", AuthenticatedUserId)));

+ rform = (Form_pg_authid) GETSTRUCT(roleTup);

I think "superuser_arg(AuthenticatedUserId)" would work here.

Yep, that worked. I've attached a patch with this change.

I see that RESET SESSION AUTHORIZATION
with a concurrently dropped role will FATAL with your patch but succeed
without it, which could be part of the reason.

That might be a good change? If the original authenticated role ID no
longer exists then we may want to return an error when trying to set
your session authorization to that role.

Thanks,
Joe Koshakow

Hi,

I’ve just stumbled upon this patch and thread and thought I could share an idea of adding an optional temporary secret to SET SESSION AUTHORIZATION so that it is only possible to RESET SESSION AUTHORIZATION by providing the same secret ,like:

SET SESSION AUTHORIZATION [role] GUARDED BY ‘[secret]’;

...

RESET SESSION AUTHORIZATION WITH ‘[secret]’;

The use case is: I have a set of Liquibase scripts I would like to execute as a different role each and make sure they cannot escape the sandbox.

As I am not a Postgres hacker I wonder how difficult to implement it might be…

Thanks,
Michal

On Thu, Jun 22, 2023 at 06:39:45PM -0400, Joseph Koshakow wrote:

On Wed, Jun 21, 2023 at 11:48 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

I see that RESET SESSION AUTHORIZATION
with a concurrently dropped role will FATAL with your patch but succeed
without it, which could be part of the reason.

That might be a good change? If the original authenticated role ID no
longer exists then we may want to return an error when trying to set
your session authorization to that role.

I was curious why we don't block DROP ROLE if there are active sessions for
the role or terminate any such sessions as part of the command, and I found
this discussion from 2016:

/messages/by-id/flat/56E87CD8.60007@ohmu.fi

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

That might be a good change? If the original authenticated role ID no
longer exists then we may want to return an error when trying to set
your session authorization to that role.

I was curious why we don't block DROP ROLE if there are active sessions

for

the role or terminate any such sessions as part of the command, and I

found

this discussion from 2016:

/messages/by-id/flat/56E87CD8.60007@ohmu.fi

Ah, that makes sense that we don't prevent DROP ROLE on active roles.
Though, we do error when you try and set your role or session
authorization to a dropped role. So erroring on RESET SESSION
AUTHORIZATION when the original role is dropped makes it consistent
with SET SESSION AUTHORIZATION TO <dropped-original-role>. On the other
hand it makes it inconsistent with RESET ROLE, which does not error on
a dropped role.

- Joe Koshakow

On Fri, Jun 23, 2023 at 1:54 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

Nathan Bossart <nathandbossart@gmail.com> wrote:

I see that RESET SESSION AUTHORIZATION
with a concurrently dropped role will FATAL with your patch but succeed
without it, which could be part of the reason.

I didn't even realize it, but the change to superuser_arg() in v2 fixed
this issue. The catalog lookup is only done if
userid != AuthenticatedUserId. So RESET SESSION AUTHORIZATION with a
concurrently dropped role will no longer FATAL.

Thanks,
Joe

On Sat, Jul 1, 2023 at 11:33 AM Joseph Koshakow <koshy44@gmail.com> wrote:

I've discovered an issue with this approach. Let's say you have some
session open that is connected as a superuser and you run the following
commands:

- CREATE ROLE r1 LOGIN SUPERUSER;
- CREATE ROLE r2;
- CREATE ROLE r3;

Then you open another session connected with user r1 and run the
following commands:

- SET SESSION AUTHROIZATION r2;
- BEGIN;
- SET SESSION AUTHORIZATION r3;

Then in your original session run:

- ALTER ROLE r1 NOSUPERUSER;

Finally in the r1 session run:

- CREATE TABLE t ();

Postgres will then panic with the following logs:

2023-07-08 16:33:27.787 EDT [157141] ERROR: permission denied for schema
public at character 14
2023-07-08 16:33:27.787 EDT [157141] STATEMENT: CREATE TABLE t ();
2023-07-08 16:33:27.787 EDT [157141] ERROR: permission denied to set
session authorization
2023-07-08 16:33:27.787 EDT [157141] WARNING: AbortTransaction while in
ABORT state
2023-07-08 16:33:27.787 EDT [157141] ERROR: permission denied to set
session authorization
2023-07-08 16:33:27.787 EDT [157141] WARNING: AbortTransaction while in
ABORT state
2023-07-08 16:33:27.787 EDT [157141] ERROR: permission denied to set
session authorization
2023-07-08 16:33:27.787 EDT [157141] WARNING: AbortTransaction while in
ABORT state
2023-07-08 16:33:27.787 EDT [157141] ERROR: permission denied to set
session authorization
2023-07-08 16:33:27.787 EDT [157141] PANIC: ERRORDATA_STACK_SIZE exceeded
2023-07-08 16:33:27.882 EDT [156878] LOG: server process (PID 157141) was
terminated by signal 6: Aborted
2023-07-08 16:33:27.882 EDT [156878] DETAIL: Failed process was running:
CREATE TABLE t ();

I think the issue here is that if a session loses the ability to set
their session authorization in the middle of a transaction, then
rolling back the transaction may fail and cause the server to panic.
That's probably what the deleted comment mean when it said:

* It's OK because the check does not require catalog access and can't
* fail during an end-of-transaction GUC reversion

Interestingly, if the r1 session manually types `ROLLBACK` instead of
executing a command that fails, then everything is fine and there's no
panic. I'm not familiar enough with transaction handling to know why
there would be a difference there.

Thanks,
Joe Koshakow

On Sat, Jul 08, 2023 at 04:44:06PM -0400, Joseph Koshakow wrote:

2023-07-08 16:33:27.787 EDT [157141] PANIC: ERRORDATA_STACK_SIZE exceeded
2023-07-08 16:33:27.882 EDT [156878] LOG: server process (PID 157141) was
terminated by signal 6: Aborted
2023-07-08 16:33:27.882 EDT [156878] DETAIL: Failed process was running:
CREATE TABLE t ();

I think the issue here is that if a session loses the ability to set
their session authorization in the middle of a transaction, then
rolling back the transaction may fail and cause the server to panic.
That's probably what the deleted comment mean when it said:

* It's OK because the check does not require catalog access and can't
* fail during an end-of-transaction GUC reversion

Yeah. IIUC the ERROR longjmps to a block that calls AbortTransaction(),
which ERRORs again when resetting the session authorization, which causes
us to call AbortTransaction() again, etc., etc.

Interestingly, if the r1 session manually types `ROLLBACK` instead of
executing a command that fails, then everything is fine and there's no
panic. I'm not familiar enough with transaction handling to know why
there would be a difference there.

I haven't had a chance to dig into this one yet, but that is indeed
interesting.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Sat, Jul 8, 2023 at 6:09 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

I think the issue here is that if a session loses the ability to set
their session authorization in the middle of a transaction, then
rolling back the transaction may fail and cause the server to panic.
That's probably what the deleted comment mean when it said:

* It's OK because the check does not require catalog access and can't
* fail during an end-of-transaction GUC reversion

Yeah. IIUC the ERROR longjmps to a block that calls AbortTransaction(),
which ERRORs again when resetting the session authorization, which causes
us to call AbortTransaction() again, etc., etc.

Everything seems to work fine if the privilege check is moved to
check_session_authorization. Which is maybe what the comment meant
instead of assign_session_authorization.

I've attached a patch with this change.

Thanks,
Joe Koshakow

On Sat, Jul 08, 2023 at 07:08:35PM -0400, Joseph Koshakow wrote:

On Sat, Jul 8, 2023 at 6:09 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

I think the issue here is that if a session loses the ability to set
their session authorization in the middle of a transaction, then
rolling back the transaction may fail and cause the server to panic.
That's probably what the deleted comment mean when it said:

* It's OK because the check does not require catalog access and can't
* fail during an end-of-transaction GUC reversion

Yeah. IIUC the ERROR longjmps to a block that calls AbortTransaction(),
which ERRORs again when resetting the session authorization, which causes
us to call AbortTransaction() again, etc., etc.

src/backend/utils/misc/README has the following relevant text:

Note that there is no provision for a failure result code. assign_hooks
should never fail except under the most dire circumstances, since a failure
may for example result in GUC settings not being rolled back properly during
transaction abort. In general, try to do anything that could conceivably
fail in a check_hook instead, and pass along the results in an "extra"
struct, so that the assign hook has little to do beyond copying the data to
someplace. This applies particularly to catalog lookups: any required
lookups must be done in the check_hook, since the assign_hook may be
executed during transaction rollback when lookups will be unsafe.

Everything seems to work fine if the privilege check is moved to
check_session_authorization. Which is maybe what the comment meant
instead of assign_session_authorization.

Ah, that does make more sense.

I think we should split this into two patches: one to move the permission
check to check_session_authorization() and another for the behavior change.
I've attached an attempt at the first one (that borrows heavily from your
latest patch). AFAICT the only reason that the permission check lives in
SetSessionAuthorization() is because AuthenticatedUserIsSuperuser is static
to miscinit.c and doesn't have an accessor function. I added one, but it
would probably just be removed by the following patch. WDYT?

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Sun, Jul 9, 2023 at 12:47 AM Nathan Bossart <nathandbossart@gmail.com>
wrote:

I think we should split this into two patches: one to move the permission
check to check_session_authorization() and another for the behavior

change.

I've attached an attempt at the first one (that borrows heavily from your
latest patch). AFAICT the only reason that the permission check lives in
SetSessionAuthorization() is because AuthenticatedUserIsSuperuser is

static

to miscinit.c and doesn't have an accessor function. I added one, but it
would probably just be removed by the following patch. WDYT?

I think that's a good idea. We could even keep around the accessor
function as a good place to bundle the calls to
Assert(OidIsValid(AuthenticatedUserId))
and
superuser_arg(AuthenticatedUserId)

* Only a superuser may set auth ID to something other than himself

Is "auth ID" the right term here? Maybe something like "Only a
superuser may set their session authorization/ID to something other
than their authenticated ID."

But we set the GUC variable
* is_superuser to indicate whether the *current* session userid is a
* superuser.

Just a small correction here, I believe the is_superuser GUC is meant
to indicate whether the current user id is a superuser, not the current
session user id. We only update is_superuser in SetSessionAuthorization
because we are also updating the current user id in SetSessionUserId.
For example,

test=# CREATE ROLE r1 SUPERUSER;
CREATE ROLE
test=# CREATE ROLE r2;
CREATE ROLE
test=# SET SESSION AUTHORIZATION r1;
SET
test=# SET ROLE r2;
SET
test=> SELECT session_user, current_user;
session_user | current_user
--------------+--------------
r1 | r2
(1 row)

test=> SHOW is_superuser;
is_superuser
--------------
off
(1 row)

Which has also made me realize that the comment on is_superuser in
guc_tables.c is incorrect:

/* Not for general use --- used by SET SESSION AUTHORIZATION */

Additionally the C variable name for is_superuser is fairly misleading:

session_auth_is_superuser

The documentation for this GUC in show.sgml is correct:

True if the current role has superuser privileges.

As an aside, I'm starting to think we should consider removing this
GUC. It sometimes reports an incorrect value [0]/messages/by-id/CAAvxfHcxH-hLndty6CRThGXL1hLsgCn+E3QuG_4Qi7GxrHmgKg@mail.gmail.com, and potentially is
not used internally for anything.

I've rebased my changes over your patch and attached them both.

[0]: /messages/by-id/CAAvxfHcxH-hLndty6CRThGXL1hLsgCn+E3QuG_4Qi7GxrHmgKg@mail.gmail.com
/messages/by-id/CAAvxfHcxH-hLndty6CRThGXL1hLsgCn+E3QuG_4Qi7GxrHmgKg@mail.gmail.com

On Sun, Jul 9, 2023 at 1:03 PM Joseph Koshakow <koshy44@gmail.com> wrote:

* Only a superuser may set auth ID to something other than himself

Is "auth ID" the right term here? Maybe something like "Only a
superuser may set their session authorization/ID to something other
than their authenticated ID."

But we set the GUC variable
* is_superuser to indicate whether the *current* session userid is a
* superuser.

Just a small correction here, I believe the is_superuser GUC is meant
to indicate whether the current user id is a superuser, not the current
session user id. We only update is_superuser in SetSessionAuthorization
because we are also updating the current user id in SetSessionUserId.

I just realized that you moved this comment from
SetSessionAuthorization. I think we should leave the part about setting
the GUC variable is_superuser on top of SetSessionAuthorization since
that's where we actually set the GUC.

Thanks,
Joe Koshakow

On Sun, Jul 09, 2023 at 08:54:30PM -0400, Joseph Koshakow wrote:

I just realized that you moved this comment from
SetSessionAuthorization. I think we should leave the part about setting
the GUC variable is_superuser on top of SetSessionAuthorization since
that's where we actually set the GUC.

Okay. Here's a new patch set in which I believe I've addressed all
feedback. I didn't keep the GetAuthenticatedUserIsSuperuser() helper
function around, as I didn't see a strong need for it. And I haven't
touched the "is_superuser" GUC, either. I figured we can take up any
changes for it in the other thread.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Mon, Jul 10, 2023 at 4:32 PM Nathan Bossart <nathandbossart@gmail.com>
wrote:

Okay. Here's a new patch set in which I believe I've addressed all
feedback. I didn't keep the GetAuthenticatedUserIsSuperuser() helper
function around, as I didn't see a strong need for it.

Thanks, I think the patch set looks good to go!

And I haven't
touched the "is_superuser" GUC, either. I figured we can take up any
changes for it in the other thread.

Yeah, I think that makes sense.

Thanks,
Joe Koshakow

On Mon, Jul 10, 2023 at 04:46:07PM -0400, Joseph Koshakow wrote:

Thanks, I think the patch set looks good to go!

Great. I'm going to wait a few more days in case anyone has additional
feedback, but otherwise I intend to commit this shortly.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Mon, Jul 10, 2023 at 01:49:55PM -0700, Nathan Bossart wrote:

Great. I'm going to wait a few more days in case anyone has additional
feedback, but otherwise I intend to commit this shortly.

I've committed 0001 for now. I'm hoping to commit the other two patches
within the next couple of days.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com

On Wed, Jul 12, 2023 at 09:37:57PM -0700, Nathan Bossart wrote:

On Mon, Jul 10, 2023 at 01:49:55PM -0700, Nathan Bossart wrote:

Great. I'm going to wait a few more days in case anyone has additional
feedback, but otherwise I intend to commit this shortly.

I've committed 0001 for now. I'm hoping to commit the other two patches
within the next couple of days.

Committed. I dwelled on whether to proceed with this change because it
doesn't completely solve the originally-stated problem; i.e., a role that
has changed its session authorization before losing superuser can still
take advantage of the privileges of the target role, which might include
reaquiring superuser. However, I think SET ROLE is subject to basically
the same problem, and I'd argue that this change is strictly an
improvement, if for no other reason than it makes SET SESSION AUTHORIZATION
more consistent with SET ROLE.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com