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

I was not after *completely* removing it, but just having an option
which makes the superuser() function always return false.

For known cases of needing a superuser there would be a way to enable
it , perhaps via a sentinel file or pg_hba-like configuration file.

And as first cut I would advocate for disabling SECURITY DEFINER
functions for simplicity and robustness of defense. A short term
solution for these would be to re-write them in C (or Go or Rust or
any other compiled language) as C functions have full access anyway.
But C functions fall in the same category as other defenses discussed
at the start of this thread - namely to use them, you already need
access to the file system.

Running production databases without superuser available is not as
impossible as you may think - Cloud SQL version of PostgreSQL has
been in use with great success for years without exposing a real
superuser to end users (there are some places where
`cloudsqlsuperuser` gives you partial superuser'y abilities).

Letting user turn off the superuser access when no known need for it
exists (which is 99.9% in must use cases) would improve secondary
defenses noticeably.

It would also be a good start to figuring out the set of roles into
which one can decompose superuser access in longer run

--
Hannu

On Tue, Jun 28, 2022 at 8:30 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Mon, Jun 27, 2022 at 5:37 PM Hannu Krosing <hannuk(at)google(dot)com> wrote:
> > My current thinking is (based on more insights from Andres) that we
> > should also have a startup flag to disable superuser altogether to
> > avoid bypasses via direct manipulation of pg_proc.
> >
> > Experience shows that 99% of the time one can run PostgreSQL just fine
> > without a superuser, so having a superuser available all the time is
> > kind of like leaving a loaded gun on the kitchen table because you
> > sometimes need to go hunting.
> >
> > I am especially waiting for Andres' feedback on viability this approach.
>
> Well, I'm not Andres but I don't think not having a superuser at all
> is in any way a viable approach. It's necessary to be able to
> administer the database system, and the bootstrap superuser can't be
> removed outright in any case because it owns a ton of objects.
>
> There are basically two ways of trying to solve this problem. On the
> one hand we could try to create a mode in which the privileges of the
> superuser are restricted enough that the superuser can't break out to
> the operating system. The list of things that would need to be blocked
> is, I think, more extensive than any list you've give so far. The
> other is to stick with the idea of an unrestricted superuser but come
> up with ways of giving a controlled subset of the superuser's
> privileges to a non-superuser. I believe this is the more promising
> approach, and there have been multiple discussion threads about it in
> the last six months.
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com