Pivotal Knowledge Base

Follow

HowTo - Configure parameters specifically for a user/database

Goal

There might be some instance when it is required that some user needs to have different parameters to what has been configured globally , or it could be the case that a specific database needs different configuration for a specific parameter.

Solution

Let take one example to understand this better.

For eg.s

If for one user "user1" or for one database you have to configure "schema1" so that the user(s) can have the schemaname in his search path during its each login ( avoiding any hassle to set it everytime or through .psqlrc file ) , you can use the below query to set it.

For database :

ALTER DATABASE <dbname> SET <parameter> = <value> ;

For User:

ALTER USER <username> SET <parameter> = <value>  ;

So placing the above script in practice , we have used search_path as an example, but other parameters (GUC's) can be changed as well in the same procedure.

For Database level changes:

gpadmin=# ALTER DATABASE configdb SET search_path = "$user",public,schema1,pg_catalog ;
ALTER DATABASE
gpadmin=# SHOW search_path ;
  search_path   
----------------
 "$user",public
(1 row)

gpadmin=# \c configdb
You are now connected to database "configdb" as user "gpadmin".
configdb=# SHOW search_path ;

             search_path              
--------------------------------------
 "$user", public, schema1, pg_catalog
(1 row)

For User level changes:

gpadmin=# ALTER USER user1 SET SEARCH_PATH  = schema1,pg_catalog;
ALTER ROLE
gpadmin=# \q
gpadmin:Fullrack@mdw $ psql -U user1
Timing is on.
Pager usage is off.
psql (8.2.15)
Type "help" for help.

gpadmin=> SHOW SEARCH_PATH ;
     search_path     
---------------------
 schema1, pg_catalog
(1 row)

Comments

Powered by Zendesk