Pivotal Knowledge Base


How to Configure Parameters Specifically for a User or a Database


Pivotal Greenplum Database (GPDB)


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 a different configuration for a specific parameter.


Let's take one example to understand this better.

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 ;
gpadmin=# SHOW search_path ;
(1 row)

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

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

For User level changes:

gpadmin=# ALTER USER user1 SET SEARCH_PATH  = schema1,pg_catalog;
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 ;
 schema1, pg_catalog
(1 row)

Things to note:

  • Settings at the database level override settings at the system level (postgresql.conf).
  • Settings at the role level override settings at the database level.
  • Settings at the session level override those at the role level.


Powered by Zendesk