Re: Additional role attributes && superuser review

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

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 error paths that pg_dumpall would hit it's OK to call it DUMP. (My specific concern is it's natural to assume that DUMP would include 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 on that.

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

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