On Wed, May 25, 2022 at 2:28 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm a little skeptical that our present design direction really moves
> the needle very far in this area. We've sliced and diced superuser
> aplenty, but that doesn't make individual capabilities such as
> pg_write_all_data or ALTER SYSTEM any less dangerous from the standpoint
> of someone trying to prevent breaking out.
We have really not sliced and diced superuser in any serious way. I'm
not here to say that the existing predefined roles are useless,
especially the more powerful ones like pg_read_all_data, but I don't
think "primitive" would be an unfair characterization. The problem is
twofold. On the one hand, you can't delegate all of the things that
the server can do - in particular, and I think this is the really
important thing, the superuser's unique ability to administer objects
inside the database. On the other hand, you can only delegate
privileges on an all-or-nothing basis. You either have the predefined
role or you don't. In the case of something like pg_read_all_data,
that's fine, because it's equivalent to SELECT privileges on every
table, which are separately grantable if you prefer. But it's a little
less obviously sufficient for things like pg_read_server_files where,
we must hope, you're OK with granting access to all or none of them,
and it's clearly insufficient for administration of objects in the
database.
Hence the whole "CREATEROLE and role ownership hierarchies" thread,
which strikes me as as way to make some really meaningful progress
toward a future in which you don't have to be superuser to do a useful
amount of database administration. Unfortunately that discussion was
less productive than I think it could have been.
--
Robert Haas
EDB: http://www.enterprisedb.com