Man Pages

create_role(7) - phpMan create_role(7) - phpMan

Command: man perldoc info search(apropos)  

CREATE ROLE(7)                   SQL Commands                   CREATE ROLE(7)

       CREATE ROLE - define a new database role

       CREATE ROLE name [ [ WITH ] option [ ... ] ]

       where option can be:

           | 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 in the documentation and in the documentation for information about managing users and authenti-
       cation. 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.

       name   The name of the new role.


              These clauses determine whether the new role is a ''superuser'', who can override  all  access  restric-
              tions  within  the  database.  Superuser status is dangerous and should be used only when really needed.
              You must yourself be a superuser to create a new  superuser.   If  not  specified,  NOSUPERUSER  is  the


              These  clauses  define  a  role's  ability to create databases. If CREATEDB is specified, the role being
              defined will be allowed to create new databases. Specifying NOCREATEDB will deny a role the  ability  to
              create databases. If not specified, NOCREATEDB is the default.


              These  clauses  determine  whether a role will be permitted to create new roles (that is, execute CREATE
              ROLE).  A role with CREATEROLE privilege can also alter and drop other roles.  If not specified,  NOCRE-
              ATEROLE is the default.


              These  clauses  are  an  obsolete, but still accepted, spelling of SUPERUSER and NOSUPERUSER.  Note that
              they are not equivalent to CREATEROLE as one might naively expect!


              These clauses determine whether a role ''inherits'' the privileges of roles it is a member of.   A  role
              with  the  INHERIT attribute can automatically use whatever database privileges have been granted to all
              roles it is directly or indirectly a member of.  Without INHERIT, membership in another role only grants
              the  ability  to  SET ROLE to that other role; the privileges of the other role are only available after
              having done so.  If not specified, INHERIT is the default.


              These clauses determine whether a role is allowed to log in; that is, whether the role can be  given  as
              the  initial  session authorization name during client connection. A role having the LOGIN attribute can
              be thought of as a user.  Roles without this attribute are useful for managing database privileges,  but
              are  not  users  in  the usual sense of the word.  If not specified, NOLOGIN is the default, except when
              CREATE ROLE is invoked through its alternative spelling CREATE USER.

       CONNECTION LIMIT connlimit
              If role can log in, this specifies how many concurrent connections the role can make. -1  (the  default)
              means no limit.

       PASSWORD password
              Sets  the  role's password. (A password is only of use for roles having the LOGIN attribute, but you can
              nonetheless define one for roles without it.) If you do not plan to use password authentication you  can
              omit this option. If no password is specified, the password will be set to null and password authentica-
              tion will always fail for that user. A null password can optionally be written  explicitly  as  PASSWORD


              These  key words control whether the password is stored encrypted in the system catalogs. (If neither is
              specified, the default behavior is determined by the configuration  parameter  password_encryption.)  If
              the  presented  password  string  is already in MD5-encrypted format, then it is stored encrypted as-is,
              regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the  speci-
              fied encrypted password string). This allows reloading of encrypted passwords during dump/restore.

              Note  that  older clients might lack support for the MD5 authentication mechanism that is needed to work
              with passwords that are stored encrypted.

       VALID UNTIL 'timestamp'
              The VALID UNTIL clause sets a date and time after which the role's password is no longer valid. If  this
              clause is omitted the password will be valid for all time.

       IN ROLE rolename
              The IN ROLE clause lists one or more existing roles to which the new role will be immediately added as a
              new member. (Note that there is no option to add the new role as an administrator; use a separate  GRANT
              command to do that.)

       IN GROUP rolename
              IN GROUP is an obsolete spelling of IN ROLE.

       ROLE rolename
              The  ROLE  clause  lists  one or more existing roles which are automatically added as members of the new
              role.  (This in effect makes the new role a ''group''.)

       ADMIN rolename
              The ADMIN clause is like ROLE, but the named roles are added to the new role WITH ADMIN  OPTION,  giving
              them the right to grant membership in this role to others.

       USER rolename
              The USER clause is an obsolete spelling of the ROLE clause.

       SYSID uid
              The SYSID clause is ignored, but is accepted for backwards compatibility.

       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  CREATE-
       ROLE-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.

       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 [createuser(1)], however,  transmits  the  password  encrypted.  Also,  psql
       [psql(1)] contains a command \password that can be used to safely change the password later.

       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:


       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.

       SET ROLE [set_role(7)],  ALTER  ROLE  [alter_role(7)],  DROP  ROLE  [drop_role(7)],  GRANT  [grant(7)],  REVOKE
       [revoke(7)], createuser(1)

SQL - Language Statements         2014-02-17                    CREATE ROLE(7)