Re: Additional role attributes && superuser review

Adam,

* Adam Brightwell (adam(dot)brightwell(at)crunchydatasolutions(dot)com) wrote:
> > I'd suggest it's called DUMP if that's what it allows, to keep it separate
> > from the backup parts.
>
> Makes sense to me.

I'm fine calling it 'DUMP', but for different reasons.

We have no (verifiable) idea what client program is being used to
connect and therefore we shouldn't try to tie the name of the client
program to the permission.

That said, a 'DUMP' privilege which allows the user to dump the contents
of the entire database is entirely reasonable. We need to be clear in
the documentation though- such a 'DUMP' privilege is essentially
granting USAGE on all schemas and table-level SELECT on all tables and
sequences (anything else..?). To be clear, a user with this privilege
can utilize that access without using pg_dump.

One other point is that this shouldn't imply any other privileges, imv.
I'm specifically thinking of BYPASSRLS- that's independently grantable
and therefore should be explicitly set, if it's intended. Things
should work 'sanely' with any combination of the two options.
Similairly, DUMP shouldn't imply BACKUP or visa-versa. In particular,
I'd like to see roles which have only the BACKUP privilege be unable to
directly read any data (modulo things granted to PUBLIC). This would
allow an unprivileged user to manage backups, kick off ad-hoc ones, etc,
without being able to actually access any of the data (this would
require the actual backup system to have a similar control, but that's
entirely possible with more advanced SANs and enterprise backup
solutions).

> > That seems really bad names, IMHO. Why? Because we use WAL and XLOG
> > throughout documentation and parameters and code to mean *the same thing*.
> > And here they'd suddenly mean different things. If we need them as separate
> > privileges, I think we need much better names. (And a better description -
> > what is "xlog operations" really?)
> >
>
> Fair enough, ultimately what I was trying to address is the following
> concern raised by Alvaro:
>
> "To me, what this repeated discussion on this particular BACKUP point
> says, is that the ability to run pg_start/stop_backend and the xlog
> related functions should be a different privilege, i.e. something other
> than BACKUP; because later we will want the ability to grant someone the
> ability to run pg_dump on the whole database without being superuser,
> and we will want to use the name BACKUP for that. So I'm inclined to
> propose something more specific for this like WAL_CONTROL or
> XLOG_OPERATOR, say."

Note that the BACKUP role attribute was never intended to cover the
pg_dump use-case. Simply the name of it caused confusion though. I'm
not sure if adding a DUMP role attribute is sufficient enough to address
that confusion, but I haven't got a better idea either.

> When indeed, what it meant was to have the following separate (effectively
> merging #2 and #3):
>
> 1) ability to pg_dump
> 2) ability to start/stop backups *and* ability to execute xlog related
> functions.

That sounds reasonable to me (and is what was initially proposed, though
I've come around to the thinking that this BACKUP role attribute should
also allow pg_xlog_replay_pause/resume(), as those can be useful on
replicas).

> Given this clarification:
>
> I think #1 could certainly be answered by using DUMP. I have no strong
> opinion in either direction, though I do think that BACKUP does make the
> most sense for #2. Previously, Stephen had mentioned a READONLY capability
> that could effectively work for pg_dump, though, Jim's suggestion of
> keeping 'read-all' separate from 'ability to pg_dump' seems logical. In
> either case, I certainly wouldn't mind having a wider agreement/consensus
> on this approach.

The read-all vs. ability-to-pg_dump distinction doesn't really exist for
role attributes, as I see it (see my comments above). That said, having
DUMP or read-all is different from read-*only*, which would probably be
good to have independently. I can imagine a use-case for a read-only
account which only has read ability for those tables, schemas, etc,
explicitly granted to it.

There is one issue that occurs to me, however. We're talking about
pg_dump, but what about pg_dumpall? In particular, I don't think the
DUMP privilege should provide access to pg_authid, as that would allow
the user to bypass the privilege system in some environments by using
the hash to log in as a superuser. Now, I don't encourage using
password based authentication, especially for superuser accounts, but
lots of people do. The idea with these privileges is to allow certain
operations to be performed by a non-superuser while preventing trivial
access to superuser. Perhaps it's pie-in-the-sky, but my hope is to
achieve that.

> So, here is a revised list of proposed attributes:
>
> * BACKUP - allows role to perform backup operations (as originally proposed)
> * LOG - allows role to rotate log files - remains broad enough to consider
> future log related operations
> * DUMP - allows role to perform pg_dump* backups of whole database
> * MONITOR - allows role to view pg_stat_* details (as originally proposed)
> * PROCSIGNAL - allows role to signal backend processes (as originally
> proposed)well

Looks reasonable to me.

Thanks!

Stephen