Re: Additional role attributes && superuser review

On Mon, Jan 4, 2016 at 4:56 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>> First, it's not really going to matter to users very much whether the
>> command to enable one of these features is a single GRANT command or a
>> short sequence of GRANT commands executed one after another. So even
>> if we don't have roles that bundle together multiple permissions, you
>> will still be able to do this; you just might need more than one
>> command. Probably, I'm guessing, not very many commands, but more
>> than one.
>
> I disagree. I find that it does make a difference to users to be using
> a well documented and clear approach over one which involves fiddling
> with the individual pieces to get everything to work, and if a new
> function comes along that is useful for backup users, that would have to
> also be granted, even if it would clearly be useful to a backup role.

How is that a fair way to characterize the discussion here? Just
because you have to execute several SQL commands instead of one
doesn't turn a "well-documented and clear approach" into something
that involves "fiddling with individual pieces". Cutting and pasting
a sequence of 3 or 4 SQL commands into a psql window is not a lot
harder than copying and pasting a single one, and does not turn a good
approach into a shambles.

>> Second, I think it's really unlikely that you're going to maintain
>> perfect alignment between your predefined roles and the permissions
>> that third-party tools need over the course of multiple releases. I
>> think the chances of that working out are just about zero. Sure, you
>> can make the initial set of permissions granted to pg_backup match
>> exactly what pgbackrest needs, but it's a good bet that one of the six
>> or eight widely-used backup tools uses something that's not included
>> in that set.
>
> There may be something I've missed with the proposed pg_backup role, but
> I don't believe you're correct that there isn't a set of privileges
> which all of those backup tools need and which could be provided through
> the pg_backup role.

Well, there's certainly some set of privileges that will make them all
work. But it might include more than some of them want. If you done
any analysis of this, I have not seen it posted to this thread.

>> And even if not, it doesn't require very much
>> imagination to suppose that some tool, maybe pgbackrest or maybe
>> something else, that comes along over the next few releases will
>> require some extra permission. When that happens, will we include add
>> that permission to the pg_backup role, or not?
>
> As I pointed out previously, we've already been doing this with the
> replication role attribute and I don't recall any complaining about it.

1. This doesn't really seem like the same thing. You're introducing
something quite new here: these are not role attributes that apply
only to the role itself, but inheritable role attributes.

2. I believe that the discussion about what the replication role
should and should not allow involved considerably more people than
have discussed any of the specific roles you propose to add here.

3. It was clear from the outset that the replication role's basic
purpose was to be sufficient privilege for a streaming standby and no
more. The remit of these roles is a lot less clear, at least to me.

> I wasn't suggesting that we give everyone the same privileges to some
> default 'pgAdmin' role but rather that providing an abstraction of the
> set of privileges possible against the catalog objects, into sets that
> make sense together, is a useful simplification for users and that it'd
> be a better approach than asking users to figure out what sets make
> sense on their own.

I have no objection to that *in theory*. What's not clear to me is
that the way that you have broken it up actually meets the bona fide
needs of actual tools in a useful way.

> Adding pg_dump dump and restore support for catalog ACLs implies that
> we're supporting ACLs on all catalog objects- including tables.

Not to me it doesn't. I think we could support it just for functions,
and have it continue to be as weird as it is currently for other types
of objects until somebody gets around to straightening that out. If
we want to support it for more object types, great, but I don't think
that's a hard requirement.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company