And thanks to Robert and Bruce for bringing up good points about potential pitfalls!
I think we do have a good discussion going on here :) --- Hannu On Fri, Jul 1, 2022 at 11:14 AM Hannu Krosing <han...@google.com> wrote: > > On Thu, Jun 30, 2022 at 7:25 PM Bruce Momjian <br...@momjian.us> wrote: > > > > On Thu, Jun 30, 2022 at 11:52:20AM -0400, Robert Haas wrote: > > > I don't think this would be very convenient in most scenarios, > > This is the eternal problem with security - more security always > includes more inconvenience. > > Unlocking your door when coming home is more inconvenient than not > unlocking it, and the least inconvenient thing would be not having a > door at all. > Imagine coming to your door with a heavy shopping bag in each hand - > at this moment almost anyone would prefer the door not being there :) > > This one would be for cases where you want best multi-layer > protections also against unknown threats and are ready to trade some > convenience for security. Also it would not be that bad once you use > automated deployment pipelines which just need an extra line to unlock > superuser for deployment. > > > > and I > > > think it would also be difficult to implement correctly. I don't think > > > you can get by with just having superuser() return false sometimes > > > despite pg_authid.rolsuper being true. There's a lot of subtle > > > assumptions in the code to the effect that the properties of a session > > > are basically stable unless some SQL is executed which changes things. > > A good barrier SQL for this could be SET ROLE=... . > Maybe just have a mode where a superuser can not log in _or_ SET ROLE > unless this is explicitly allowed in pg_superuser.conf > > > > I think if we start injecting hacks like this it may seem to work in > > > light testing but we'll never get to the end of the bug reports. > > In this case it looks like each of these bug reports would mean an > avoided security breach which for me looks preferable. > > Again, this would be all optional, opt-in, DBA-needs-to-set-it-up > feature for the professionally paranoid and not something we suddenly > force on people who would like to run all their databases using > user=postgres database=postgres with trust specified in the > pg_hba.conf "because the firewall takes care of security" :) > > > Yeah, seems it would have to be specified per-session, but how would you > > specify a specific session before the session starts? > > One often recommended way to do superuser'y things in a secure > production database is to have a non-privileged NOINHERIT user for > logging in and then do > SET ROLE=<superuserrole>; > when needed, similar to using su/sudo in shell. This practice both > reduces the attack surface and also provides auditability by knowing > who logged in for superuser work. > > In this case one could easily get the pid and do the needed extra > setup before escalating privileges to superuser. > > --- > Hannu