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
Description: Digital signature