CREATE ROLE name [ [ WITH ] option [ ... ] ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' | IN ROLE rolename [, ...] | IN GROUP rolename [, ...] | ROLE rolename [, ...] | ADMIN rolename [, ...] | USER rolename [, ...] | SYSID uid
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 the documentation and the documentation 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.
Note that older clients may lack support for the MD5 authentication mechanism that is needed to work with passwords that are stored encrypted.
Use ALTER ROLE [alter_role(7)] to change the attributes of a role, and DROP ROLE [drop_role(7)] 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 [grant(7)] and REVOKE [revoke(7)].
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 [set_role(7)] 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 [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.
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;
The CREATE ROLE statement is in the SQL standard, but the standard only requires the syntax
CREATE ROLE name [ WITH ADMIN rolename ]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.