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