[GENERAL] roles inheriting configuration values
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? $ 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)
Re: [GENERAL] 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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] roles inheriting configuration values
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
