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.

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 INHERIT attribute governs inheritance of grantable privileges (that is, access privileges for database objects and role memberships). It does not apply to the special role attributes set by CREATE ROLE and ALTER ROLE. For example, being a member of a role with CREATEDB privilege does not immediately grant the ability to create databases, even if INHERIT is set; it would be necessary to become that role via SET ROLE before creating a database.

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.

Be careful with the CREATEROLE privilege. There is no concept of inheritance for the privileges of a CREATEROLE-role. That means that even if a role does not have a certain privilege but is allowed to create other roles, it can easily create another role with different privileges than its own (except for creating roles with superuser privileges). For example, if the role “user” has the CREATEROLE privilege but not the CREATEDB privilege, nonetheless it can create a new role with the CREATEDB privilege. Therefore, regard roles that have the CREATEROLE privilege as almost-superuser-roles.

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 by giving users the NOINHERIT attribute, while roles are given the INHERIT attribute.

SEE ALSO¶

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