Re: Privilege boundary between sysadmin and database superuser [Was: Re: pg_amcheck option to install extension]

Greetings,

* Mark Dilger (mark(dot)dilger(at)enterprisedb(dot)com) wrote:
> > On Apr 20, 2021, at 3:19 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > The rest of your analysis seems a bit off-point to me, which is what
> > makes me think that one of us is confused. If Alice is storing her
> > data in a Postgres database, she had better trust both the Postgres
> > superuser and the box's administrators ... otherwise, she should go
> > get her own box and her own Postgres installation.
>
> It is the other way around. Alice is the operating system administrator who doesn't trust Bob. She wants Bob to be able to do any database thing he wants within the PostgreSQL environment, but doesn't want that to leak out as an ability to run arbitrary stuff on the system, not even if it's just stuff running as bog-standard user "postgres". In my view, Alice can accomplish this goal using a very tightly designed container, but it is far from easy to do and to get right.

Then Bob doesn't get to be a superuser.

There's certainly some capabilities that aren't able to be GRANT'd out
today and which are reserved for superusers, but there's been ongoing
work to improve on that situation (pg_read_all_data being one of the
recent improvements in this area, in fact...). Certainly, if others are
interested in that then it'd be great to have more folks working to
improve the situation.

We do need to make it clear when a given capability isn't intended to
allow a user who has that capability to be able to become a superuser
and when the capability itself means that they would be able to. The
predefined role 'pg_execute_server_program' grants out the capability to
execute programs on the server, which both allows a user to become a
superuser if they wished, and goes against your stated requirement that
Bob isn't allowed to do that, so that predefined role shouldn't be
GRANT'd to Bob.

The question is: what do you wish Bob could do, as a non-superuser, that
Bob isn't able to do today? Assuming that there's a set of capabilities
there that both wouldn't allow Bob to become a superuser (which implies
that Bob can't do things like execute arbitrary programs or read/write
arbitrary files on the server) and which would allow Bob to perform the
actions you'd like Bob to be able to do, it's mostly a matter of
programming to make it happen...

Thanks,

Stephen