CREATE ROLE

Create a database role (or user) and define access privileges 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 defined for 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 difference in behavior. When you use CREATE USER, LOGIN privilege is assumed by default. However, NOLOGIN is the default when you use CREATE ROLE.

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 privilege only when it is really needed.
CREATEDB | NOCREATEDB
Whether the role can create databases. NOCREATEDB is the default.
CREATEROLE | NOCREATEROLE
Whether the role can create new roles (execute CREATE ROLE). A role with this privilege can also alter and drop roles. NOCREATEROLE is the default.
INHERIT | NOINHERIT
Whether a role inherits the database 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.

INHERIT refers only to inheritance of privileges that can be granted (access to database objects and memberships in roles). For example, a member of a role with CREATEDB privilege does not inherit that privilege but could use SET ROLE to assume the parent role and create databases owned by that role.

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 privilege 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 LOGIN privilege, the concurrent connection limit for the role. The default is -1 (no limit).
ENCRYPTED | UNENCRYPTED PASSWORD 'password'
For a role with LOGIN privilege, 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 sytax 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 INHERIT privilege.
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       |                                                            | {}
 ybadmin     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 ybd         | Superuser                                                  | {}
 yellowbrick | Superuser                                                  | {}
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}
 ybadmin     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 ybd         | Superuser                                                  | {}
 yellowbrick | Superuser                                                  | {}
Note that CREATEDB privilege cannot be inherited, so none of the allqa members can run as themselves and create databases. However, any member of allqa can use SET ROLE to run as allqa and create a database that is owned by allqa:
premdb=# \c premdb alex
You are now connected to database "premdb" as user "alex".
premdb=> set role allqa;
SET
premdb=> create database alexdb;
CREATE DATABASE
premdb=> set role alex;
SET
premdb=> create database alexdb2;
ERROR:  permission denied to create database