Re: Additional role attributes && superuser review

On Tue, Dec 30, 2014 at 6:35 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> * Amit Kapila (amit(dot)kapila16(at)gmail(dot)com) wrote:
> > On Tue, Dec 30, 2014 at 6:52 AM, Stephen Frost <sfrost(at)snowman(dot)net>
wrote:
>
> > another could be have a separate privilege (DBA) or a role which is
> > not a superuser, however can be used to perform such tasks.
>
> 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. 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.

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.

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 don't really like 'xlog' as a permission. BINARY BACKUP is
> > > interesting but if we're going to go multi-word, I would think we
would
> > > do PITR BACKUP or something FILESYSTEM BACKUP or similar. I'm not
> > > really a big fan of the two-word options though.
> >
> > 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). 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)?

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com