Обсуждение: roles inheriting configuration values

On 02/07/2014 11:08 AM, Joe Van Dyk wrote:
> I'd like to have join_collapse_limit=20 for all users that belong to a
> certain group. Is there a way to do that without having to alter all the
> roles that are in that group?

 From what I see in the docs no:

http://www.postgresql.org/docs/9.3/interactive/sql-alterrole.html

"Whenever the role subsequently starts a new session, the specified
value becomes the session default, overriding whatever setting is
present in postgresql.conf or has been received from the postgres
command line. This only happens at login time; executing SET ROLE or SET
SESSION AUTHORIZATION does not cause new configuration values to be set. "

Looks like the settings only apply to the role that logs in.

>
> $ psql monkey
> psql (9.3.1)
> Type "help" for help.
>
> monkey=# create user f1 login;
> CREATE ROLE
> monkey=# create user f2 in role f1 login;
> CREATE ROLE
> monkey=# alter role f1 set join_collapse_limit=20;
> ALTER ROLE
>
>
> $ psql --user f1 monkey
> psql (9.3.1)
> Type "help" for help.
>
> monkey=> show join_collapse_limit ;
>   join_collapse_limit
> ---------------------
>   20
> (1 row)
>
>
> $ psql --user f2 monkey
> psql (9.3.1)
> Type "help" for help.
>
> monkey=> show join_collapse_limit ;
>   join_collapse_limit
> ---------------------
>   8
> (1 row)


--
Adrian Klaver
adrian.klaver@gmail.com

Adrian Klaver-3 wrote
> On 02/07/2014 11:08 AM, Joe Van Dyk wrote:
>> I'd like to have join_collapse_limit=20 for all users that belong to a
>> certain group. Is there a way to do that without having to alter all the
>> roles that are in that group?
>
>  From what I see in the docs no:
>
> http://www.postgresql.org/docs/9.3/interactive/sql-alterrole.html
>
> "Whenever the role subsequently starts a new session, the specified
> value becomes the session default, overriding whatever setting is
> present in postgresql.conf or has been received from the postgres
> command line. This only happens at login time; executing SET ROLE or SET
> SESSION AUTHORIZATION does not cause new configuration values to be set. "
>
> Looks like the settings only apply to the role that logs in.

This does not, by itself, preclude role-inheritance of variable values.  It
would simply mean that the inheritance resolution routine would only be
resolved at logon.

Unlike GRANT/REVOKE variable value inheritance has potential for
multiple-inheritance resolution ambiguities - which likely increases
cost/benefit equation for anyone looking to implement such a feature.

There is like a scripting solution to this problem to at least minimize the
burden but I do not recall seeing anything already in place that meets this
need.

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/roles-inheriting-configuration-values-tp5791011p5791036.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.