Re: Additional role attributes && superuser review

José,

* José Luis Tallón (jltallon(at)adv-solutions(dot)net) wrote:
> On 12/30/2014 04:16 PM, Stephen Frost wrote:
> >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".

Not sure I see how..? Can you clarify what you think needs to be
changed in the existing privilege system?

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

Not entirely following what you mean here either, but to the extent that
it's independent from the current discussion, perhaps it deserves to be
on its own thread?

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

How would a COPY-to-STDOUT privilege be different from SELECT?

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

One of the challenges to adding such a role is defining what it means.
What privileges do you think such a role would have? I can see that
perhaps it would include read-only access to everything, but I'd want it
to also have read access to the logs..

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

I think I'm coming around to the EXCLUSIVEBACKUP option, per the
discussion with Magnus. I don't particularly like LOGBACKUP and don't
think it really makes sense, while PHYSBACKUP seems like it'd cover what
REPLICATION already does.

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

I see what you mean that it's a bit strange for pg_basebackup to require
the REPLICATION role attribute, but, well, that's already the case, no?
Don't think we're going to change that..

> 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)?

Yeah, Magnus didn't like ARCHIVE either and I understand his reasoning.

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

Yeah, Magnus was also agreeing that they should be independent.

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

Right, think Magnus and I clarified what was meant there.

> >Yes. My thinking on how to approach this was to forcibly set all of
> >their transactions to read-only.
>
> So "READONLY" ?

Right.

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

I think a lot of people would like to see that, though I do think it's
at least somewhat independent from this particular proposal.

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

Well, I would argue that we're making a good set of 'low-level'
capabilities which users are then able to pull together as they see fit
into specific 'roles' for their environment. One environment might see
a DBA role as having X, Y, Z, while another only wants X and Y.

> I am hereby volunteering to compile this thread into some wiki page.
> I'm thinking "Privileges" as the title for starters. Suggestions?

A wiki page would certainly be useful, especially if it's done in such a
way that we can take and make it into the documentation to go along with
these role attributes easily.

Thanks!

Stephen