Re: 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(dot)klaver(at)gmail(dot)com