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