diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createrole.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createrole.html | 281 |
1 files changed, 281 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createrole.html b/doc/src/sgml/html/sql-createrole.html new file mode 100644 index 0000000..685442c --- /dev/null +++ b/doc/src/sgml/html/sql-createrole.html @@ -0,0 +1,281 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE ROLE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createpublication.html" title="CREATE PUBLICATION" /><link rel="next" href="sql-createrule.html" title="CREATE RULE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE ROLE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createpublication.html" title="CREATE PUBLICATION">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createrule.html" title="CREATE RULE">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEROLE"><div class="titlepage"></div><a id="id-1.9.3.78.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE ROLE</span></h2><p>CREATE ROLE — define a new database role</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +CREATE ROLE <em class="replaceable"><code>name</code></em> [ [ WITH ] <em class="replaceable"><code>option</code></em> [ ... ] ] + +<span class="phrase">where <em class="replaceable"><code>option</code></em> can be:</span> + + SUPERUSER | NOSUPERUSER + | CREATEDB | NOCREATEDB + | CREATEROLE | NOCREATEROLE + | INHERIT | NOINHERIT + | LOGIN | NOLOGIN + | REPLICATION | NOREPLICATION + | BYPASSRLS | NOBYPASSRLS + | CONNECTION LIMIT <em class="replaceable"><code>connlimit</code></em> + | [ ENCRYPTED ] PASSWORD '<em class="replaceable"><code>password</code></em>' | PASSWORD NULL + | VALID UNTIL '<em class="replaceable"><code>timestamp</code></em>' + | IN ROLE <em class="replaceable"><code>role_name</code></em> [, ...] + | IN GROUP <em class="replaceable"><code>role_name</code></em> [, ...] + | ROLE <em class="replaceable"><code>role_name</code></em> [, ...] + | ADMIN <em class="replaceable"><code>role_name</code></em> [, ...] + | USER <em class="replaceable"><code>role_name</code></em> [, ...] + | SYSID <em class="replaceable"><code>uid</code></em> +</pre></div><div class="refsect1" id="id-1.9.3.78.5"><h2>Description</h2><p> + <code class="command">CREATE ROLE</code> adds a new role to a + <span class="productname">PostgreSQL</span> database cluster. A role is + an entity that can own database objects and have database privileges; + a role can be considered a <span class="quote">“<span class="quote">user</span>”</span>, a <span class="quote">“<span class="quote">group</span>”</span>, or both + depending on how it is used. Refer to + <a class="xref" href="user-manag.html" title="Chapter 22. Database Roles">Chapter 22</a> and <a class="xref" href="client-authentication.html" title="Chapter 21. Client Authentication">Chapter 21</a> for information about managing + users and authentication. You must have <code class="literal">CREATEROLE</code> + privilege or be a database superuser to use this command. + </p><p> + Note that roles are defined at the database cluster + level, and so are valid in all databases in the cluster. + </p><p> + During role creation it is possible to immediately assign the newly created + role to be a member of an existing role, and also assign existing roles + to be members of the newly created role. The rules for which initial + role membership options are enabled described below in the + <code class="literal">IN ROLE</code>, <code class="literal">ROLE</code>, and + <code class="literal">ADMIN</code> clauses. The <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a> + command has fine-grained option control during membership creation, + and the ability to modify these options after the new role is created. + </p></div><div class="refsect1" id="id-1.9.3.78.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p> + The name of the new role. + </p></dd><dt><span class="term"><code class="literal">SUPERUSER</code><br /></span><span class="term"><code class="literal">NOSUPERUSER</code></span></dt><dd><p> + These clauses determine whether the new role is a <span class="quote">“<span class="quote">superuser</span>”</span>, + 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, + <code class="literal">NOSUPERUSER</code> is the default. + </p></dd><dt><span class="term"><code class="literal">CREATEDB</code><br /></span><span class="term"><code class="literal">NOCREATEDB</code></span></dt><dd><p> + These clauses define a role's ability to create databases. If + <code class="literal">CREATEDB</code> is specified, the role being + defined will be allowed to create new databases. Specifying + <code class="literal">NOCREATEDB</code> will deny a role the ability to + create databases. If not specified, + <code class="literal">NOCREATEDB</code> is the default. + Only superuser roles or roles with <code class="literal">CREATEDB</code> + can specify <code class="literal">CREATEDB</code>. + </p></dd><dt><span class="term"><code class="literal">CREATEROLE</code><br /></span><span class="term"><code class="literal">NOCREATEROLE</code></span></dt><dd><p> + These clauses determine whether a role will be permitted to + create, alter, drop, comment on, and change the security label for + other roles. + See <a class="xref" href="role-attributes.html#ROLE-CREATION">role creation</a> for more details about what + capabilities are conferred by this privilege. + If not specified, <code class="literal">NOCREATEROLE</code> is the default. + </p></dd><dt><span class="term"><code class="literal">INHERIT</code><br /></span><span class="term"><code class="literal">NOINHERIT</code></span></dt><dd><p> + This affects the membership inheritance status when this + role is added as a member of another role, both in this and + future commands. Specifically, it controls the inheritance + status of memberships added with this command using the + <code class="literal">IN ROLE</code> clause, and in later commands using + the <code class="literal">ROLE</code> clause. It is also used as the + default inheritance status when adding this role as a member + using the <code class="literal">GRANT</code> command. If not specified, + <code class="literal">INHERIT</code> is the default. + </p><p> + In <span class="productname">PostgreSQL</span> versions before 16, + inheritance was a role-level attribute that controlled all runtime + membership checks for that role. + </p></dd><dt><span class="term"><code class="literal">LOGIN</code><br /></span><span class="term"><code class="literal">NOLOGIN</code></span></dt><dd><p> + 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 <code class="literal">LOGIN</code> 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, + <code class="literal">NOLOGIN</code> is the default, except when + <code class="command">CREATE ROLE</code> is invoked through its alternative spelling + <a class="link" href="sql-createuser.html" title="CREATE USER"><code class="command">CREATE USER</code></a>. + </p></dd><dt><span class="term"><code class="literal">REPLICATION</code><br /></span><span class="term"><code class="literal">NOREPLICATION</code></span></dt><dd><p> + 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 <code class="literal">REPLICATION</code> attribute is a very + highly privileged role, and should only be used on roles actually + used for replication. If not specified, + <code class="literal">NOREPLICATION</code> is the default. + Only superuser roles or roles with <code class="literal">REPLICATION</code> + can specify <code class="literal">REPLICATION</code>. + </p></dd><dt><span class="term"><code class="literal">BYPASSRLS</code><br /></span><span class="term"><code class="literal">NOBYPASSRLS</code></span></dt><dd><p> + These clauses determine whether a role bypasses every row-level + security (RLS) policy. <code class="literal">NOBYPASSRLS</code> is the default. + Only superuser roles or roles with <code class="literal">BYPASSRLS</code> + can specify <code class="literal">BYPASSRLS</code>. + </p><p> + Note that pg_dump will set <code class="literal">row_security</code> to + <code class="literal">OFF</code> 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. + </p></dd><dt><span class="term"><code class="literal">CONNECTION LIMIT</code> <em class="replaceable"><code>connlimit</code></em></span></dt><dd><p> + 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. + </p></dd><dt><span class="term">[ <code class="literal">ENCRYPTED</code> ] <code class="literal">PASSWORD</code> '<em class="replaceable"><code>password</code></em>'<br /></span><span class="term"><code class="literal">PASSWORD NULL</code></span></dt><dd><p> + Sets the role's password. (A password is only of use for + roles having the <code class="literal">LOGIN</code> 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 + <code class="literal">PASSWORD NULL</code>. + </p><div class="note"><h3 class="title">Note</h3><p> + Specifying an empty string will also set the password to null, + but that was not the case before <span class="productname">PostgreSQL</span> + 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. + </p></div><p> + The password is always stored encrypted in the system catalogs. The + <code class="literal">ENCRYPTED</code> keyword has no effect, but is accepted for + backwards compatibility. The method of encryption is determined + by the configuration parameter <a class="xref" href="runtime-config-connection.html#GUC-PASSWORD-ENCRYPTION">password_encryption</a>. + If the presented password string is already in MD5-encrypted or + SCRAM-encrypted format, then it is stored as-is regardless of + <code class="varname">password_encryption</code> (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. + </p></dd><dt><span class="term"><code class="literal">VALID UNTIL</code> '<em class="replaceable"><code>timestamp</code></em>'</span></dt><dd><p> + The <code class="literal">VALID UNTIL</code> 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. + </p></dd><dt><span class="term"><code class="literal">IN ROLE</code> <em class="replaceable"><code>role_name</code></em></span></dt><dd><p> + The <code class="literal">IN ROLE</code> clause causes the new role to + be automatically added as a member of the specified existing + roles. The new membership will have the <code class="literal">SET</code> + option enabled and the <code class="literal">ADMIN</code> option disabled. + The <code class="literal">INHERIT</code> option will be enabled unless the + <code class="literal">NOINHERIT</code> option is specified. + </p></dd><dt><span class="term"><code class="literal">IN GROUP</code> <em class="replaceable"><code>role_name</code></em></span></dt><dd><p><code class="literal">IN GROUP</code> is an obsolete spelling of + <code class="literal">IN ROLE</code>. + </p></dd><dt><span class="term"><code class="literal">ROLE</code> <em class="replaceable"><code>role_name</code></em></span></dt><dd><p> + The <code class="literal">ROLE</code> clause causes one or more specified + existing roles to be automatically added as members, with the + <code class="literal">SET</code> option enabled. This in effect makes the + new role a <span class="quote">“<span class="quote">group</span>”</span>. Roles named in this clause + with role-level the <code class="literal">INHERIT</code> attribute will have + the <code class="literal">INHERIT</code> option enabled in the new membership. + New memberships will have the <code class="literal">ADMIN</code> option disabled. + </p></dd><dt><span class="term"><code class="literal">ADMIN</code> <em class="replaceable"><code>role_name</code></em></span></dt><dd><p> + The <code class="literal">ADMIN</code> clause has the same effect as + <code class="literal">ROLE</code>, but the named roles are added as members + of the new role with <code class="literal">ADMIN</code> enabled, giving + them the right to grant membership in the new role to others. + </p></dd><dt><span class="term"><code class="literal">USER</code> <em class="replaceable"><code>role_name</code></em></span></dt><dd><p> + The <code class="literal">USER</code> clause is an obsolete spelling of + the <code class="literal">ROLE</code> clause. + </p></dd><dt><span class="term"><code class="literal">SYSID</code> <em class="replaceable"><code>uid</code></em></span></dt><dd><p> + The <code class="literal">SYSID</code> clause is ignored, but is accepted + for backwards compatibility. + </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.78.7"><h2>Notes</h2><p> + Use <a class="link" href="sql-alterrole.html" title="ALTER ROLE"><code class="command">ALTER ROLE</code></a> to + change the attributes of a role, and <a class="link" href="sql-droprole.html" title="DROP ROLE"><code class="command">DROP ROLE</code></a> + to remove a role. All the attributes + specified by <code class="command">CREATE ROLE</code> can be modified by later + <code class="command">ALTER ROLE</code> commands. + </p><p> + The preferred way to add and remove members of roles that are being + used as groups is to use + <a class="link" href="sql-grant.html" title="GRANT"><code class="command">GRANT</code></a> and + <a class="link" href="sql-revoke.html" title="REVOKE"><code class="command">REVOKE</code></a>. + </p><p> + The <code class="literal">VALID UNTIL</code> 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. + </p><p> + The role attributes defined here are non-inheritable, i.e., being a + member of a role with, e.g., <code class="literal">CREATEDB</code> will not + allow the member to create new databases even if the membership grant + has the <code class="literal">INHERIT</code> option. Of course, if the membership + grant has the <code class="literal">SET</code> option the member role would be able to + <a class="link" href="sql-set-role.html" title="SET ROLE"><code class="command">SET ROLE</code></a> to the + createdb role and then create a new database. + </p><p> + The membership grants created by the + <code class="literal">IN ROLE</code>, <code class="literal">ROLE</code>, and <code class="literal">ADMIN</code> + clauses have the role executing this command as the grantee. + </p><p> + The <code class="literal">INHERIT</code> attribute is the default for reasons of backwards + compatibility: in prior releases of <span class="productname">PostgreSQL</span>, + users always had access to all privileges of groups they were members of. + However, <code class="literal">NOINHERIT</code> provides a closer match to the semantics + specified in the SQL standard. + </p><p> + <span class="productname">PostgreSQL</span> includes a program <a class="xref" href="app-createuser.html" title="createuser"><span class="refentrytitle"><span class="application">createuser</span></span></a> that has + the same functionality as <code class="command">CREATE ROLE</code> (in fact, + it calls this command) but can be run from the command shell. + </p><p> + The <code class="literal">CONNECTION LIMIT</code> option is only enforced approximately; + if two new sessions start at about the same time when just one + connection <span class="quote">“<span class="quote">slot</span>”</span> remains for the role, it is possible that + both will fail. Also, the limit is never enforced for superusers. + </p><p> + 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 <a class="xref" href="app-createuser.html" title="createuser"><span class="refentrytitle"><span class="application">createuser</span></span></a>, however, transmits + the password encrypted. Also, <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a> + contains a command + <code class="command">\password</code> that can be used to safely change the + password later. + </p></div><div class="refsect1" id="id-1.9.3.78.8"><h2>Examples</h2><p> + Create a role that can log in, but don't give it a password: +</p><pre class="programlisting"> +CREATE ROLE jonathan LOGIN; +</pre><p> + </p><p> + Create a role with a password: +</p><pre class="programlisting"> +CREATE USER davide WITH PASSWORD 'jw8s0F4'; +</pre><p> + (<code class="command">CREATE USER</code> is the same as <code class="command">CREATE ROLE</code> except + that it implies <code class="literal">LOGIN</code>.) + </p><p> + 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. + +</p><pre class="programlisting"> +CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01'; +</pre><p> + </p><p> + Create a role that can create databases and manage roles: +</p><pre class="programlisting"> +CREATE ROLE admin WITH CREATEDB CREATEROLE; +</pre></div><div class="refsect1" id="id-1.9.3.78.9"><h2>Compatibility</h2><p> + The <code class="command">CREATE ROLE</code> statement is in the SQL standard, + but the standard only requires the syntax +</p><pre class="synopsis"> +CREATE ROLE <em class="replaceable"><code>name</code></em> [ WITH ADMIN <em class="replaceable"><code>role_name</code></em> ] +</pre><p> + Multiple initial administrators, and all the other options of + <code class="command">CREATE ROLE</code>, are + <span class="productname">PostgreSQL</span> extensions. + </p><p> + 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 + <span class="productname">PostgreSQL</span> 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. + </p><p> + The behavior specified by the SQL standard is most closely approximated + creating SQL-standard users as <span class="productname">PostgreSQL</span> + roles with the <code class="literal">NOINHERIT</code> option, and SQL-standard + roles as <span class="productname">PostgreSQL</span> roles with the + <code class="literal">INHERIT</code> option. + </p></div><div class="refsect1" id="id-1.9.3.78.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-set-role.html" title="SET ROLE"><span class="refentrytitle">SET ROLE</span></a>, <a class="xref" href="sql-alterrole.html" title="ALTER ROLE"><span class="refentrytitle">ALTER ROLE</span></a>, <a class="xref" href="sql-droprole.html" title="DROP ROLE"><span class="refentrytitle">DROP ROLE</span></a>, <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a>, <a class="xref" href="sql-revoke.html" title="REVOKE"><span class="refentrytitle">REVOKE</span></a>, <a class="xref" href="app-createuser.html" title="createuser"><span class="refentrytitle"><span class="application">createuser</span></span></a>, <a class="xref" href="runtime-config-client.html#GUC-CREATEROLE-SELF-GRANT">createrole_self_grant</a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createpublication.html" title="CREATE PUBLICATION">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createrule.html" title="CREATE RULE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE PUBLICATION </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE RULE</td></tr></table></div></body></html>
\ No newline at end of file |