doc/src/sgml/ref/create_role.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATEROLE">
   7  <refmeta>
   8   <refentrytitle id="sql-createrole-title">CREATE ROLE</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11
  12  <refnamediv>
  13   <refname>CREATE ROLE</refname>
  14   <refpurpose>define a new database role</refpurpose>
  15  </refnamediv>
  16
  17  <indexterm zone="sql-createrole">
  18   <primary>CREATE ROLE</primary>
  19  </indexterm>
  20
  21  <refsynopsisdiv>
  22 <synopsis>
  23 CREATE ROLE <replaceable class="PARAMETER">name</replaceable> [ [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ] ]
  24
  25 where <replaceable class="PARAMETER">option</replaceable> can be:
  26
  27       SUPERUSER | NOSUPERUSER
  28     | CREATEDB | NOCREATEDB
  29     | CREATEROLE | NOCREATEROLE
  30     | CREATEUSER | NOCREATEUSER
  31     | INHERIT | NOINHERIT
  32     | LOGIN | NOLOGIN
  33     | CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
  34     | [ ENCRYPTED | UNENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
  35     | VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'
  36     | IN ROLE <replaceable class="PARAMETER">rolename</replaceable> [, ...]
  37     | IN GROUP <replaceable class="PARAMETER">rolename</replaceable> [, ...]
  38     | ROLE <replaceable class="PARAMETER">rolename</replaceable> [, ...]
  39     | ADMIN <replaceable class="PARAMETER">rolename</replaceable> [, ...]
  40     | USER <replaceable class="PARAMETER">rolename</replaceable> [, ...]
  41     | SYSID <replaceable class="PARAMETER">uid</replaceable>
  42 </synopsis>
  43  </refsynopsisdiv>
  44
  45  <refsect1>
  46   <title>Description</title>
  47
  48   <para>
  49    <command>CREATE ROLE</command> adds a new role to a
  50    <productname>PostgreSQL</productname> database cluster.  A role is
  51    an entity that can own database objects and have database privileges;
  52    a role can be considered a <quote>user</>, a <quote>group</>, or both
  53    depending on how it is used.  Refer to
  54    <xref linkend="user-manag"> and <xref
  55    linkend="client-authentication"> for information about managing
  56    users and authentication.  You must have <literal>CREATEROLE</>
  57    privilege or be a database superuser to use this command.
  58   </para>
  59
  60   <para>
  61    Note that roles are defined at the database cluster
  62    level, and so are valid in all databases in the cluster.
  63   </para>
  64  </refsect1>
  65
  66  <refsect1>
  67   <title>Parameters</title>
  68
  69     <variablelist>
  70      <varlistentry>
  71       <term><replaceable class="parameter">name</replaceable></term>
  72       <listitem>
  73        <para>
  74         The name of the new role.
  75        </para>
  76       </listitem>
  77      </varlistentry>
  78
  79      <varlistentry>
  80       <term><literal>SUPERUSER</literal></term>
  81       <term><literal>NOSUPERUSER</literal></term>
  82       <listitem>
  83        <para>
  84         These clauses determine whether the new role is a <quote>superuser</>,
  85         who can override all access restrictions within the database.
  86         Superuser status is dangerous and should be used only when really
  87         needed.  You must yourself be a superuser to create a new superuser.
  88         If not specified,
  89         <literal>NOSUPERUSER</literal> is the default.
  90        </para>
  91       </listitem>
  92      </varlistentry>
  93
  94      <varlistentry>
  95       <term><literal>CREATEDB</></term>
  96       <term><literal>NOCREATEDB</></term>
  97       <listitem>
  98        <para>
  99         These clauses define a role's ability to create databases.  If
 100         <literal>CREATEDB</literal> is specified, the role being
 101         defined will be allowed to create new databases. Specifying
 102         <literal>NOCREATEDB</literal> will deny a role the ability to
 103         create databases. If not specified,
 104         <literal>NOCREATEDB</literal> is the default.
 105        </para>
 106       </listitem>
 107      </varlistentry>
 108
 109      <varlistentry>
 110       <term><literal>CREATEROLE</literal></term>
 111       <term><literal>NOCREATEROLE</literal></term>
 112       <listitem>
 113        <para>
 114         These clauses determine whether a role will be permitted to
 115         create new roles (that is, execute <command>CREATE ROLE</command>).
 116         A role with <literal>CREATEROLE</literal> privilege can also alter
 117         and drop other roles.
 118         If not specified,
 119         <literal>NOCREATEROLE</literal> is the default.
 120        </para>
 121       </listitem>
 122      </varlistentry>
 123
 124      <varlistentry>
 125       <term><literal>CREATEUSER</literal></term>
 126       <term><literal>NOCREATEUSER</literal></term>
 127       <listitem>
 128        <para>
 129         These clauses are an obsolete, but still accepted, spelling of
 130         <literal>SUPERUSER</literal> and <literal>NOSUPERUSER</literal>.
 131         Note that they are <emphasis>not</> equivalent to
 132         <literal>CREATEROLE</literal> as one might naively expect!
 133        </para>
 134       </listitem>
 135      </varlistentry>
 136
 137      <varlistentry>
 138       <term><literal>INHERIT</literal></term>
 139       <term><literal>NOINHERIT</literal></term>
 140       <listitem>
 141        <para>
 142         These clauses determine whether a role <quote>inherits</> the
 143         privileges of roles it is a member of.
 144         A role with the <literal>INHERIT</literal> attribute can automatically
 145         use whatever database privileges have been granted to all roles
 146         it is directly or indirectly a member of.
 147         Without <literal>INHERIT</literal>, membership in another role
 148         only grants the ability to <command>SET ROLE</> to that other role;
 149         the privileges of the other role are only available after having
 150         done so.
 151         If not specified,
 152         <literal>INHERIT</literal> is the default.
 153        </para>
 154       </listitem>
 155      </varlistentry>
 156
 157      <varlistentry>
 158       <term><literal>LOGIN</literal></term>
 159       <term><literal>NOLOGIN</literal></term>
 160       <listitem>
 161        <para>
 162         These clauses determine whether a role is allowed to log in;
 163         that is, whether the role can be given as the initial session
 164         authorization name during client connection.  A role having
 165         the <literal>LOGIN</literal> attribute can be thought of as a user.
 166         Roles without this attribute are useful for managing database
 167         privileges, but are not users in the usual sense of the word.
 168         If not specified,
 169         <literal>NOLOGIN</literal> is the default, except when
 170         <command>CREATE ROLE</> is invoked through its alternative spelling
 171         <command>CREATE USER</>.
 172        </para>
 173       </listitem>
 174      </varlistentry>
 175
 176      <varlistentry>
 177       <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
 178       <listitem>
 179        <para>
 180         If role can log in, this specifies how many concurrent connections
 181         the role can make.  -1 (the default) means no limit.
 182        </para>
 183       </listitem>
 184      </varlistentry>
 185
 186      <varlistentry>
 187       <term><literal>PASSWORD</> <replaceable class="parameter">password</replaceable></term>
 188       <listitem>
 189        <para>
 190         Sets the role's password.  (A password is only of use for
 191         roles having the <literal>LOGIN</literal> attribute, but you
 192         can nonetheless define one for roles without it.)  If you do
 193         not plan to use password authentication you can omit this
 194         option.  If no password is specified, the password will be set
 195         to null and password authentication will always fail for that
 196         user.  A null password can optionally be written explicitly as
 197         <literal>PASSWORD NULL</literal>.
 198        </para>
 199       </listitem>
 200      </varlistentry>
 201
 202      <varlistentry>
 203       <term><literal>ENCRYPTED</></term>
 204       <term><literal>UNENCRYPTED</></term>
 205       <listitem>
 206        <para>
 207         These key words control whether the password is stored
 208         encrypted in the system catalogs.  (If neither is specified,
 209         the default behavior is determined by the configuration
 210         parameter <xref linkend="guc-password-encryption">.)  If the
 211         presented password string is already in MD5-encrypted format,
 212         then it is stored encrypted as-is, regardless of whether
 213         <literal>ENCRYPTED</> or <literal>UNENCRYPTED</> is specified
 214         (since the system cannot decrypt the specified encrypted
 215         password string).  This allows reloading of encrypted
 216         passwords during dump/restore.
 217        </para>
 218
 219        <para>
 220         Note that older clients might lack support for the MD5
 221         authentication mechanism that is needed to work with passwords
 222         that are stored encrypted.
 223        </para>
 224       </listitem>
 225      </varlistentry>
 226
 227      <varlistentry>
 228       <term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term>
 229       <listitem>
 230        <para>
 231         The <literal>VALID UNTIL</literal> clause sets a date and
 232         time after which the role's password is no longer valid.  If
 233         this clause is omitted the password will be valid for all time.
 234        </para>
 235       </listitem>
 236      </varlistentry>
 237
 238      <varlistentry>
 239       <term><literal>IN ROLE</> <replaceable class="parameter">rolename</replaceable></term>
 240       <listitem>
 241        <para>
 242         The <literal>IN ROLE</literal> clause lists one or more existing
 243         roles to which the new role will be immediately added as a new
 244         member.  (Note that there is no option to add the new role as an
 245         administrator; use a separate <command>GRANT</> command to do that.)
 246        </para>
 247       </listitem>
 248      </varlistentry>
 249
 250      <varlistentry>
 251       <term><literal>IN GROUP</> <replaceable class="parameter">rolename</replaceable></term>
 252       <listitem>
 253        <para>
 254         <literal>IN GROUP</literal> is an obsolete spelling of
 255         <literal>IN ROLE</>.
 256        </para>
 257       </listitem>
 258      </varlistentry>
 259
 260      <varlistentry>
 261       <term><literal>ROLE</> <replaceable class="parameter">rolename</replaceable></term>
 262       <listitem>
 263        <para>
 264         The <literal>ROLE</literal> clause lists one or more existing
 265         roles which are automatically added as members of the new role.
 266         (This in effect makes the new role a <quote>group</>.)
 267        </para>
 268       </listitem>
 269      </varlistentry>
 270
 271      <varlistentry>
 272       <term><literal>ADMIN</> <replaceable class="parameter">rolename</replaceable></term>
 273       <listitem>
 274        <para>
 275         The <literal>ADMIN</literal> clause is like <literal>ROLE</literal>,
 276         but the named roles are added to the new role <literal>WITH ADMIN
 277         OPTION</>, giving them the right to grant membership in this role
 278         to others.
 279        </para>
 280       </listitem>
 281      </varlistentry>
 282
 283      <varlistentry>
 284       <term><literal>USER</> <replaceable class="parameter">rolename</replaceable></term>
 285       <listitem>
 286        <para>
 287         The <literal>USER</literal> clause is an obsolete spelling of
 288         the <literal>ROLE</> clause.
 289        </para>
 290       </listitem>
 291      </varlistentry>
 292
 293      <varlistentry>
 294       <term><literal>SYSID</> <replaceable class="parameter">uid</replaceable></term>
 295       <listitem>
 296        <para>
 297         The <literal>SYSID</literal> clause is ignored, but is accepted
 298         for backwards compatibility.
 299        </para>
 300       </listitem>
 301      </varlistentry>
 302     </variablelist>
 303  </refsect1>
 304
 305  <refsect1>
 306   <title>Notes</title>
 307
 308   <para>
 309    Use <xref linkend="SQL-ALTERROLE" endterm="SQL-ALTERROLE-title"> to
 310    change the attributes of a role, and <xref linkend="SQL-DROPROLE"
 311    endterm="SQL-DROPROLE-title"> to remove a role.  All the attributes
 312    specified by <command>CREATE ROLE</> can be modified by later
 313    <command>ALTER ROLE</> commands.
 314   </para>
 315
 316   <para>
 317    The preferred way to add and remove members of roles that are being
 318    used as groups is to use
 319    <xref linkend="SQL-GRANT" endterm="SQL-GRANT-title"> and
 320    <xref linkend="SQL-REVOKE" endterm="SQL-REVOKE-title">.
 321   </para>
 322
 323   <para>
 324    The <literal>VALID UNTIL</> clause defines an expiration time for a
 325    password only, not for the role <foreignphrase>per se</>.  In
 326    particular, the expiration time is not enforced when logging in using
 327    a non-password-based authentication method.
 328   </para>
 329
 330   <para>
 331    The <literal>INHERIT</> attribute governs inheritance of grantable
 332    privileges (that is, access privileges for database objects and role
 333    memberships).  It does not apply to the special role attributes set by
 334    <command>CREATE ROLE</> and <command>ALTER ROLE</>.  For example, being
 335    a member of a role with <literal>CREATEDB</> privilege does not immediately
 336    grant the ability to create databases, even if <literal>INHERIT</> is set;
 337    it would be necessary to become that role via
 338    <xref linkend="SQL-SET-ROLE" endterm="SQL-SET-ROLE-title"> before
 339    creating a database.
 340   </para>
 341
 342   <para>
 343    The <literal>INHERIT</> attribute is the default for reasons of backwards
 344    compatibility: in prior releases of <productname>PostgreSQL</productname>,
 345    users always had access to all privileges of groups they were members of.
 346    However, <literal>NOINHERIT</> provides a closer match to the semantics
 347    specified in the SQL standard.
 348   </para>
 349
 350   <para>
 351    Be careful with the <literal>CREATEROLE</> privilege. There is no concept of
 352    inheritance for the privileges of a <literal>CREATEROLE</>-role. That
 353    means that even if a role does not have a certain privilege but is allowed
 354    to create other roles, it can easily create another role with different
 355    privileges than its own (except for creating roles with superuser
 356    privileges). For example, if the role <quote>user</> has the
 357    <literal>CREATEROLE</> privilege but not the <literal>CREATEDB</> privilege,
 358    nonetheless it can create a new role with the <literal>CREATEDB</>
 359    privilege. Therefore, regard roles that have the <literal>CREATEROLE</>
 360    privilege as almost-superuser-roles.
 361   </para>
 362
 363   <para>
 364    <productname>PostgreSQL</productname> includes a program <xref
 365    linkend="APP-CREATEUSER" endterm="APP-CREATEUSER-title"> that has
 366    the same functionality as <command>CREATE ROLE</command> (in fact,
 367    it calls this command) but can be run from the command shell.
 368   </para>
 369
 370   <para>
 371    The <literal>CONNECTION LIMIT</> option is only enforced approximately;
 372    if two new sessions start at about the same time when just one
 373    connection <quote>slot</> remains for the role, it is possible that
 374    both will fail.  Also, the limit is never enforced for superusers.
 375   </para>
 376
 377   <para>
 378    Caution must be exercised when specifying an unencrypted password
 379    with this command.  The password will be transmitted to the server
 380    in cleartext, and it might also be logged in the client's command
 381    history or the server log.  The command <xref
 382    linkend="APP-CREATEUSER" endterm="APP-CREATEUSER-title">, however, transmits
 383    the password encrypted.  Also, <xref linkend="app-psql"
 384    endterm="app-psql-title"> contains a command
 385    <command>\password</command> that can be used to safely change the
 386    password later.
 387   </para>
 388  </refsect1>
 389
 390  <refsect1>
 391   <title>Examples</title>
 392
 393   <para>
 394    Create a role that can log in, but don't give it a password:
 395 <programlisting>
 396 CREATE ROLE jonathan LOGIN;
 397 </programlisting>
 398   </para>
 399
 400   <para>
 401    Create a role with a password:
 402 <programlisting>
 403 CREATE USER davide WITH PASSWORD 'jw8s0F4';
 404 </programlisting>
 405    (<command>CREATE USER</> is the same as <command>CREATE ROLE</> except
 406    that it implies <literal>LOGIN</>.)
 407   </para>
 408
 409   <para>
 410    Create a role with a password that is valid until the end of 2004.
 411    After one second has ticked in 2005, the password is no longer
 412    valid.
 413
 414 <programlisting>
 415 CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';
 416 </programlisting>
 417   </para>
 418
 419   <para>
 420    Create a role that can create databases and manage roles:
 421 <programlisting>
 422 CREATE ROLE admin WITH CREATEDB CREATEROLE;
 423 </programlisting>
 424   </para>
 425  </refsect1>
 426
 427  <refsect1>
 428   <title>Compatibility</title>
 429
 430   <para>
 431    The <command>CREATE ROLE</command> statement is in the SQL standard,
 432    but the standard only requires the syntax
 433 <synopsis>
 434 CREATE ROLE <replaceable class="PARAMETER">name</> [ WITH ADMIN <replaceable class="PARAMETER">rolename</> ]
 435 </synopsis>
 436    Multiple initial administrators, and all the other options of
 437    <command>CREATE ROLE</command>, are
 438    <productname>PostgreSQL</productname> extensions.
 439   </para>
 440
 441   <para>
 442    The SQL standard defines the concepts of users and roles, but it
 443    regards them as distinct concepts and leaves all commands defining
 444    users to be specified by each database implementation.  In
 445    <productname>PostgreSQL</productname> we have chosen to unify
 446    users and roles into a single kind of entity.  Roles therefore
 447    have many more optional attributes than they do in the standard.
 448   </para>
 449
 450   <para>
 451    The behavior specified by the SQL standard is most closely approximated
 452    by giving users the <literal>NOINHERIT</> attribute, while roles are
 453    given the <literal>INHERIT</> attribute.
 454   </para>
 455  </refsect1>
 456
 457  <refsect1>
 458   <title>See Also</title>
 459
 460   <simplelist type="inline">
 461    <member><xref linkend="sql-set-role" endterm="sql-set-role-title"></member>
 462    <member><xref linkend="sql-alterrole" endterm="sql-alterrole-title"></member>
 463    <member><xref linkend="sql-droprole" endterm="sql-droprole-title"></member>
 464    <member><xref linkend="sql-grant" endterm="sql-grant-title"></member>
 465    <member><xref linkend="sql-revoke" endterm="sql-revoke-title"></member>
 466    <member><xref linkend="app-createuser"></member>
 467   </simplelist>
 468  </refsect1>
 469 </refentry>