Revoking a Default Role

The DBA or the owner of the database can define a default role for one or more users or for PUBLIC with the GRANT DEFAULT ROLE statement. Unlike a non-default role, which does not take effect until the SET ROLE statement activates the role, a default role takes effect automatically when the user connects to the database. The default role can specify a set of access privileges for all the users who are granted that default role. Conversely, the REVOKE DEFAULT ROLE statement cancels the current default role as the default role for the specified user-list, as in the following program fragment:

CREATE ROLE accounting;
GRANT USAGE ON LANGUAGE SPL TO accounting;
GRANT ALL PRIVILEGES ON receivables TO accounting;
GRANT DEFAULT ROLE accounting TO mary;
. . .
REVOKE DEFAULT ROLE FROM mary;

The last statement removes from user mary any access privileges that she holds only through her default role. In this example, the default role was accounting, but because at a given point in time there can be only one default role for an individual user (or for the PUBLIC group), the name of the default role is not specified in the REVOKE DEFAULT ROLE statement. If mary issues the SET ROLE DEFAULT statement, it has no effect until she is granted some new default role.

After you execute REVOKE DEFAULT ROLE specifying one or more users or PUBLIC, any privileges that those users held only through the default role are cancelled. (But this statement does not revoke any privileges that were granted to a user individually, or privileges that were granted to a user through another role, or privileges that PUBLIC holds.)

After REVOKE DEFAULT ROLE successfully cancels the default role of user, the default role of user becomes NULL, and the default role information is removed from the system catalog. (In this context, NULL and NONE are synonyms.)

No warning is issued if REVOKE DEFAULT ROLE specifies a user who has not been granted a default role.

No options besides the user-list are valid after the FROM keyword in the REVOKE DEFAULT ROLE statement.