Re: Hardening PostgreSQL via (optional) ban on local file system access

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