Default Roles for Database Users

By default, no roles (other than the default PUBLIC Role) are enabled at the start of a user session.

=> SHOW ENABLED_ROLES;     
     name      | setting
---------------+---------
 enabled roles |
(1 row)

A superuser can set one or more default roles for a user, which are automatically enabled at the start of the user's session. Setting a default role is a good idea if users normally rely on the privileges granted by one or more roles to carry out the majority of their tasks. To set a default role, use the DEFAULT ROLE parameter of the ALTER USER statement as superuser:

=> \c vmart apps
You are now connected to database "apps" as user "dbadmin".
=> ALTER USER Bob DEFAULT ROLE logadmin;
ALTER USER
=> \c - Bob;
You are now connected as user "Bob"
=> SHOW ENABLED_ROLES;
     name      | setting
---------------+----------
 enabled roles | logadmin
(1 row)

Notes

• Only roles that the user already has access to can be made default.
• Unlike granting a role, setting a default role or roles overwrites any previously-set defaults.
• To clear any default roles for a user, use the keyword NONE as the role name in the DEFAULT ROLE argument.
• Default roles only take effect at the start of a user session. They do not affect the roles enabled in the user's current session.
• Avoid giving users default roles that have administrative or destructive privileges (the PSEUDOSUPERUSER role or DROP privileges, for example). By forcing users to explicitly enable these privileges, you can help prevent accidental data loss.