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

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

Excellent. We've been looking at the same considerations.

> The "capabilities" would be:
> * MAINTENANCE --- Ability to run
> VACUUM [ANALYZE | FREEZE] (but not VACUUM FULL),
> ANALYZE (including SET LOCAL statistics_target TO 10000),

There's likely to be discussion about these from the perspective that
you really shouldn't need to run them all that much. Why isn't
autovacuum able to handle this?

> REINDEX CONCURRENTLY (but not the blocking, regular, one)
> REFRESH MATERIALIZED VIEW CONCURRENTLY (but not the blocking one)

These are interesting, but would these make sense at the role level?
Both of these commands explicitly take specific relations to operate
against, after all.

> COPY ???

The question around this one goes back to the "CREATE DIRECTORY"
discussion that happened this fall. I'm still hopeful that we can do
*something* in this area, but I'm not sure what that's going to end up
looking like. The problem with COPY is that it's either trivial to use
it to become a superuser, or insanely difficult to secure sufficiently.

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

This makes sense for the reindex/refresh cases, though "no harm" might
be over-stating it.

> * 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"
> Rationale: for use by connection poolers (esp. pgBouncer), where
> the role used for connection would only have the LOGIN and
> IMPERSONATE privileges. The remaining operations would be authorized
> against the supplanted role (i.e. ability to create tables/indexes
> or views, perform DML and/or DDL, etc)
> AFAIK, a superuser role is needed for this purpose currently.

No.. You can have 'no-inherit' roles which you can use for exactly this
purpose. The initial login role can have no rights on the database,
except to SET ROLE to other roles which have been granted to it.

You should never have your pgBouncer or other pooling connection logging
in as a superuser.

Thanks,

Stephen