Product Documentation

Appearance

CREATE ROLE ​

Create a database role (or user) and define attributes for the role.

Users and roles are virtually the same thing. You can use a CREATE ROLE statement to create a new role that takes on the privileges granted to an existing role. In that sense, users may become a member of a role and accrue privileges from that role.

CREATE USER syntax is accepted; however, CREATE USER and CREATE ROLE are synonymous, except for one important difference in behavior. When you use CREATE USER, the LOGIN attribute is assumed by default. However, NOLOGIN is the default when you use CREATE ROLE.

Only users and roles that have CREATE ROLE privilege can create other roles and users. This privilege can be inherited or granted:

  • Inherited when the CREATEROLE option is used in the CREATE ROLE statement
  • Granted explicitly with a GRANT CREATE ROLE ON SYSTEM statement

Note the difference between attributes assigned to roles when they are created and grantable privileges that may be subsequently given to roles or accrued via role membership. With the exception of CREATEDB/NOCREATEDB and CREATEROLE/NOCREATEROLE, the options defined in the CREATE ROLE statement are attributes, not privileges.

CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

     SUPERUSER | NOSUPERUSER
   | CREATEDB | NOCREATEDB
   | CREATEROLE | NOCREATEROLE
   | INHERIT | NOINHERIT
   | LOGIN | NOLOGIN
   | CONNECTION LIMIT connlimit
   | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
   | VALID UNTIL 'timestamp'
   | IN ROLE role_name [, ...]
   | ROLE role_name [, ...]
   | ADMIN role_name [, ...]
   | USER role_name [, ...]
name

Specify the role or user name, using a quoted or unquoted identifier. See SQL Identifiers.

SUPERUSER | NOSUPERUSER

Whether the role can override all access restrictions for the database. Only a superuser can create a new superuser. NOSUPERUSER is the default. Assign this attribute only when it is really needed. You cannot grant SUPERUSER privilege.

CREATEDB | NOCREATEDB

Whether the role can create databases. NOCREATEDB is the default. Ability to create databases is inheritable when users belong to a role that has that privilege.

CREATEROLE | NOCREATEROLE

Whether the role can create new roles (execute CREATE ROLE). A role with this attribute can also alter and drop roles. NOCREATEROLE is the default. Ability to create roles is inheritable when users belong to a role that has that privilege.

INHERIT | NOINHERIT

Whether a role inherits the privileges of roles that it is a member of. NOINHERIT means that membership in another role only grants the ability to SET ROLE to that other role. The privileges of the other role become available only after doing this.

INHERIT is the default behavior. INHERIT refers to inheritance of grantable privileges (system-level privileges, access to database objects, and memberships in roles, CREATEDB, CREATEROLE). A role with the INHERIT attribute can automatically use whatever privileges have been granted to all roles it is directly or indirectly a member of.

LOGIN

Whether a role is allowed to log in (in other words, used as the initial session authorization name for a client connection). Think of a role that has LOGIN attribute as a user. Roles that cannot log in are useful for managing database privileges. NOLOGIN is the default, except when the alternative CREATE USER syntax is used. CREATE USER creates a user (role) with default LOGIN capability.

CONNECTION LIMIT limit

For a role with the LOGIN attribute, the concurrent connection limit for the role. The default is -1 (no limit).

ENCRYPTED | UNENCRYPTED PASSWORD 'password'

For a role with the LOGIN attribute, define a password for password authentication. If no password is specified, the password is set to null and authentication always fails for this user. PASSWORD NULL also sets the password to null.

You can store the password ENCRYPTED in the system catalogs. The default encryption behavior depends on the setting of the password_encryption configuration parameter.) If the password string is already MD5-encrypted, it is stored encrypted as is, regardless of the ENCRYPTED or UNENCRYPTED option.

CAUTION:

If you create and update unencrypted passwords with CREATE ROLE and ALTER ROLE commands, passwords are transmitted in clear text and may appear in log messages. A more secure alternative is the ybsql \password command.

VALID UNTIL 'timestamp'

Expiration date and time for the password. If you do not set a date and time, the password remains valid for all time.

IN ROLE role_name

One or more existing roles that this role will become a member of. You cannot add the new role as an administrator; use the GRANT command.

ROLE role_name

One or more existing roles that will become members of this role. (The new role effectively becomes a "group.") USER role_name is alternative syntax for the ROLE clause.

ADMIN role_name

Like the ROLE clause, except that the named roles become members of the new role WITH ADMIN OPTION, which means they can grant membership in this role to others.

Examples ​

Create a role with password authentication and the ability to create other roles and create databases.

premdb=# create role ybuser1 createdb createrole login 
encrypted password '!@#$%900' valid until '2016-12-31 23:59:59';
CREATE ROLE

Create a set of LOGIN users with the INHERIT option.

premdb=# create role alex login inherit;
CREATE ROLE
premdb=# create role vicky login inherit;
CREATE ROLE
premdb=# create role henry login inherit;
CREATE ROLE
premdb=# create role niklas login inherit;
CREATE ROLE
premdb=# \du
                                   List of roles
      Role name        |            Attributes             |       Member of        
------------------------+-----------------------------------+------------------------
 alex                   |                                   | {}
 henry                  |                                   | {}
 niklas                 |                                   | {}
 vicky                  |                                   | {}
...

Create a role (or group) to which these four users belong:

premdb=# create role allqa nologin createdb role alex, vicky, henry, niklas;
CREATE ROLE
premdb=# \du
                                   List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 alex        |                                                            | {allqa}
 allqa       | Create DB, Cannot login                                    | {}
 henry       |                                                            | {allqa}
 niklas      |                                                            | {allqa}
 vicky       |                                                            | {allqa}
...

Note that the CREATEDB attribute is inherited, so the allqa members can run as themselves and create databases. (They do not need to use SET ROLE to run as allqa.)

premdb=# premdb=# \c premdb alex
You are now connected to database "premdb" as user "alex".
premdb=> create database alexdb;
CREATE DATABASE

Parent topic:SQL Commands