Re: Additional role attributes && superuser review

On 10/16/14, 10:47 AM, Stephen Frost wrote:
>>>> As others have commented, I too think this should support pg_dump.
>>> > >
>>> > >I'm uttly mystified as to what that*means*. Everyone asking for it is
>>> > >great but until someone can define what "support pg_dump" means, there's
>>> > >not much progress I can make towards it..
>> >
>> >To me, what this repeated discussion on this particular BACKUP point
>> >says, is that the ability to run pg_start/stop_backend and the xlog
>> >related functions should be a different privilege, i.e. something other
>> >than BACKUP; because later we will want the ability to grant someone the
>> >ability to run pg_dump on the whole database without being superuser,
>> >and we will want to use the name BACKUP for that. So I'm inclined to
>> >propose something more specific for this like WAL_CONTROL or
>> >XLOG_OPERATOR, say.
> Ok. Not sure that I see 'XLOG_OPERATOR' as making sense for
> 'pg_start_backup' though, and I see the need to support pg_dump'ing the
> whole database as a non-superuser being more like a 'READONLY' kind of
> role.
>
> We've actually already looked at the notion of a 'READONLY' or 'READALL'
> role attribute and, well, it's quite simple to implement.. We're
> talking about a few lines of code to implicitly allow 'USAGE' on all
> schemas, plus a minor change in ExecCheckRTEPerms to always (or perhaps
> *only*) include SELECT rights. As there's so much interest in that
> capability, perhaps we should add it..
>
> One of the big question is- do we take steps to try and prevent a role
> with this attribute from being able to modify the database in any way?
> Or would this role attribute only ever increase your rights?

Let me address the pg_dump case first, because it's simpler. I want a way to allow certain roles to successfully run pg_dump without being superuser and without having to manually try and maintain a magic read-all role. Note that pg_dump might (today or in the future) need more than just read-all so it's probably wise to treat the two features (pg_dump vs a plain read-all) as separate.

For PITR, I see 3 different needs:

1) The ability for someone to start a backup, and if needed cancel that backup
2) The ability to manage running backups/archiving
3) The ability to actually setup/modify PITR infrastructure

#2 is probably a weak case that may not be needed; I include it because someone mentioned stopping a backup that someone else started.

I think #3 should just require superuser.

#1 is what you'd want a more junior person to handle. "Bob needs a snapshot of cluster A". This role needs to be able to run everything you need to get that backup started, monitor it, and cancel if needed.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com