Re: Additional role attributes && superuser review

On Mon, Dec 29, 2014 at 11:01 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

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

Well, it would not give you full USAGE - granted USAGE on a schema, you can
execute functions in there for example (provided permissions). This
privilege would not do that, 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)

But other than that I agree - for *all* these privileges, it needs to be
clearly documented that they are not limited to a specific client side
application, even if their name happens to be similar to one.

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
>

I think BYPASSRLS would have to be implicitly granted by the DUMP
privilege. Without that, the DUMP privilege is more or less meaningless
(for anybody who uses RLS - but if they don't use RLS, then having it
include BYPASSRLS makes no difference). Worse than that, you may end up
with a dump that you cannot restore.

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.

should work 'sanely' with any combination of the two options.
> 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).
>

So you're saying a privilege that would allow you to do
pg_start_backup()/pg_stop_backup() but *not* actually use pg_basebackup?
That would be "EXCLUSIVEBACKUP" or something like that, to be consistent
with existing terminology though.

> > That seems really bad names, IMHO. Why? Because we use WAL and XLOG
> > > throughout documentation and parameters and code to mean *the same
> thing*.
> > > And here they'd suddenly mean different things. If we need them as
> separate
> > > privileges, I think we need much better names. (And a better
> description -
> > > what is "xlog operations" really?)
> > >
> >
> > Fair enough, ultimately what I was trying to address is the following
> > concern raised by Alvaro:
> >
> > "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."
>
> Note that the BACKUP role attribute was never intended to cover the
> pg_dump use-case. Simply the name of it caused confusion though. I'm
> not sure if adding a DUMP role attribute is sufficient enough to address
> that confusion, but I haven't got a better idea either.
>

We need to separate the logical backups (pg_dump) from the physical ones
(start/stop+filesystem and pg_basebackup). We might also need to separate
the two different ways of doing physical backups.

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?

> When indeed, what it meant was to have the following separate (effectively
> > merging #2 and #3):
> >
> > 1) ability to pg_dump
> > 2) ability to start/stop backups *and* ability to execute xlog related
> > functions.
>

We probably also need to define what those "xlog related functions"
actually arse. pg_current_xlog_location() is definitely an xlog related
function, but does it need the privilege? pg_switch_xlog()?
pg_start_backup()? pg_xlog_replay_pause()?

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.

That sounds reasonable to me (and is what was initially proposed, though
> I've come around to the thinking that this BACKUP role attribute should
> also allow pg_xlog_replay_pause/resume(), as those can be useful on
> replicas).
>

If it's for replicas, then why are we not using the REPLICATION privilege
which is extremely similar to this?

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

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

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.

(We could dump all the users *without* passwords with just the DUMP
privilege)

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/