CREATE ROLE [IF NOT EXISTS | OR REPLACE] name1 [, name2 ...] [SETTINGS variable [= value] [MIN [=] min_value] [MAX [=] max_value] [READONLY|WRITABLE] | PROFILE 'profile_name'] [,...]
A user can be assigned multiple roles. Users can apply their assigned roles in arbitrary combinations by the SET ROLE statement. The final scope of privileges is a combined set of all the privileges of all the applied roles. If a user has privileges granted directly to it’s user account, they are also combined with the privileges granted by roles.
To revoke a role, use the REVOKE statement.
To delete role, use the DROP ROLE statement. The deleted role is being automatically revoked from all the users and roles to which it was assigned.
CREATE ROLE accountant; GRANT SELECT ON db.* TO accountant;
This sequence of queries creates the role
accountant that has the privilege of reading data from the
Assigning the role to the user
GRANT accountant TO mira;
After the role is assigned, the user can apply it and execute the allowed queries. For example:
SET ROLE accountant; SELECT * FROM db.*;