* Jim Nasby (jim.na...@bluetreble.com) wrote:
> On 12/29/14, 4:01 PM, Stephen Frost wrote:
> >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).

Did you favor something else below..?  I don't see it.

> >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...

That really depends on your view of the privilege.  I'm inclined to view
it from the more-tightly-constrained point of view which matches up with
what I anticipate it actually providing.  That doesn't translate into a
short name very well though, unfortunately.

> >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.

The locks aren't any more extensive than regular selects, it's just that
the entire pg_dump works inside of one large transaction which lasts a
good long time and simply that can cause issues with high-rate update

> >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. :(

I don't think it kills the whole idea, but we do need to work out how to
address it.

> >>* 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
> 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.

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.

> 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.

That's actually already dealt with.  pg_dump defaults to setting the
row_security GUC to 'off', in which case you'll get an error if you hit
a table that has RLS enabled and you don't have BYPASSRLS.  If you're
not checking for errors from pg_dump, well, there's a lot of ways you
could run into problems.

> My how this has become a can of worms...

I'm not sure it's as bad as all that, but it does require some



Attachment: signature.asc
Description: Digital signature

Reply via email to