create role usera_sandbox in group usera; \c - usera_sandbox but what will happen if I then issue: reset role? I don't think it is a real sandbox. You can always escape.
To make it worse, I tested "set session authorization rolename", which will change the session user and current user to new rolename. But I can still do reset session authorization to go back to super user. Seems like once I connect as a super user, there is no way for drop the previledge. On Tue, Nov 25, 2025 at 6:30 AM <[email protected]> wrote: > Just because you did set role does not mean you lost your superuser > privileges, it's correct behavior. > > If you want to impersonate in a permissions sandbox it's easy: > > create role usera_sandbox in group usera; > \c - usera_sandbox > > > -- > regards, > Kiriakos Georgiou > > > On 11/24/25 3:15 AM, Calvin Guo - newoakllc2023 at gmail.com 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. > > It's unsafe! > >
