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 <hannuk@google.com> wrote:
>
> On Thu, Jun 30, 2022 at 7:25 PM Bruce Momjian <bruce@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
