public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Calvin Guo <[email protected]>
To: [email protected]
Subject: Re: set role command
Date: Mon, 24 Nov 2025 13:57:49 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>
References: <CA+bysH_or91tt7r0gKLJtw5Wp+DEYwnaRJoTvLfKO33dcdQ_rQ@mail.gmail.com>

On Mon, 2025-11-24 at 16:15 +0800, Calvin Guo wrote:
> I feel that set role logic is kindof misleading.
> 
> I am a superuser, admin,
> I do:
> set role usera
> Now I am under the security context of usera, so I think running any sql is safe
> as long as it's allowed by usera.
> 
> Which is not the case!
> as usera can do:
> set role userb; other sql,
> or 
> reset role; orther sql,
> it turns out it's not safe at all, the sql can easily get access right of the
> super user. it can impernate userb though they do not have any relationship whatso ever.
> 
> I really feel, once you "set role usera", you should behave like usera, you should
> NOT have the power say: hi, I can assume my super user power whenever I want.
> As this make the "set role usera" pretty much useless.

I respect your feelings, but that is not how SET ROLE works.
The current behavior is intentional and documented in
https://www.postgresql.org/docs/current/sql-set-role.html

There is SET SESSION AUTHORIZATION, which acts somewhet more like you want,
except that you can become a superuser again with RESET SESSION AUTHORIZATION.

You'll have to come up with a different security concept.

Yours,
Laurenz Albe

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: set role command
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox