Re: Additional role attributes && superuser review

On 12/30/2014 04:16 PM, Stephen Frost wrote:
> [snip]
> The approach I was thinking was to document and implement this as
> impliciting granting exactly USAGE and SELECT rights, no more (not
> BYPASSRLS) and no less (yes, the role could execute functions). I agree
> that doing so would be strictly more than what pg_dump actually requires
> but it's also what we actually have support for in our privilege system.

Hmmm.... coupled with your comments below, I'd say some tweaking of the
existing privileges is actually needed if we want to add these new
"capabilities".

BTW, and since this is getting a bit bigger than originally considered:
would it be interesting to support some extension-defined capabilities, too?
(for things can't be easily controlled by the existing USAGE /
SELECT / ... rights, I mean)

>> it would only give you COPY access. (And also
>> COPY != SELECT in the way that the rule system applies, I think? And this
>> one could be for COPY only)
> COPY certainly does equal SELECT rights.. We haven't got an independent
> COPY privilege and I don't think it makes sense to invent one.

FWIW, a COPY(DUMP?) privilege different from SELECT would make sense.
Considering your below comments it would be better that it not imply
BYPASSRLS, though.

> It
> sounds like you're suggesting that we add a hack directly into COPY for
> this privilege, but my thinking is that the right place to change for
> this is in the privilege system and not hack it into individual
> commands.. I'm also a bit nervous that we'll end up painting ourselves
> into a corner if we hack this to mean exactly what pg_dump needs today.
Agreed.

> Lastly, I've been considering other use-cases for this privilege beyond
> the pg_dump one (thinking about auditing, for example).

ISTR there was something upthread on an AUDIT role, right?
This might be the moment to add it....

> [snip]
>> Similar concerns would exist for the existing REPLICATION role for example
>> - that one clearly lets you bypass RLS as well, just not with a SQL
>> statement.
> I'm not sure that I see the point you're making here. Yes, REPLICATION
> allows you to do a filesystem copy of the entire database and that
> clearly bypasses RLS and *all* of our privilege system. I'm suggesting
> that this role attribute work as an implicit grant of privileges we
> already have. That strikes me as easy to document and very clear for
> users.

+1
> [snip]
> So you're saying a privilege that would allow you to do
> pg_start_backup()/pg_stop_backup() but *not* actually use pg_basebackup?
> Yes.
>
>> That would be "EXCLUSIVEBACKUP" or something like that, to be consistent
>> with existing terminology though.
> Ok. I agree that working out the specific naming is difficult and would
> like to focus on that, but we probably need to agree on the specific
> capabilities first. :)

Yes :)
For the record, LOGBACKUP vs PHYSBACKUP was suggested a couple days ago.
I'd favor DUMP(logical) and BACKUP(physical) --- for lack of a better name.
The above reasoning would have pg_basebackup requiring REPLICATION,
which is a logical consequence of the implementation but strikes me as a
bit surprising in the light of these other privileges.

>
> [snip]
>> Personalyl I think using the DUMP name makes that a lot more clear. Maybe
>> we need to avoid using BACKUP alone as well, to make sure it doesn't go the
>> other way - using BASEBACKUP and EXCLUSIVEBACKUP for those two different
>> ones perhaps?
> DUMP - implicitly grants existing rights, to facilitate pg_dump and
> perhaps some other use-cases
> BASEBACKUP - allows pg_basebackup, which means bypassing all in-DB
> privilege systems
> EXCLUSIVEBACKUP - just allows pg_start/stop_backup and friends
>
> I'm still not entirely happy with the naming, but I feel like we're
> getting there. One thought I had was using ARCHIVE somewhere, but I
> kind of like BASEBACKUP meaning what's needed to run pg_basebackup, and,
> well, we say 'backup' in the pg_start/stop_backup function names, so
> it's kind of hard to avoid that. EXCLUSIVEBACKUP seems really long tho.

ARCHIVE, though completely appropriate for the "exclusivebackup" case
above (so this would become DUMP/BASEBACKUP/ARCHIVE +REPLICATION) might
end up causing quite some confusion ... ("what? WAL archiving is NOT
granted by the "archive" privilege, but requires a superuser to turn it
on(via ALTER SYSTEM)?

POLA again....
> I had defined them when I started the thread:
>
> pg_start_backup
> pg_stop_backup
> pg_switch_xlog
> pg_create_restore_point

... for "BACKUP" / EXCLUSIVEBACKUP (or, actually, FSBACKUP/PHYSBACKUP ...)
> Later I added:
>
> pg_xlog_replay_pause
> pg_xlog_replay_resume
>
> Though I'd be find if the xlog_replay ones were their own privilege (eg:
> REPLAY or something).
+1
>> I think just calling them "xlog related functions" is doing us a disservice
>> there. Definitely once we have an actual documentation to write for it, but
>> also in this discussion.
> [snip]
>> If it's for replicas, then why are we not using the REPLICATION privilege
>> which is extremely similar to this?
> I don't actually see REPLICATION as being the same.

REPLICATION (ability to replicate) vs REPLICAOPERATOR (ability to
control *the replica* or the R/O behaviour of the server, for that
matter...)

> The point is to have a role which can log into the replica, pause
> the stream to be able to run whatever queries they're permitted to
> (which might not include all of the data) and then resume it when done.
> Perhaps that needs to be independent of the EXCLUSIVEBACKUP role, but
> it's definitely different from the REPLICATION privilege.

Looks like it is.
>>> 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.
>> You mean something that restricts the user to read even *if* write
>> permissions has been granted on an individual table? Yeah, that would
>> actually be quite useful, I think - sort of a "reverse privilege".
> Yes. My thinking on how to approach this was to forcibly set all of
> their transactions to read-only.

So "READONLY" ?

>> 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.
>> Well, from an actual security perspective that would make it equivalent to
>> superuser in the case of anybody using password auth. I'm not sure we cant
>> to grant that out to DUMP by default - perhaps we need a separate one for
>> DUMPAUTH or DUMPPASSWORDS.
> That makes sense to me- DUMPAUTH or maybe just DUMPALL (to go with
> pg_dumpall).

+1
>> (We could dump all the users *without* passwords with just the DUMP
>> privilege)
> Agreed. That's actually something that I think would be *really* nice-
> an option to dump the necessary globals for whatever database you're
> running pg_dump against. We have existing problems in this area
> (database-level GUCs aren't considered database-specific and therefore
> aren't picked up by pg_dump, for example..), but being able to also dump
> the roles which are used in a given database with pg_dump would be
> *really* nice..

Ok, so this would imply modifying pg_dump to include database-level
configs. I would heartily welcome this.

So, it seems to me that we are actually evolving towards a set of
"low-level" "capabilities" and some "high-level" (use case-focused)
"privileges".
I am hereby volunteering to compile this thread into some wiki page. I'm
thinking "Privileges" as the title for starters. Suggestions?

Thanks,

/ J.L.