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

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

Autovacuum can certainly run vacuum/analyze on a few tables every 12
hours, so I'm not really following where you see autovacuum being unable
to cope.  I agree that there *are* such cases, but getting more
information about those cases and exactly what solution *does* work
would really help us improve autovacuum to address those use-cases.

> 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

This really looks like a configuration issue with autovacuum..  Perhaps
you need to make it more aggressive than the default and have it run
more threads?  Have you turned the autovacuum logging up all the way?
Is autovacuum giving up due to locking?

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

If they make sense at a relation level then they should be
relation-level GRANT'd permissions, not role-level attributes.

> 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 sounds like it's something that we might want an autovacuum-like
background process to handle..  Some kind of auto-reindex-concurrently.
There are already plans to deal with updating of materialized views, as
I understand it.

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

I agree that having a maintenance role which is a member of every other
role isn't a very good solution.

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

I've run into quite a few processes which would really benefit from
this, and would even be safe to use (the processes running the COPY
commands don't have any rights on the directories except through PG),
but it's not clear if that use-case is sufficiently broad for the
feature to be worthwhile..  At least, some feel it isn't.  Can you
describe your use-case more and perhaps the needle will move on that
point?

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

That's correct.

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

I suppose, but I think you're over-simplying your use-case.  Consider
this- do you want this login role to be a member of superuser roles?
What about DBA or other privilege-user roles?

> However, a quick look at pgbouncer's sources shows we were wrong.
>
> Thank you for the clarification, Stephen.

Happy to help!
Thanks,
    Stephen