Jim, * 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
systems.
> >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
thinking..
Thanks,
Stephen
signature.asc
Description: Digital signature
