Re: Should SET ROLE inherit config params?

On Wed, 2009-03-11 at 14:27 -0700, Josh Berkus wrote:

> I was just noticing that doing SET ROLE changes the current session's
> priviledges, but not any runtime configuration parameters (like work_mem
> or statement_timeout) associated with the new role.
>
> This is as documented (although I want to add a line to SET ROLE docs)
> but is it the behavior we want? I for one would like SET ROLE to change
> runtime configs.

Thinking some more about the requirements for this and various
objections.

I'm guessing that there's a small cluster of parameters you want to
alter using this. It seems easier to think about those parameters and to
look at ways of managing those. Perhaps what we need is not parameters
on roles, but a related concept: profiles.

Profiles define the limits and priorities given to certain categories of
work. So one profile might be work_mem = 128M and constraint_exclusion =
on, others could differ. If we invent a new concept, we get to define
the semantics from scratch. Maybe RESET doesn't work with profiles,
maybe you can't change user parameters set by a profile, maybe they
allow you to define maximum values. Maybe. Maybe. Nice clear
distinction: roles manage privileges, profiles manage
resources/optimisation.

The main reason for abstraction is that we can avoid hardcoding resource
management data into applications, so that when we upgrade we don't need
to retune or re-arrange everything.

8.5 obviously. But if some time is given to a coherent design that
focuses on what we actually want rather than on a specific solution, we
may find there is a neat way to do this without breaking anything.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support