Provided by: postgresql-client-8.4_8.4.11-1_amd64 bug


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


       name   The name of the new role.


              These clauses determine whether the new role is a ``superuser'', who  can  override
              all  access  restrictions  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 default.


              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, NOCREATEROLE 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


              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 authentication will always
              fail  for  that  user.  A  null  password  can  optionally be written explicitly as
              PASSWORD NULL.


              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
              specified  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 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.

       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

       CREATE ROLE name [ WITH ADMIN rolename ]

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

       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

       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)