Re: Additional role attributes && superuser review

On 12/29/14, 4:01 PM, Stephen Frost wrote:
> Adam,
>
> * Adam Brightwell (adam(dot)brightwell(at)crunchydatasolutions(dot)com) wrote:
>>> I'd suggest it's called DUMP if that's what it allows, to keep it separate
>>> from the backup parts.
>>
>> Makes sense to me.
>
> I'm fine calling it 'DUMP', but for different reasons.
>
> We have no (verifiable) idea what client program is being used to
> connect and therefore we shouldn't try to tie the name of the client
> program to the permission.
>
> 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).

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

> Similairly, DUMP shouldn't imply BACKUP or visa-versa. In particular,
> I'd like to see roles which have only the BACKUP privilege be unable to
> directly read any data (modulo things granted to PUBLIC). This would
> allow an unprivileged user to manage backups, kick off ad-hoc ones, etc,
> without being able to actually access any of the data (this would
> require the actual backup system to have a similar control, but that's
> entirely possible with more advanced SANs and enterprise backup
> solutions).

Yes, since a binary backup doesn't need to actually "read" data, it shouldn't implicitly allow a user granted that role to either.

>> Given this clarification:
>>
>> I think #1 could certainly be answered by using DUMP. I have no strong
>> opinion in either direction, though I do think that BACKUP does make the
>> most sense for #2. Previously, Stephen had mentioned a READONLY capability
>> that could effectively work for pg_dump, though, Jim's suggestion of
>> keeping 'read-all' separate from 'ability to pg_dump' seems logical. In
>> either case, I certainly wouldn't mind having a wider agreement/consensus
>> on this approach.
>
> 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.

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

>> So, here is a revised list of proposed attributes:
>>
>> * 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.

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.

My how this has become a can of worms...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com