CREATE ROLE statement [YSQL]
CREATE ROLE statement to add a new role to a YugabyteDB database cluster. A role is an entity that can own database objects and have database privileges.
A role can be a user or a group, depending on how it is used. A role with atttribute
LOGIN can be considered as a "user".
You must have
CREATEROLE privilege or be a database superuser to use this command.
Note that roles are defined at the YSQL cluster level, and so are valid in all databases in the cluster.
You can use
REVOKE commands to set/remove permissions for roles.
create_role ::= CREATE ROLE role_name [ [ WITH ] role_option [ , ... ] ] role_option ::= SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED ] PASSWORD ' password ' | PASSWORD NULL | VALID UNTIL ' timestamp ' | IN ROLE role_name [ , ... ] | IN GROUP role_name [ , ... ] | ROLE role_name [ , ... ] | ADMIN role_name [ , ... ] | USER role_name [ , ... ] | SYSID uid
role_nameis the name of the new role.
NOSUPERUSERdetermine whether the new role is a “superuser” or not. Superusers can override all access restrictions and should be used with care. Only roles with SUPERUSER privilege can create other SUPERUSER roles. If not specified, NOSUPERUSER is the default.
NOCREATEDBdetermine whether the new role can create a database or not. Default is NOCREATEDB.
NOCREATEROLEdetermine whether the new role can create other roles or not. Default is NOCREATEROLE.
NOINHERITdetermine whether the new role inherits privileges of the roles that it is a member of. Without INHERIT, membership in another role only grants the ability to SET ROLE to that other role. The privileges of the other role are only available after having done so. If not specified, INHERIT is the default.
NOLOGINdetermine whether the new role is allowed to login or not. Only roles with login privilege can be used during client connection. A role with LOGIN can be thought of as a user. If not specified, NOLOGIN is the default. Note that if
CREATE USERstatement is used instead of
CREATE ROLE, then default is LOGIN.
CONNECTION LIMITspecifies how many concurrent connections the role can make. Default is -1 which means unlimited. This only applies to roles that can login.
[ENCRYPTED] PASSWORDsets the password for the new role. This only applies to roles that can login. If no password is specified, the password will be set to null and password authentication will always fail for that user. Note that password is always stored encrypted in system catalogs and the optional keyword ENCRYPTED is only present for compatibility with Postgres.
VALID UNTILsets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time.
IN ROLE role_name,
IN GROUP role_namelists one or more existing roles to which the new role will be immediately added as a new member. (Note that there is no option to add the new role as an administrator; use a separate GRANT command to do that.)
USER role_namelists one or more existing roles which are automatically added as members of the new role. (This in effect makes the new role a “group”.)
ADMIN role_nameis similar to
ROLE role_name, but the named roles are added to the new role WITH ADMIN OPTION, giving them the right to grant membership in this role to others.
SYSID uidis ignored and present for compatibility with Postgres.
- Create a role that can login.
yugabyte=# CREATE ROLE John LOGIN;
- Create a role that can login and has a password.
yugabyte=# CREATE ROLE Jane LOGIN PASSWORD 'password';
- Create a role that can manage databases and roles.
yugabyte=# CREATE ROLE SysAdmin CREATEDB CREATEROLE;