Re: Additional role attributes && superuser review

Jim,

* Jim Nasby (Jim(dot)Nasby(at)BlueTreble(dot)com) wrote:
> On 12/29/14, 4:01 PM, Stephen Frost wrote:
> >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).

Did you favor something else below..? I don't see it.

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

That really depends on your view of the privilege. I'm inclined to view
it from the more-tightly-constrained point of view which matches up with
what I anticipate it actually providing. That doesn't translate into a
short name very well though, unfortunately.

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

The locks aren't any more extensive than regular selects, it's just that
the entire pg_dump works inside of one large transaction which lasts a
good long time and simply that can cause issues with high-rate update
systems.

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

I don't think it kills the whole idea, but we do need to work out how to
address it.

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

I don't really like 'xlog' as a permission. BINARY BACKUP is
interesting but if we're going to go multi-word, I would think we would
do PITR BACKUP or something FILESYSTEM BACKUP or similar. I'm not
really a big fan of the two-word options though.

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

That's actually already dealt with. pg_dump defaults to setting the
row_security GUC to 'off', in which case you'll get an error if you hit
a table that has RLS enabled and you don't have BYPASSRLS. If you're
not checking for errors from pg_dump, well, there's a lot of ways you
could run into problems.

> My how this has become a can of worms...

I'm not sure it's as bad as all that, but it does require some
thinking..

Thanks,

Stephen