doc/src/sgml/ref/create_role.sgml

   1 <!--
   2 doc/src/sgml/ref/create_role.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-createrole">
   7  <indexterm zone="sql-createrole">
   8   <primary>CREATE ROLE</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>CREATE ROLE</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>CREATE ROLE</refname>
  19   <refpurpose>define a new database role</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE ROLE <replaceable class="parameter">name</replaceable> [ [ WITH ] <replaceable class="parameter">option</replaceable> [ ... ] ]
  25
  26 <phrase>where <replaceable class="parameter">option</replaceable> can be:</phrase>
  27
  28       SUPERUSER | NOSUPERUSER
  29     | CREATEDB | NOCREATEDB
  30     | CREATEROLE | NOCREATEROLE
  31     | INHERIT | NOINHERIT
  32     | LOGIN | NOLOGIN
  33     | REPLICATION | NOREPLICATION
  34     | BYPASSRLS | NOBYPASSRLS
  35     | CONNECTION LIMIT <replaceable class="parameter">connlimit</replaceable>
  36     | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>' | PASSWORD NULL
  37     | VALID UNTIL '<replaceable class="parameter">timestamp</replaceable>'
  38     | IN ROLE <replaceable class="parameter">role_name</replaceable> [, ...]
  39     | ROLE <replaceable class="parameter">role_name</replaceable> [, ...]
  40     | ADMIN <replaceable class="parameter">role_name</replaceable> [, ...]
  41     | SYSID <replaceable class="parameter">uid</replaceable>
  42 </synopsis>
  43  </refsynopsisdiv>
  44
  45 <!--
  46 CAUTION: remember to keep create_user.sgml and create_group.sgml
  47 in sync when changing the above synopsis!
  48 -->
  49
  50  <refsect1>
  51   <title>Description</title>
  52
  53   <para>
  54    <command>CREATE ROLE</command> adds a new role to a
  55    <productname>PostgreSQL</productname> database cluster.  A role is
  56    an entity that can own database objects and have database privileges;
  57    a role can be considered a <quote>user</quote>, a <quote>group</quote>, or both
  58    depending on how it is used.  Refer to
  59    <xref linkend="user-manag"/> and <xref
  60    linkend="client-authentication"/> for information about managing
  61    users and authentication.  You must have <literal>CREATEROLE</literal>
  62    privilege or be a database superuser to use this command.
  63   </para>
  64
  65   <para>
  66    Note that roles are defined at the database cluster
  67    level, and so are valid in all databases in the cluster.
  68   </para>
  69
  70   <para>
  71    During role creation it is possible to immediately assign the newly created
  72    role to be a member of an existing role, and also assign existing roles
  73    to be members of the newly created role.  The rules for which initial
  74    role membership options are enabled described below in the
  75    <literal>IN ROLE</literal>, <literal>ROLE</literal>, and
  76    <literal>ADMIN</literal> clauses.  The <xref linkend="sql-grant"/>
  77    command has fine-grained option control during membership creation,
  78    and the ability to modify these options after the new role is created.
  79   </para>
  80  </refsect1>
  81
  82  <refsect1>
  83   <title>Parameters</title>
  84
  85     <variablelist>
  86      <varlistentry>
  87       <term><replaceable class="parameter">name</replaceable></term>
  88       <listitem>
  89        <para>
  90         The name of the new role.
  91        </para>
  92       </listitem>
  93      </varlistentry>
  94
  95      <varlistentry>
  96       <term><literal>SUPERUSER</literal></term>
  97       <term><literal>NOSUPERUSER</literal></term>
  98       <listitem>
  99        <para>
 100         These clauses determine whether the new role is a <quote>superuser</quote>,
 101         who can override all access restrictions within the database.
 102         Superuser status is dangerous and should be used only when really
 103         needed.  You must yourself be a superuser to create a new superuser.
 104         If not specified,
 105         <literal>NOSUPERUSER</literal> is the default.
 106        </para>
 107       </listitem>
 108      </varlistentry>
 109
 110      <varlistentry>
 111       <term><literal>CREATEDB</literal></term>
 112       <term><literal>NOCREATEDB</literal></term>
 113       <listitem>
 114        <para>
 115         These clauses define a role's ability to create databases.  If
 116         <literal>CREATEDB</literal> is specified, the role being
 117         defined will be allowed to create new databases. Specifying
 118         <literal>NOCREATEDB</literal> will deny a role the ability to
 119         create databases. If not specified,
 120         <literal>NOCREATEDB</literal> is the default.
 121         Only superuser roles or roles with <literal>CREATEDB</literal>
 122         can specify <literal>CREATEDB</literal>.
 123        </para>
 124       </listitem>
 125      </varlistentry>
 126
 127      <varlistentry>
 128       <term><literal>CREATEROLE</literal></term>
 129       <term><literal>NOCREATEROLE</literal></term>
 130       <listitem>
 131        <para>
 132         These clauses determine whether a role will be permitted to
 133         create, alter, drop, comment on, and change the security label for
 134         other roles.
 135         See <xref linkend="role-creation"/> for more details about what
 136         capabilities are conferred by this privilege.
 137         If not specified, <literal>NOCREATEROLE</literal> is the default.
 138        </para>
 139       </listitem>
 140      </varlistentry>
 141
 142      <varlistentry>
 143       <term><literal>INHERIT</literal></term>
 144       <term><literal>NOINHERIT</literal></term>
 145       <listitem>
 146        <para>
 147         This affects the membership inheritance status when this
 148         role is added as a member of another role, both in this and
 149         future commands.  Specifically, it controls the inheritance
 150         status of memberships added with this command using the
 151         <literal>IN ROLE</literal> clause, and in later commands using
 152         the <literal>ROLE</literal> clause.  It is also used as the
 153         default inheritance status when adding this role as a member
 154         using the <literal>GRANT</literal> command.  If not specified,
 155         <literal>INHERIT</literal> is the default.
 156        </para>
 157
 158        <para>
 159         In <productname>PostgreSQL</productname> versions before 16,
 160         inheritance was a role-level attribute that controlled all runtime
 161         membership checks for that role.
 162        </para>
 163       </listitem>
 164      </varlistentry>
 165
 166      <varlistentry>
 167       <term><literal>LOGIN</literal></term>
 168       <term><literal>NOLOGIN</literal></term>
 169       <listitem>
 170        <para>
 171         These clauses determine whether a role is allowed to log in;
 172         that is, whether the role can be given as the initial session
 173         authorization name during client connection.  A role having
 174         the <literal>LOGIN</literal> attribute can be thought of as a user.
 175         Roles without this attribute are useful for managing database
 176         privileges, but are not users in the usual sense of the word.
 177         If not specified,
 178         <literal>NOLOGIN</literal> is the default, except when
 179         <command>CREATE ROLE</command> is invoked through its alternative spelling
 180         <link linkend="sql-createuser"><command>CREATE USER</command></link>.
 181        </para>
 182       </listitem>
 183      </varlistentry>
 184
 185      <varlistentry>
 186       <term><literal>REPLICATION</literal></term>
 187       <term><literal>NOREPLICATION</literal></term>
 188       <listitem>
 189        <para>
 190         These clauses determine whether a role is a replication role.  A role
 191         must have this attribute (or be a superuser) in order to be able to
 192         connect to the server in replication mode (physical or logical
 193         replication) and in order to be able to create or drop replication
 194         slots.
 195         A role having the <literal>REPLICATION</literal> attribute is a very
 196         highly privileged role, and should only be used on roles actually
 197         used for replication. If not specified,
 198         <literal>NOREPLICATION</literal> is the default.
 199         Only superuser roles or roles with <literal>REPLICATION</literal>
 200         can specify <literal>REPLICATION</literal>.
 201        </para>
 202       </listitem>
 203      </varlistentry>
 204
 205      <varlistentry>
 206       <term><literal>BYPASSRLS</literal></term>
 207       <term><literal>NOBYPASSRLS</literal></term>
 208       <listitem>
 209        <para>
 210         These clauses determine whether a role bypasses every row-level
 211         security (RLS) policy.  <literal>NOBYPASSRLS</literal> is the default.
 212         Only superuser roles or roles with <literal>BYPASSRLS</literal>
 213         can specify <literal>BYPASSRLS</literal>.
 214        </para>
 215
 216        <para>
 217         Note that pg_dump will set <literal>row_security</literal> to
 218         <literal>OFF</literal> by default, to ensure all contents of a table are
 219         dumped out.  If the user running pg_dump does not have appropriate
 220         permissions, an error will be returned.  However, superusers and the
 221         owner of the table being dumped always bypass RLS.
 222        </para>
 223       </listitem>
 224      </varlistentry>
 225
 226      <varlistentry>
 227       <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
 228       <listitem>
 229        <para>
 230         If role can log in, this specifies how many concurrent connections
 231         the role can make.  -1 (the default) means no limit. Note that only
 232         normal connections are counted towards this limit. Neither prepared
 233         transactions nor background worker connections are counted towards
 234         this limit.
 235        </para>
 236       </listitem>
 237      </varlistentry>
 238
 239      <varlistentry>
 240       <term>[ <literal>ENCRYPTED</literal> ] <literal>PASSWORD</literal> '<replaceable class="parameter">password</replaceable>'</term>
 241       <term><literal>PASSWORD NULL</literal></term>
 242       <listitem>
 243        <para>
 244         Sets the role's password.  (A password is only of use for
 245         roles having the <literal>LOGIN</literal> attribute, but you
 246         can nonetheless define one for roles without it.)  If you do
 247         not plan to use password authentication you can omit this
 248         option.  If no password is specified, the password will be set
 249         to null and password authentication will always fail for that
 250         user.  A null password can optionally be written explicitly as
 251         <literal>PASSWORD NULL</literal>.
 252        </para>
 253        <note>
 254          <para>
 255            Specifying an empty string will also set the password to null,
 256            but that was not the case before <productname>PostgreSQL</productname>
 257            version 10. In earlier versions, an empty string could be used,
 258            or not, depending on the authentication method and the exact
 259            version, and libpq would refuse to use it in any case.
 260            To avoid the ambiguity, specifying an empty string should be
 261            avoided.
 262          </para>
 263        </note>
 264        <para>
 265         The password is always stored encrypted in the system catalogs. The
 266         <literal>ENCRYPTED</literal> keyword has no effect, but is accepted for
 267         backwards compatibility. The method of encryption is determined
 268         by the configuration parameter <xref linkend="guc-password-encryption"/>.
 269         If the presented password string is already in MD5-encrypted or
 270         SCRAM-encrypted format, then it is stored as-is regardless of
 271         <varname>password_encryption</varname> (since the system cannot decrypt
 272         the specified encrypted password string, to encrypt it in a
 273         different format).  This allows reloading of encrypted passwords
 274         during dump/restore.
 275        </para>
 276       </listitem>
 277      </varlistentry>
 278
 279      <varlistentry>
 280       <term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term>
 281       <listitem>
 282        <para>
 283         The <literal>VALID UNTIL</literal> clause sets a date and
 284         time after which the role's password is no longer valid.  If
 285         this clause is omitted the password will be valid for all time.
 286        </para>
 287       </listitem>
 288      </varlistentry>
 289
 290      <varlistentry>
 291       <term><literal>IN ROLE</literal> <replaceable class="parameter">role_name</replaceable></term>
 292       <listitem>
 293        <para>
 294         The <literal>IN ROLE</literal> clause causes the new role to
 295         be automatically added as a member of the specified existing
 296         roles. The new membership will have the <literal>SET</literal>
 297         option enabled and the <literal>ADMIN</literal> option disabled.
 298         The <literal>INHERIT</literal> option will be enabled unless the
 299         <literal>NOINHERIT</literal> option is specified.
 300        </para>
 301       </listitem>
 302      </varlistentry>
 303
 304      <varlistentry>
 305       <term><literal>ROLE</literal> <replaceable class="parameter">role_name</replaceable></term>
 306       <listitem>
 307        <para>
 308         The <literal>ROLE</literal> clause causes one or more specified
 309         existing roles to be automatically added as members, with the
 310         <literal>SET</literal> option enabled. This in effect makes the
 311         new role a <quote>group</quote>.  Roles named in this clause
 312         with role-level the <literal>INHERIT</literal> attribute will have
 313         the <literal>INHERIT</literal> option enabled in the new membership.
 314         New memberships will have the <literal>ADMIN</literal> option disabled.
 315        </para>
 316       </listitem>
 317      </varlistentry>
 318
 319      <varlistentry>
 320       <term><literal>ADMIN</literal> <replaceable class="parameter">role_name</replaceable></term>
 321       <listitem>
 322        <para>
 323         The <literal>ADMIN</literal> clause has the same effect as
 324         <literal>ROLE</literal>, but the named roles are added as members
 325         of the new role with <literal>ADMIN</literal> enabled, giving
 326         them the right to grant membership in the new role to others.
 327        </para>
 328       </listitem>
 329      </varlistentry>
 330
 331      <varlistentry>
 332       <term><literal>SYSID</literal> <replaceable class="parameter">uid</replaceable></term>
 333       <listitem>
 334        <para>
 335         The <literal>SYSID</literal> clause is ignored, but is accepted
 336         for backwards compatibility.
 337        </para>
 338       </listitem>
 339      </varlistentry>
 340     </variablelist>
 341  </refsect1>
 342
 343  <refsect1>
 344   <title>Notes</title>
 345
 346   <para>
 347    Use <link linkend="sql-alterrole"><command>ALTER ROLE</command></link> to
 348    change the attributes of a role, and <link linkend="sql-droprole"><command>DROP ROLE</command></link>
 349    to remove a role.  All the attributes
 350    specified by <command>CREATE ROLE</command> can be modified by later
 351    <command>ALTER ROLE</command> commands.
 352   </para>
 353
 354   <para>
 355    The preferred way to add and remove members of roles that are being
 356    used as groups is to use
 357    <link linkend="sql-grant"><command>GRANT</command></link> and
 358    <link linkend="sql-revoke"><command>REVOKE</command></link>.
 359   </para>
 360
 361   <para>
 362    The <literal>VALID UNTIL</literal> clause defines an expiration time for a
 363    password only, not for the role per se.  In
 364    particular, the expiration time is not enforced when logging in using
 365    a non-password-based authentication method.
 366   </para>
 367
 368   <para>
 369    The role attributes defined here are non-inheritable, i.e., being a
 370    member of a role with, e.g., <literal>CREATEDB</literal> will not
 371    allow the member to create new databases even if the membership grant
 372    has the <literal>INHERIT</literal> option.  Of course, if the membership
 373    grant has the <literal>SET</literal> option the member role would be able to
 374    <link linkend="sql-set-role"><command>SET ROLE</command></link> to the
 375    createdb role and then create a new database.
 376   </para>
 377
 378   <para>
 379    The membership grants created by the
 380    <literal>IN ROLE</literal>, <literal>ROLE</literal>, and <literal>ADMIN</literal>
 381    clauses have the role executing this command as the grantee.
 382   </para>
 383
 384   <para>
 385    The <literal>INHERIT</literal> attribute is the default for reasons of backwards
 386    compatibility: in prior releases of <productname>PostgreSQL</productname>,
 387    users always had access to all privileges of groups they were members of.
 388    However, <literal>NOINHERIT</literal> provides a closer match to the semantics
 389    specified in the SQL standard.
 390   </para>
 391
 392   <para>
 393    <productname>PostgreSQL</productname> includes a program <xref
 394    linkend="app-createuser"/> that has
 395    the same functionality as <command>CREATE ROLE</command> (in fact,
 396    it calls this command) but can be run from the command shell.
 397   </para>
 398
 399   <para>
 400    The <literal>CONNECTION LIMIT</literal> option is only enforced approximately;
 401    if two new sessions start at about the same time when just one
 402    connection <quote>slot</quote> remains for the role, it is possible that
 403    both will fail.  Also, the limit is never enforced for superusers.
 404   </para>
 405
 406   <para>
 407    Caution must be exercised when specifying an unencrypted password
 408    with this command.  The password will be transmitted to the server
 409    in cleartext, and it might also be logged in the client's command
 410    history or the server log.  The command <xref
 411    linkend="app-createuser"/>, however, transmits
 412    the password encrypted.  Also, <xref linkend="app-psql"/>
 413    contains a command
 414    <command>\password</command> that can be used to safely change the
 415    password later.
 416   </para>
 417  </refsect1>
 418
 419  <refsect1>
 420   <title>Examples</title>
 421
 422   <para>
 423    Create a role that can log in, but don't give it a password:
 424 <programlisting>
 425 CREATE ROLE jonathan LOGIN;
 426 </programlisting>
 427   </para>
 428
 429   <para>
 430    Create a role with a password:
 431 <programlisting>
 432 CREATE USER davide WITH PASSWORD 'jw8s0F4';
 433 </programlisting>
 434    (<command>CREATE USER</command> is the same as <command>CREATE ROLE</command> except
 435    that it implies <literal>LOGIN</literal>.)
 436   </para>
 437
 438   <para>
 439    Create a role with a password that is valid until the end of 2004.
 440    After one second has ticked in 2005, the password is no longer
 441    valid.
 442
 443 <programlisting>
 444 CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';
 445 </programlisting>
 446   </para>
 447
 448   <para>
 449    Create a role that can create databases and manage roles:
 450 <programlisting>
 451 CREATE ROLE admin WITH CREATEDB CREATEROLE;
 452 </programlisting></para>
 453  </refsect1>
 454
 455  <refsect1>
 456   <title>Compatibility</title>
 457
 458   <para>
 459    The <command>CREATE ROLE</command> statement is in the SQL standard,
 460    but the standard only requires the syntax
 461 <synopsis>
 462 CREATE ROLE <replaceable class="parameter">name</replaceable> [ WITH ADMIN <replaceable class="parameter">role_name</replaceable> ]
 463 </synopsis>
 464    Multiple initial administrators, and all the other options of
 465    <command>CREATE ROLE</command>, are
 466    <productname>PostgreSQL</productname> extensions.
 467   </para>
 468
 469   <para>
 470    The SQL standard defines the concepts of users and roles, but it
 471    regards them as distinct concepts and leaves all commands defining
 472    users to be specified by each database implementation.  In
 473    <productname>PostgreSQL</productname> we have chosen to unify
 474    users and roles into a single kind of entity.  Roles therefore
 475    have many more optional attributes than they do in the standard.
 476   </para>
 477
 478   <para>
 479    The behavior specified by the SQL standard is most closely approximated
 480    creating SQL-standard users as <productname>PostgreSQL</productname>
 481    roles with the <literal>NOINHERIT</literal> option, and SQL-standard
 482    roles as <productname>PostgreSQL</productname> roles with the
 483    <literal>INHERIT</literal> option.
 484   </para>
 485
 486   <para>
 487    The <literal>USER</literal> clause has the same behavior as
 488    <literal>ROLE</literal> but has been deprecated:
 489 <synopsis>
 490 USER <replaceable class="parameter">role_name</replaceable> [, ...]
 491 </synopsis>
 492   </para>
 493
 494   <para>
 495    The <literal>IN GROUP</literal> clause has the same behavior as <literal>IN
 496    ROLE</literal> but has been deprecated:
 497 <synopsis>
 498 IN GROUP <replaceable class="parameter">role_name</replaceable> [, ...]
 499 </synopsis>
 500   </para>
 501  </refsect1>
 502
 503  <refsect1>
 504   <title>See Also</title>
 505
 506   <simplelist type="inline">
 507    <member><xref linkend="sql-set-role"/></member>
 508    <member><xref linkend="sql-alterrole"/></member>
 509    <member><xref linkend="sql-droprole"/></member>
 510    <member><xref linkend="sql-grant"/></member>
 511    <member><xref linkend="sql-revoke"/></member>
 512    <member><xref linkend="app-createuser"/></member>
 513    <member><xref linkend="guc-createrole-self-grant"/></member>
 514   </simplelist>
 515  </refsect1>
 516 </refentry>