Re: Delegating superuser tasks to new security roles (Was: Granting control of SUSET gucs to non-superusers)

On Thu, Jul 22, 2021 at 3:00 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> I'm a bit on the fence about the documentation side... I could be
> convinced either way, really, but I generally agree that it'd be good to
> pick one and be consistent. I don't think the places where we do/don't
> mention it were done for any particular reason.
>
> > So I don't know. At the very least I think we should not do it as an
> > "or" in the code; what we want to do in comments and documentation I'm
> > less sure.
>
> Agreed.

Thanks for weighing in.

> > I also tend to think that functions like pg_read_file() ought to come
> > with execute permission pre-granted, with grant option, to
> > pg_host_security, and perhaps similarly for adminpack.
>
> When it comes to these- we already have pg_read_server_files and
> pg_write_server_files, so I'm not sure I see why it'd make sense to have
> another thing that grants filesystem access like this..?

It's awkward. I think that we can't afford to create a separate
predefined role for every single thing that someone could
theoretically want to sever, because then we'll have a zillion of them
and it will be unmaintainable. So the idea was to try to break up
everything someone might want to do either via DDL or by setting GUCs
into one of three categories: internal to the database
(pg_database_security), facing outward toward the network
(pg_network_security), and facing inward toward the host
(pg_host_security). If we didn't have any predefined security-related
roles already, this would still have complications, but as things
stand it has more, because as you point out, pg_read_server_files
overlaps with pg_host_security. But what do we do about that? Neither
pg_read_server_files nor pg_write_server_files covers the ability to
create tablespaces or set log_directory, but I think it's more
desirable to lump those things together in one bucket than to have a
ton of separate buckets for each individual thing. I guess one option
would to grant the existing roles pg_read_server_files and
pg_write_server_files to the new pg_host_security role, or whatever we
decide to call it (pg_access_server_filesystem?
pg_administer_server_files? pg_hack_postgres_account?). But I'm open
to suggestions. See also below here on the overall intent.

> I'm also left wondering if this doesn't end up introducing opportunities
> for someone with this role to become superuser pretty easily. Maybe it
> does and maybe we're ok with that, but I would think that it'd be really
> useful to have a role that can't become superuser easily which can
> access/modify most objects in the system.

Creating something like that is precisely the intention here because,
like you, I think that would be extremely handy. If it's possible for
that role to become superuser, we've lost the plot.

> I don't really see either of those as being filesystem changing things.

I think the thought process here was that if you are a managed
services provider you would not want the user to change
zero_damaged_pages or wal_sync_method or things like that because that
stuff is the provider's responsibility; similar for the recovery
settings. But yes ... we need something better here, I think.

> It's often the case that logging/auditing are handled by a different
> group than those who might be creating/modifying objects. Yet another
> group is often the group that actually handles granting access. Broad
> classes being:
>
> - Users
> - Auditors (controls what's logged, what is audited, etc)
> - Security (controls who has access to what)
>
> Note that 'security' and 'auditors' shouldn't have access to the actual
> data either, or have the ability to do things like modify data. Not
> sure all of this quite fits what we're going for here but figured it
> might help with sorting out what other buckets we need.

Hmm, interesting point. The division between the "security" group, who
I suppose would be the DBA, and the "auditors" group is one I had
thought about only slightly.

--
Robert Haas
EDB: http://www.enterprisedb.com