NAME¶

CREATE_ROLE - define a new database role

SYNOPSIS¶

CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:


      SUPERUSER | NOSUPERUSER


    | CREATEDB | NOCREATEDB


    | CREATEROLE | NOCREATEROLE


    | INHERIT | NOINHERIT


    | LOGIN | NOLOGIN


    | REPLICATION | NOREPLICATION


    | BYPASSRLS | NOBYPASSRLS


    | 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

DESCRIPTION¶

CREATE ROLE adds a new role to a PostgreSQL database cluster. A role is an entity that can own database objects and have database privileges; a role can be considered a “user”, a “group”, or both depending on how it is used. Refer to Chapter 22 and Chapter 21 for information about managing users and authentication. You must have CREATEROLE privilege or be a database superuser to use this command.

Note that roles are defined at the database cluster level, and so are valid in all databases in the cluster.

During role creation it is possible to immediately assign the newly created role to be a member of an existing role, and also assign existing roles to be members of the newly created role. The rules for which initial role membership options are enabled are described below in the IN ROLE, ROLE, and ADMIN clauses. The GRANT(7) command has fine-grained option control during membership creation, and the ability to modify these options after the new role is created.

PARAMETERS¶

name

SUPERUSER
NOSUPERUSER

CREATEDB
NOCREATEDB

CREATEROLE
NOCREATEROLE

INHERIT
NOINHERIT

LOGIN
NOLOGIN

REPLICATION
NOREPLICATION

BYPASSRLS
NOBYPASSRLS

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

NOTES¶

Use ALTER ROLE to change the attributes of a role, and DROP ROLE to remove a role. All the attributes specified by CREATE ROLE can be modified by later ALTER ROLE commands.

The preferred way to add and remove members of roles that are being used as groups is to use GRANT and REVOKE.

The VALID UNTIL clause defines an expiration time for a password only, not for the role per se. In particular, the expiration time is not enforced when logging in using a non-password-based authentication method.

The role attributes defined here are non-inheritable, i.e., being a member of a role with, e.g., CREATEDB will not allow the member to create new databases even if the membership grant has the INHERIT option. Of course, if the membership grant has the SET option the member role would be able to SET ROLE to the createdb role and then create a new database.

The membership grants created by the IN ROLE, ROLE, and ADMIN clauses have the role executing this command as the grantor.

The INHERIT attribute is the default for reasons of backwards compatibility: in prior releases of PostgreSQL, users always had access to all privileges of groups they were members of. However, NOINHERIT provides a closer match to the semantics specified in the SQL standard.

PostgreSQL includes a program createuser(1) that has the same functionality as CREATE ROLE (in fact, it calls this command) but can be run from the command shell.

The CONNECTION LIMIT option is only enforced approximately; if two new sessions start at about the same time when just one connection “slot” remains for the role, it is possible that both will fail. Also, the limit is never enforced for superusers.

Caution must be exercised when specifying an unencrypted password with this command. The password will be transmitted to the server in cleartext, and it might also be logged in the client's command history or the server log. The command createuser(1), however, transmits the password encrypted. Also, psql(1) contains a command \password that can be used to safely change the password later.

EXAMPLES¶

Create a role that can log in, but don't give it a password:

CREATE ROLE jonathan LOGIN;

Create a role with a password:

CREATE USER davide WITH PASSWORD 'jw8s0F4';

(CREATE USER is the same as CREATE ROLE except that it implies LOGIN.)

Create a role with a password that is valid until the end of 2004. After one second has ticked in 2005, the password is no longer valid.

CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';

Create a role that can create databases and manage roles:

CREATE ROLE admin WITH CREATEDB CREATEROLE;

COMPATIBILITY¶

The CREATE ROLE statement is in the SQL standard, but the standard only requires the syntax

CREATE ROLE name [ WITH ADMIN role_name ]

Multiple initial administrators, and all the other options of CREATE ROLE, are PostgreSQL extensions.

The SQL standard defines the concepts of users and roles, but it regards them as distinct concepts and leaves all commands defining users to be specified by each database implementation. In PostgreSQL we have chosen to unify users and roles into a single kind of entity. Roles therefore have many more optional attributes than they do in the standard.

The behavior specified by the SQL standard is most closely approximated creating SQL-standard users as PostgreSQL roles with the NOINHERIT option, and SQL-standard roles as PostgreSQL roles with the INHERIT option.

SEE ALSO¶

SET ROLE (SET_ROLE(7)), ALTER ROLE (ALTER_ROLE(7)), DROP ROLE (DROP_ROLE(7)), GRANT(7), REVOKE(7), createuser(1), createrole_self_grant