Re: Proposal: two new role attributes and/or capabilities?

On Tue, Dec 23, 2014 at 11:20 AM, José Luis Tallón
<jltallon(at)adv-solutions(dot)net> wrote:
> I've found myself needing two role capabilities? as of lately, when
> thinking about restricting some roles to the barely minimum allowed
> permissions needed to perform their duties ... as opposed to having a
> superuser role devoted to these task.
>
> The "capabilities" would be:
> * MAINTENANCE --- Ability to run
> VACUUM [ANALYZE | FREEZE] (but not VACUUM FULL),
> ANALYZE (including SET LOCAL statistics_target TO 10000),
> REINDEX CONCURRENTLY (but not the blocking, regular, one)
> REFRESH MATERIALIZED VIEW CONCURRENTLY (but not the blocking one)
> COPY ???
>
> Rationale: delegate the routine maintenance tasks to a low privilege
> role, which can't do harm (apart from some performance degradation) ---
> hence the "no exclusive locking operations" requirement.

I think the problem here is that, while almost everybody would
probably agree that something like this is useful, three hackers in a
room will have four or five different opinions on how to set the
boundaries around it. I for example wouldn't feel too bad about
grouping VACUUM and ANALYZE under the same umbrella, but certainly
would be surprised to see all of the other stuff included. But you've
got a different idea that is clearly valid, and somebody else might
want yet another thing. We can avoid those problems by making the
capabilities finer-grained, but of course then you end up with lots
and lots of them, which is annoying too.

> * IMPERSONATE --- Ability to do "SET AUTHORIZATION TO some_role;" and "RESET
> AUTHORIZATION"
> This might be further refined to provide a way to say "This role is
> authorized to impersonate role1 but no other"

I can't see this providing any meaningful security improvement.

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