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.
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 grantSUPERUSER
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 toSET 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 theINHERIT
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 alternativeCREATE USER
syntax is used.CREATE USER
creates a user (role) with defaultLOGIN
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 thepassword_encryption
configuration parameter.) If the password string is already MD5-encrypted, it is stored encrypted as is, regardless of theENCRYPTED
orUNENCRYPTED
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 theybsql \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 theROLE
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
premdb=# create role ybuser1 createdb createrole login
encrypted password '!@#$%900' valid until '2016-12-31 23:59:59';
CREATE ROLE
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 | | {}
...
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}
...
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