diff options
Diffstat (limited to 'doc/src/sgml/ref/create_role.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_role.sgml | 504 |
1 files changed, 504 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_role.sgml b/doc/src/sgml/ref/create_role.sgml new file mode 100644 index 0000000..89df6db --- /dev/null +++ b/doc/src/sgml/ref/create_role.sgml @@ -0,0 +1,504 @@ +<!-- +doc/src/sgml/ref/create_role.sgml +PostgreSQL documentation +--> + +<refentry id="sql-createrole"> + <indexterm zone="sql-createrole"> + <primary>CREATE ROLE</primary> + </indexterm> + + <refmeta> + <refentrytitle>CREATE ROLE</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>CREATE ROLE</refname> + <refpurpose>define a new database role</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +CREATE ROLE <replaceable class="parameter">name</replaceable> [ [ WITH ] <replaceable class="parameter">option</replaceable> [ ... ] ] + +<phrase>where <replaceable class="parameter">option</replaceable> can be:</phrase> + + SUPERUSER | NOSUPERUSER + | CREATEDB | NOCREATEDB + | CREATEROLE | NOCREATEROLE + | INHERIT | NOINHERIT + | LOGIN | NOLOGIN + | REPLICATION | NOREPLICATION + | BYPASSRLS | NOBYPASSRLS + | CONNECTION LIMIT <replaceable class="parameter">connlimit</replaceable> + | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>' | PASSWORD NULL + | VALID UNTIL '<replaceable class="parameter">timestamp</replaceable>' + | IN ROLE <replaceable class="parameter">role_name</replaceable> [, ...] + | IN GROUP <replaceable class="parameter">role_name</replaceable> [, ...] + | ROLE <replaceable class="parameter">role_name</replaceable> [, ...] + | ADMIN <replaceable class="parameter">role_name</replaceable> [, ...] + | USER <replaceable class="parameter">role_name</replaceable> [, ...] + | SYSID <replaceable class="parameter">uid</replaceable> +</synopsis> + </refsynopsisdiv> + +<!-- +CAUTION: remember to keep create_user.sgml and create_group.sgml +in sync when changing the above synopsis! +--> + + <refsect1> + <title>Description</title> + + <para> + <command>CREATE ROLE</command> adds a new role to a + <productname>PostgreSQL</productname> database cluster. A role is + an entity that can own database objects and have database privileges; + a role can be considered a <quote>user</quote>, a <quote>group</quote>, or both + depending on how it is used. Refer to + <xref linkend="user-manag"/> and <xref + linkend="client-authentication"/> for information about managing + users and authentication. You must have <literal>CREATEROLE</literal> + privilege or be a database superuser to use this command. + </para> + + <para> + Note that roles are defined at the database cluster + level, and so are valid in all databases in the cluster. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">name</replaceable></term> + <listitem> + <para> + The name of the new role. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>SUPERUSER</literal></term> + <term><literal>NOSUPERUSER</literal></term> + <listitem> + <para> + These clauses determine whether the new role is a <quote>superuser</quote>, + 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, + <literal>NOSUPERUSER</literal> is the default. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CREATEDB</literal></term> + <term><literal>NOCREATEDB</literal></term> + <listitem> + <para> + These clauses define a role's ability to create databases. If + <literal>CREATEDB</literal> is specified, the role being + defined will be allowed to create new databases. Specifying + <literal>NOCREATEDB</literal> will deny a role the ability to + create databases. If not specified, + <literal>NOCREATEDB</literal> is the default. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CREATEROLE</literal></term> + <term><literal>NOCREATEROLE</literal></term> + <listitem> + <para> + These clauses determine whether a role will be permitted to + create, alter, drop, comment on, change the security label for, + and grant or revoke membership in other roles. + See <xref linkend='role-creation' /> for more details about what + capabilities are conferred by this privilege. + If not specified, <literal>NOCREATEROLE</literal> is the default. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INHERIT</literal></term> + <term><literal>NOINHERIT</literal></term> + <listitem> + <para> + These clauses determine whether a role <quote>inherits</quote> the + privileges of roles it is a member of. + A role with the <literal>INHERIT</literal> attribute can automatically + use whatever database privileges have been granted to all roles + it is directly or indirectly a member of. + Without <literal>INHERIT</literal>, membership in another role + only grants the ability to <command>SET ROLE</command> to that other role; + the privileges of the other role are only available after having + done so. + If not specified, + <literal>INHERIT</literal> is the default. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>LOGIN</literal></term> + <term><literal>NOLOGIN</literal></term> + <listitem> + <para> + 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 <literal>LOGIN</literal> 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, + <literal>NOLOGIN</literal> is the default, except when + <command>CREATE ROLE</command> is invoked through its alternative spelling + <link linkend="sql-createuser"><command>CREATE USER</command></link>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>REPLICATION</literal></term> + <term><literal>NOREPLICATION</literal></term> + <listitem> + <para> + These clauses determine whether a role is a replication role. A role + must have this attribute (or be a superuser) in order to be able to + connect to the server in replication mode (physical or logical + replication) and in order to be able to create or drop replication + slots. + A role having the <literal>REPLICATION</literal> attribute is a very + highly privileged role, and should only be used on roles actually + used for replication. If not specified, + <literal>NOREPLICATION</literal> is the default. + You must be a superuser to create a new role having the + <literal>REPLICATION</literal> attribute. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>BYPASSRLS</literal></term> + <term><literal>NOBYPASSRLS</literal></term> + <listitem> + <para> + These clauses determine whether a role bypasses every row-level + security (RLS) policy. <literal>NOBYPASSRLS</literal> is the default. + You must be a superuser to create a new role having + the <literal>BYPASSRLS</literal> attribute. + </para> + + <para> + Note that pg_dump will set <literal>row_security</literal> to + <literal>OFF</literal> by default, to ensure all contents of a table are + dumped out. If the user running pg_dump does not have appropriate + permissions, an error will be returned. However, superusers and the + owner of the table being dumped always bypass RLS. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term> + <listitem> + <para> + If role can log in, this specifies how many concurrent connections + the role can make. -1 (the default) means no limit. Note that only + normal connections are counted towards this limit. Neither prepared + transactions nor background worker connections are counted towards + this limit. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>[ <literal>ENCRYPTED</literal> ] <literal>PASSWORD</literal> '<replaceable class="parameter">password</replaceable>'</term> + <term><literal>PASSWORD NULL</literal></term> + <listitem> + <para> + Sets the role's password. (A password is only of use for + roles having the <literal>LOGIN</literal> 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 + <literal>PASSWORD NULL</literal>. + </para> + <note> + <para> + Specifying an empty string will also set the password to null, + but that was not the case before <productname>PostgreSQL</productname> + version 10. In earlier versions, an empty string could be used, + or not, depending on the authentication method and the exact + version, and libpq would refuse to use it in any case. + To avoid the ambiguity, specifying an empty string should be + avoided. + </para> + </note> + <para> + The password is always stored encrypted in the system catalogs. The + <literal>ENCRYPTED</literal> keyword has no effect, but is accepted for + backwards compatibility. The method of encryption is determined + by the configuration parameter <xref linkend="guc-password-encryption"/>. + If the presented password string is already in MD5-encrypted or + SCRAM-encrypted format, then it is stored as-is regardless of + <varname>password_encryption</varname> (since the system cannot decrypt + the specified encrypted password string, to encrypt it in a + different format). This allows reloading of encrypted passwords + during dump/restore. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term> + <listitem> + <para> + The <literal>VALID UNTIL</literal> 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. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>IN ROLE</literal> <replaceable class="parameter">role_name</replaceable></term> + <listitem> + <para> + The <literal>IN ROLE</literal> 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 <command>GRANT</command> command to do that.) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>IN GROUP</literal> <replaceable class="parameter">role_name</replaceable></term> + <listitem> + <para><literal>IN GROUP</literal> is an obsolete spelling of + <literal>IN ROLE</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ROLE</literal> <replaceable class="parameter">role_name</replaceable></term> + <listitem> + <para> + The <literal>ROLE</literal> 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 <quote>group</quote>.) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>ADMIN</literal> <replaceable class="parameter">role_name</replaceable></term> + <listitem> + <para> + The <literal>ADMIN</literal> clause is like <literal>ROLE</literal>, + but the named roles are added to the new role <literal>WITH ADMIN + OPTION</literal>, giving them the right to grant membership in this role + to others. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>USER</literal> <replaceable class="parameter">role_name</replaceable></term> + <listitem> + <para> + The <literal>USER</literal> clause is an obsolete spelling of + the <literal>ROLE</literal> clause. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>SYSID</literal> <replaceable class="parameter">uid</replaceable></term> + <listitem> + <para> + The <literal>SYSID</literal> clause is ignored, but is accepted + for backwards compatibility. + </para> + </listitem> + </varlistentry> + </variablelist> + </refsect1> + + <refsect1> + <title>Notes</title> + + <para> + Use <link linkend="sql-alterrole"><command>ALTER ROLE</command></link> to + change the attributes of a role, and <link linkend="sql-droprole"><command>DROP ROLE</command></link> + to remove a role. All the attributes + specified by <command>CREATE ROLE</command> can be modified by later + <command>ALTER ROLE</command> commands. + </para> + + <para> + The preferred way to add and remove members of roles that are being + used as groups is to use + <link linkend="sql-grant"><command>GRANT</command></link> and + <link linkend="sql-revoke"><command>REVOKE</command></link>. + </para> + + <para> + The <literal>VALID UNTIL</literal> 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. + </para> + + <para> + The <literal>INHERIT</literal> 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 + <command>CREATE ROLE</command> and <command>ALTER ROLE</command>. For example, being + a member of a role with <literal>CREATEDB</literal> privilege does not immediately + grant the ability to create databases, even if <literal>INHERIT</literal> is set; + it would be necessary to become that role via + <link linkend="sql-set-role"><command>SET ROLE</command></link> before + creating a database. + </para> + + <para> + The <literal>INHERIT</literal> attribute is the default for reasons of backwards + compatibility: in prior releases of <productname>PostgreSQL</productname>, + users always had access to all privileges of groups they were members of. + However, <literal>NOINHERIT</literal> provides a closer match to the semantics + specified in the SQL standard. + </para> + + <para> + Be careful with the <literal>CREATEROLE</literal> privilege. There is no concept of + inheritance for the privileges of a <literal>CREATEROLE</literal>-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 <quote>user</quote> has the + <literal>CREATEROLE</literal> privilege but not the <literal>CREATEDB</literal> privilege, + nonetheless it can create a new role with the <literal>CREATEDB</literal> + privilege. Therefore, regard roles that have the <literal>CREATEROLE</literal> + privilege as almost-superuser-roles. + </para> + + <para> + <productname>PostgreSQL</productname> includes a program <xref + linkend="app-createuser"/> that has + the same functionality as <command>CREATE ROLE</command> (in fact, + it calls this command) but can be run from the command shell. + </para> + + <para> + The <literal>CONNECTION LIMIT</literal> option is only enforced approximately; + if two new sessions start at about the same time when just one + connection <quote>slot</quote> remains for the role, it is possible that + both will fail. Also, the limit is never enforced for superusers. + </para> + + <para> + 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 <xref + linkend="app-createuser"/>, however, transmits + the password encrypted. Also, <xref linkend="app-psql"/> + contains a command + <command>\password</command> that can be used to safely change the + password later. + </para> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + Create a role that can log in, but don't give it a password: +<programlisting> +CREATE ROLE jonathan LOGIN; +</programlisting> + </para> + + <para> + Create a role with a password: +<programlisting> +CREATE USER davide WITH PASSWORD 'jw8s0F4'; +</programlisting> + (<command>CREATE USER</command> is the same as <command>CREATE ROLE</command> except + that it implies <literal>LOGIN</literal>.) + </para> + + <para> + 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. + +<programlisting> +CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01'; +</programlisting> + </para> + + <para> + Create a role that can create databases and manage roles: +<programlisting> +CREATE ROLE admin WITH CREATEDB CREATEROLE; +</programlisting></para> + </refsect1> + + <refsect1> + <title>Compatibility</title> + + <para> + The <command>CREATE ROLE</command> statement is in the SQL standard, + but the standard only requires the syntax +<synopsis> +CREATE ROLE <replaceable class="parameter">name</replaceable> [ WITH ADMIN <replaceable class="parameter">role_name</replaceable> ] +</synopsis> + Multiple initial administrators, and all the other options of + <command>CREATE ROLE</command>, are + <productname>PostgreSQL</productname> extensions. + </para> + + <para> + 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 + <productname>PostgreSQL</productname> 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. + </para> + + <para> + The behavior specified by the SQL standard is most closely approximated + by giving users the <literal>NOINHERIT</literal> attribute, while roles are + given the <literal>INHERIT</literal> attribute. + </para> + </refsect1> + + <refsect1> + <title>See Also</title> + + <simplelist type="inline"> + <member><xref linkend="sql-set-role"/></member> + <member><xref linkend="sql-alterrole"/></member> + <member><xref linkend="sql-droprole"/></member> + <member><xref linkend="sql-grant"/></member> + <member><xref linkend="sql-revoke"/></member> + <member><xref linkend="app-createuser"/></member> + </simplelist> + </refsect1> +</refentry> |