Re: Proposal: two new role attributes and/or capabilities? - Mailing list pgsql-hackers

On 12/23/2014 05:29 PM, Stephen Frost wrote:
> * José Luis Tallón (jltallon@adv-solutions.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 tasks.
> 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?

For some (arguably, ill-devised) use cases of INSERT - SELECT aggregate 
- DELETE (third party, closed-source app, massive insert rate) at the 
very least, autovacuum can't possibly cope with the change rate in some 
tables, given that there are quite many other interactive queries running.

Manually performing VACUUM / VACUUM ANALYZE on the (few) affected tables 
every 12h or so fixes the performance problem for the particular queries 
without impacting the other users too much --- the tables and indexes in 
question have been moved to a separate tablespace/disk volume of their own.


In short, this addresses situations where some tables have a much higher 
update rate than the rest of the database so that performance degrades 
with time --- the application became unusable after about 6 days' worth 
of updates until the manual vacuums were setup

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

Yup. Let's imagine a cron job invoking psql in order to perform 
maintenance routine.
The particular command(s) can be generated on-the-fly by querying the 
catalog and then send them in one go to be run sequentially by the one 
backend.... as a crude form of rate limiting/quality-of-service of sorts 
("renice -p" or even "ionice -p" seems quite inadequate).

This automation becomes impossible to do if the object owners differ 
(only the owner or a superuser can perform these operations AFAICS -- 
there is no mention of it in the current documentation) unless the DBA 
makes the maintenance role a member of every other role ... which 
quickly becomes a problem.

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

Yes. That's the reason for the question marks  :-\
Some "dump to csv then load somewhere else" kind of jobs might benefit 
from this feature, but I'm not sure the convenience is worth the risk.

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

Well.... it's performance degradation vs DoS due to massive (exclusive) 
locking  :S
At least restricting it to one backend (connection_limit=1) allows quite 
some rate limit.

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

Hmm.... the current documentation states that: "The specified role_name 
must be a role that the current session user is a member of".
I can see use cases where making the login role a member of every other 
used role quickly becomes a burden, and that's the main driver for this 
feature (I'm thinking about multiple app servers running several 
applications each, minimum two roles per application)

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

At least the default pgBouncer config explicitly says (albeit for 8.2)
doc/faq.txt:server_reset_query = RESET ALL; SET SESSION AUTHORIZATION 
DEFAULT; ... so at least some people (including me) had assumed that SET 
SESSION AUTHORIZATION (with force_user) is being used, and this is 
described as superuser only.
However, a quick look at pgbouncer's sources shows we were wrong.

Thank you for the clarification, Stephen.

    / J.L.