Re: Privilege boundary between sysadmin and database superuser [Was: Re: pg_amcheck option to install extension] - Mailing list pgsql-hackers

From Stephen Frost
Subject Re: Privilege boundary between sysadmin and database superuser [Was: Re: pg_amcheck option to install extension]
Date
Msg-id 20210421202435.GR20766@tamriel.snowman.net
Whole thread Raw
In response to Re: Privilege boundary between sysadmin and database superuser [Was: Re: pg_amcheck option to install extension]  (Mark Dilger <mark.dilger@enterprisedb.com>)
List pgsql-hackers
Greetings,

* Mark Dilger (mark.dilger@enterprisedb.com) wrote:
> > On Apr 20, 2021, at 3:19 PM, Tom Lane <tgl@sss.pgh.pa.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
ableto do any database thing he wants within the PostgreSQL environment, but doesn't want that to leak out as an
abilityto run arbitrary stuff on the system, not even if it's just stuff running as bog-standard user "postgres".  In
myview, Alice can accomplish this goal using a very tightly designed container, but it is far from easy to do and to
getright.  

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

Attachment

pgsql-hackers by date:

Previous
From: Thomas Munro
Date:
Subject: Re: WIP: WAL prefetch (another approach)
Next
From: Andres Freund
Date:
Subject: Re: Bogus collation version recording in recordMultipleDependencies