Re: Additional role attributes && superuser review - Mailing list pgsql-hackers

Amit,

* Amit Kapila (amit.kapila16@gmail.com) wrote:
> On Tue, Dec 30, 2014 at 6:35 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > I'm pretty sure we've agreed that having a catch-all role attribute like
> > 'DBA' is a bad idea because we'd likely be adding privileges to it later
> > which would expand the rights of users with that attribute, possibly
> > beyond what is intended.
>
> Yes, that could happen but do you want to say that is the only reason
> to consider server level roles (such as DBA) a bad idea.

No, the other reason is that having more granular permissions is better
than catch-all's.  'superuser' is that catch-all today.

> Can't we make
> users aware of such things via documentation and then there will be
> some onus on user's to give such a privilege with care.

Perhaps, but if we're going to go in that direction, I'd rather have a
hierarchy which is built upon more granular options rather than assuming
we know exactly what the 'DBA' role should have for every environment.

> On looking around, it seems many of the databases provides such
> roles
> https://dbbulletin.wordpress.com/2013/05/29/backup-privileges-needed-to-backup-databases/
>
> In the link, though they are talking about physical (file level) backup,
> there is mention about such roles in other databases.

Most of this discussion is about non-physical-level-backups.  We have
the REPLICATION role attribute for physical-level-backups and I don't
think anyone wants to get rid of that in favor of pulling it into some
'DBA' role attribute.

> The other way as discussed on this thread is to use something like
> DUMPALL (or DUMPFULL) privilege which also sounds to be a good
> way, apart from the fact that the same privilege can be used for
> non-dump purposes to extract the information from database/cluster.

I think we're probably going to go with DUMPAUTH as the specific role
attribute privilege, to make it clear that it includes authentication
information.  That's really the only distinction between DUMP (or
whatever) and a privilege to support pg_dumpall.

This does make me think that we need to consider if this role attribute
implicitly grants CONNECT to all databases also.

> > > How about PHYSICAL BACKUP (for basebackup) and LOGICAL BACKUP
> > > for pg_dump?
> >
> > Again, this makes it look like the read-all right would be specific to
> > users executing pg_dump, which is incorrect and misleading.  "PHYSICAL"
> > might also imply the ability to do pg_basebackup.
>
> That's right, however having unrelated privileges for doing physical
> backup via pg_basebackup and pg_start*/pg_stop* method also
> doesn't sound to be the best way (can be slightly difficult for
> users to correlate after reading docs).

We should definitely segregate the privilege to run start/stop backup
and run pg_basebackup.  One allows you to read the database files off
the filesystem more-or-less directly and the other doesn't.  That's a
big difference.

> Don't you think in this case
> we should have some form of hierarchy for privileges (like user
> having privilege to do pg_basebackup can also perform the
> backup via pg_start*/pg_stop* method or some other way to relate
> both forms of physical backup)?

If we had a specific privilege for pg_basebackup then I would agree that
it would allow call pg_start/stop_backup.  The same is not true in
reverse though.
Thanks,
    Stephen