Role operations
-
CREATE ROLE— Create a new role -
ALTER ROLE— Change role attributes -
DROP ROLE— Reset a role -
SHOW ROLES— Display a list of all roles -
USE ROLE— Set the active role -
GRANT— Grant attributes to roles
Create a new role
CREATE [OR REPLACE] ROLE [IF NOT EXISTS] <role_name>;Creates a new role with the specified name.
If the OR REPLACE modifier is specified, the final action is equivalent to deleting the existing role and creating a new role with the same name.
The newly created role does not inherit any attributes or privileges from the previous role.
The optional IF NOT EXISTS modifier restricts the query to only those cases in which the specified object does not already exist.
| The modifiers are mutually exclusive. Specifying them both will result in an error. |
Change role attributes
ALTER ROLE [IF EXISTS] <role_name> RENAME TO <new_role_name>;
ALTER ROLE [IF EXISTS] <role_name> WITH <attribute>;Modifies an existing role.
The action can be either renaming the role or changing its attributes.
Available attributes:
-
SUPERUSER— grants the role superuser privileges. -
CREATEROLE— allows the role to create new roles.
The optional IF EXISTS modifier restricts the query to only those cases in which the specified object exists.
Reset a role
DROP ROLE [IF EXISTS] <role_name>;Removes the role from the system.
The command will fail if the role is still referenced by any objects (for example, it is granted to users or has access rights for some objects).
The optional IF EXISTS modifier restricts the query to only those cases in which the specified object exists.
Display a list of all roles
SHOW [TERSE] ROLES
[LIKE '<pattern>']
[STARTS WITH '<pattern>']
[LIMIT <number> [FROM '<pattern>']];Outputs a list of all roles in the system. The output can be filtered and restricted using various options:
-
TERSE— displays a simplified output format. -
LIKE '<pattern>'— filters roles whose names match the specified pattern. -
STARTS WITH '<pattern>'— filters roles whose names begin with the specified pattern. -
LIMIT <number> [FROM '<pattern>']— limits the number of roles returned, optionally starting with the specified role name.
Templates <pattern> are case-sensitive string literals that may contain wildcard characters.
Set the active role
USE ROLE <role_name>;Sets the specified role as the active role for the current session.
If a role is set as active, the user receives all privileges granted to that role for the duration of the session.
To use the specified role, the user must be granted the appropriate privileges.