Setting the Default Role

The DBA or the owner of the database can issue the GRANT DEFAULT ROLE statement to assign an existing role as the default role to a specified list of users or to PUBLIC. Unlike a non-default role, the default role does not require the SET ROLE statement to enable it. When a user is assigned to the default role, an implicit connection to the database is granted to the user.

Each of the three statements in next example respectively performs one of the following operations on a role:

  • Declares a role called Engineer
  • Assigns Select privileges on the locomotives table to the Engineer role
  • Defines Engineer as the default role for the user jgould.
EXEC SQL CREATE ROLE 'Engineer';
EXEC SQL GRANT SELECT ON locomotives TO 'Engineer';
EXEC SQL GRANT DEFAULT ROLE 'Engineer' TO jgould;
If jgould subsequently uses the SET ROLE statement to enable some other role, then by executing the following statement, jgould replaces that role with Engineer as the current role: 
SET ROLE DEFAULT;

If you have no default role, SET ROLE DEFAULT makes NONE your current role, leaving only the privileges that have been granted explicitly to your username or to PUBLIC. After GRANT DEFAULT ROLE changes your default role to a new default role, executing SET ROLE DEFAULT restores your most recently granted default role, even if this role was not your default role when you connected to the database.

If one default role is granted to PUBLIC, but a different role is granted as the default role to an individual user, the individually-granted default role takes precedence if that user issues SET ROLE DEFAULT or connects to the database.