Proposal: two new role attributes and/or capabilities?

From: José Luis Tallón <jltallon(at)adv-solutions(dot)net>
To: 'Pg Hackers' <pgsql-hackers(at)postgresql(dot)org>
Subject: Proposal: two new role attributes and/or capabilities?
Date: 2014-12-23 16:20:19
Message-ID: 54999643.9060800@adv-solutions.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hello,

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.

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

The relevant code is quite simple and looks like it could be very
useful. Any suggestions / input on this?
I can certainly prepare a patch for this (bear with me, It'll be my
first here), and I'm willing to include more features if deemed useful.

Regards,

/ J.L.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2014-12-23 16:22:09 Re: [COMMITTERS] pgsql: Use a bitmask to represent role attributes
Previous Message Stephen Frost 2014-12-23 16:16:31 Re: [COMMITTERS] pgsql: Use a bitmask to represent role attributes