Re: [HACKERS] Superowners

Simon,

* Simon Riggs (simon@2ndquadrant.com) wrote:
> On 27 January 2017 at 22:48, Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> wrote:
> > On 1/26/17 1:25 PM, Simon Riggs wrote:
> >> That should include the ability to dump all objects, yet without any
> >> security details. And it should allow someone to setup logical
> >> replication easily, including both trigger based and new logical
> >> replication. And GRANT ON ALL should work.
> >
> > This basically sounds like a GRANT $privilege ON ALL $objecttype TO
> > $user.  So you could have a user that can read everything, for example.
> >
> > This kind of thing has been asked for many times, but that quieted down
> > when the default privileges feature appeared.  I think it would still be
> > useful.
>
> I would call these "super privileges".
>
> Peter suggests that we have a much more flexible structure for super-privileges.
>
> In Peter's model, Tom's suggestion woud be to grant all of these
> automatically to database owners.
> GRANT ALL ON ALL TABLES TO $user
> GRANT ALL ON ALL SEQUENCES TO $user
> GRANT ALL ON ALL FUNCTIONS TO $user

We need to make sure that we're actually talking about the same things
here, because we've now shifted from ownership-like privileges to those
privielges which can be GRANT'd, and the two are far from the same.

The above model of GRANT ALL ON ALL TABLES TO $user; would *not*, for
example, GRANT the right to drop the object, or to ALTER it, as those
are not currently GRANT'able privileges.

> Either of them would be good for me, as long as we implement the rule
> as Tom suggests that this would never apply to objects owned by a
> superuser.

Things are not quite so simple as this- part of the point of a read-only
role would be to allow that role to perform pg_dump-based backups.  If
the above GRANT system does not apply to catalog tables, then trying to
create such a user by using:

GRANT SELECT ON ALL TABLES TO readonly;

wouldn't cover pg_authid and pg_dumpall would fail.

Of course, pg_authid is a shared catalog and we're talking about
per-database things here, for the most part, so perhaps that's not quite
the same, but what about extensions which are installed by a superuser
which include config tables that need to be dumped out?

> After thinking about this, the things I care about are
>
> * That super-privileges can be executed efficiently at run-time. If
> they cause major overheads, especially in cases where they have not
> been granted then we would have a big problem.

This seems to play into a question regarding the implementation which
then leads into user expectations a bit, I believe.

Would these super-privileges be seen when doing a \dp on a table?  Are
we going to record the super-privileges into every object which exists
when the command is called and then also into new objects when they're
created?  Or are we going to have an additional catalog lookup for every
privilege check?

At first blush, I tend to think the answers are:

- They exist as an independent catalog
- Yes, super-privs are shown in \dp, but through psql doing the necessary lookups/joins to combine the ACL info.
- We check normal privileges first and fall back to looking for super-privileges if the caller doesn't have any
non-superprivileges on the table.  This would hopefully minimize the impact of having these additional checks, at least
onexisting systems. 

> * Some super-privileges should exist by default. If super-privileges
> exist only when granted by superusers and we have no access to
> superusers then they never will exist, so they will be mostly useless
> in many cases.

We should at least consider if we really want upgrades to automatically
set up these super-privileges or not.

> * This model is needed now, or at least change in this area will be
> easier because of PG10.0

This doesn't strike me as a terribly good argument.  I'm sure we could
do this for PG11 too.

> Neither of those things make me think deferring this and allowing a
> student to work on it in the future would be a useful outcome. The
> task looks to be a small amount of dev, a medium amount of design and
> a lot of checking and documentation.

For my 2c, at least, that makes it sound like a very good project for a
student to work on.  That said, I'm not against doing it now, if someone
wants to spend the time to do so.

> So my proposal is to implement what Tom suggests now, which I will now
> describe as automatic super-privileges for database owning roles. And
> we can implement a more flexible scheme for explicit GRANTs to other
> roles in later releases.

I don't believe doing something quick now will lead us to a place where
we will easily be able to make it more flexible later.  I'm much more
concerned that we'll end up painting ourselves into a corner that's
difficult to get out of as privileges are, generally speaking, not
something we can really whack around from release to release.  They're
extremely user-visible and any issues will almost certainly lead to
security releases.

Thanks!

Stephen