Grant Default (non-default roles)

You can assign a default role to a user by using either the CREATE USER command or the ALTER USER command. Use the keywords DEFAULT ROLE and then the name of the role or roles that you want to assign as defaults.
For instance, to assign the roles BIDDER and BROWSER to user BID1, who already exists, you would use the command:

ALTER USER BID1 DEFALT ROLE BIDDER, BROWSER;

You can assign roles individually as default roles. If a user has been granted several roles, you can use the keyword ALL to make all the available roles into default roles. To make all the roles granted to user BID1 above into default roles, use the following syntax:

ALTER USER BID1 DEFAULT ROLE ALL;

ALTER USER BID1 DEFAULT ROLE 
ALL EXCEPT ADMINISTRATOR;

If you want to assign most of the roles available to a user as default roles, you can use the ALL keyword along with the EXCEPT keyword to exclude specific roles. To make all the roles granted to user BID1 into default roles, except for the ADMINISTRATOR role, use the following command. Click the View Code button.

Changing Roles at runtime

You can also assign roles for a user at runtime. To do this, use the SQL command SET ROLE, followed by the role name or names.
The SET ROLE command can use the ALL and ALL EXCEPT keywords. Using the SET ROLE command automatically disables all roles that are not mentioned in the command. All roles for the user would be disabled except the role of BIDDER if user BID1 issued the following SQL command:

SET ROLE BIDDER;