Re: Additional role attributes && superuser review - Mailing list pgsql-hackers

On 12/29/14, 7:22 PM, Stephen Frost wrote:
>>> 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.

READ MOST? ;)

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

Good, so really DUMP and READ ALL are the same.

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

Yeah... it does indicate that we shouldn't call this thing DUMP, but perhaps as long as we put appropriate HINTS in the
errorpaths that pg_dumpall would hit it's OK to call it DUMP. (My specific concern is it's natural to assume that DUMP
wouldinclude pg_dumpall).
 


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

BINARY? Though that's pretty generic. STREAM might be better, even though it's not exactly accurate for a simple
backup.

Perhaps this is just a documentation issue, but there's enough caveats floating around here that I'm reluctant to rely
onthat.
 

>> >Likewise, if we segregate "DUMP" and BYPASSRLS then I think we need to call DUMP something else. Otherwise, it's a
massivefoot-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.

This also indicates DUMP is better than something like READ ALL, if we can handle the pg_dumpall case.

Based on all this, my inclination is DUMP with appropriate hints for pg_dumpall, and BINARY.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com