On 12/29/14, 4:01 PM, Stephen Frost wrote:

* Adam Brightwell (adam.brightw...@crunchydatasolutions.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.

Yeah... it may be better to call this something other than DUMP (see below).

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.

That does violate POLA, but it's probably worth doing so...

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

Yes, since a binary backup doesn't need to actually "read" data, it shouldn't 
implicitly allow a user granted that role to either.

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.

My specific concern about DUMP vs read all/only is IIRC dump needs to do more 
extensive locking than regular selects do, which can cause production problems.

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.

Ugh, hadn't thought about 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

Given the confusion that can exist with the data reading stuff, perhaps BINARY 
BACKUP or XLOG would be a better term, especially since this will probably have 
some overlap with streaming replication.

Likewise, if we segregate "DUMP" and BYPASSRLS then I think we need to call DUMP 
something else. Otherwise, it's a massive foot-gun; you get a "successful" backup only to 
find out it contains only a small part of the database.

My how this has become a can of worms...
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to