Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
sql-createrole.html281 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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">3CREATE ROLE <em class="replaceable"><code>name</code></em> [ [ WITH ] <em class="replaceable"><code>option</code></em> [ ... ] ]4 5<span class="phrase">where <em class="replaceable"><code>option</code></em> can be:</span>6 7      SUPERUSER | NOSUPERUSER8    | CREATEDB | NOCREATEDB9    | CREATEROLE | NOCREATEROLE10    | INHERIT | NOINHERIT11    | LOGIN | NOLOGIN12    | REPLICATION | NOREPLICATION13    | BYPASSRLS | NOBYPASSRLS14    | CONNECTION LIMIT <em class="replaceable"><code>connlimit</code></em>15    | [ ENCRYPTED ] PASSWORD '<em class="replaceable"><code>password</code></em>' | PASSWORD NULL16    | VALID UNTIL '<em class="replaceable"><code>timestamp</code></em>'17    | IN ROLE <em class="replaceable"><code>role_name</code></em> [, ...]18    | IN GROUP <em class="replaceable"><code>role_name</code></em> [, ...]19    | ROLE <em class="replaceable"><code>role_name</code></em> [, ...]20    | ADMIN <em class="replaceable"><code>role_name</code></em> [, ...]21    | USER <em class="replaceable"><code>role_name</code></em> [, ...]22    | SYSID <em class="replaceable"><code>uid</code></em>23</pre></div><div class="refsect1" id="id-1.9.3.78.5"><h2>Description</h2><p>24   <code class="command">CREATE ROLE</code> adds a new role to a25   <span class="productname">PostgreSQL</span> database cluster.  A role is26   an entity that can own database objects and have database privileges;27   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 both28   depending on how it is used.  Refer to29   <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 managing30   users and authentication.  You must have <code class="literal">CREATEROLE</code>31   privilege or be a database superuser to use this command.32  </p><p>33   Note that roles are defined at the database cluster34   level, and so are valid in all databases in the cluster.35  </p><p>36   During role creation it is possible to immediately assign the newly created37   role to be a member of an existing role, and also assign existing roles38   to be members of the newly created role.  The rules for which initial39   role membership options are enabled described below in the40   <code class="literal">IN ROLE</code>, <code class="literal">ROLE</code>, and41   <code class="literal">ADMIN</code> clauses.  The <a class="xref" href="sql-grant.html" title="GRANT"><span class="refentrytitle">GRANT</span></a>42   command has fine-grained option control during membership creation,43   and the ability to modify these options after the new role is created.44  </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>45        The name of the new role.46       </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>47        These clauses determine whether the new role is a <span class="quote">“<span class="quote">superuser</span>”</span>,48        who can override all access restrictions within the database.49        Superuser status is dangerous and should be used only when really50        needed.  You must yourself be a superuser to create a new superuser.51        If not specified,52        <code class="literal">NOSUPERUSER</code> is the default.53       </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>54        These clauses define a role's ability to create databases.  If55        <code class="literal">CREATEDB</code> is specified, the role being56        defined will be allowed to create new databases. Specifying57        <code class="literal">NOCREATEDB</code> will deny a role the ability to58        create databases. If not specified,59        <code class="literal">NOCREATEDB</code> is the default.60        Only superuser roles or roles with <code class="literal">CREATEDB</code>61        can specify <code class="literal">CREATEDB</code>.62       </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>63        These clauses determine whether a role will be permitted to64        create, alter, drop, comment on, and change the security label for65        other roles.66        See <a class="xref" href="role-attributes.html#ROLE-CREATION">role creation</a> for more details about what67        capabilities are conferred by this privilege.68        If not specified, <code class="literal">NOCREATEROLE</code> is the default.69       </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>70        This affects the membership inheritance status when this71        role is added as a member of another role, both in this and72        future commands.  Specifically, it controls the inheritance73        status of memberships added with this command using the74        <code class="literal">IN ROLE</code> clause, and in later commands using75        the <code class="literal">ROLE</code> clause.  It is also used as the76        default inheritance status when adding this role as a member77        using the <code class="literal">GRANT</code> command.  If not specified,78        <code class="literal">INHERIT</code> is the default.79       </p><p>80        In <span class="productname">PostgreSQL</span> versions before 16,81        inheritance was a role-level attribute that controlled all runtime82        membership checks for that role.83       </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>84        These clauses determine whether a role is allowed to log in;85        that is, whether the role can be given as the initial session86        authorization name during client connection.  A role having87        the <code class="literal">LOGIN</code> attribute can be thought of as a user.88        Roles without this attribute are useful for managing database89        privileges, but are not users in the usual sense of the word.90        If not specified,91        <code class="literal">NOLOGIN</code> is the default, except when92        <code class="command">CREATE ROLE</code> is invoked through its alternative spelling93        <a class="link" href="sql-createuser.html" title="CREATE USER"><code class="command">CREATE USER</code></a>.94       </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>95        These clauses determine whether a role is a replication role.  A role96        must have this attribute (or be a superuser) in order to be able to97        connect to the server in replication mode (physical or logical98        replication) and in order to be able to create or drop replication99        slots.100        A role having the <code class="literal">REPLICATION</code> attribute is a very101        highly privileged role, and should only be used on roles actually102        used for replication. If not specified,103        <code class="literal">NOREPLICATION</code> is the default.104        Only superuser roles or roles with <code class="literal">REPLICATION</code>105        can specify <code class="literal">REPLICATION</code>.106       </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>107        These clauses determine whether a role bypasses every row-level108        security (RLS) policy.  <code class="literal">NOBYPASSRLS</code> is the default.109        Only superuser roles or roles with <code class="literal">BYPASSRLS</code>110        can specify <code class="literal">BYPASSRLS</code>.111       </p><p>112        Note that pg_dump will set <code class="literal">row_security</code> to113        <code class="literal">OFF</code> by default, to ensure all contents of a table are114        dumped out.  If the user running pg_dump does not have appropriate115        permissions, an error will be returned.  However, superusers and the116        owner of the table being dumped always bypass RLS.117       </p></dd><dt><span class="term"><code class="literal">CONNECTION LIMIT</code> <em class="replaceable"><code>connlimit</code></em></span></dt><dd><p>118        If role can log in, this specifies how many concurrent connections119        the role can make.  -1 (the default) means no limit. Note that only120        normal connections are counted towards this limit. Neither prepared121        transactions nor background worker connections are counted towards122        this limit.123       </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>124        Sets the role's password.  (A password is only of use for125        roles having the <code class="literal">LOGIN</code> attribute, but you126        can nonetheless define one for roles without it.)  If you do127        not plan to use password authentication you can omit this128        option.  If no password is specified, the password will be set129        to null and password authentication will always fail for that130        user.  A null password can optionally be written explicitly as131        <code class="literal">PASSWORD NULL</code>.132       </p><div class="note"><h3 class="title">Note</h3><p>133           Specifying an empty string will also set the password to null,134           but that was not the case before <span class="productname">PostgreSQL</span>135           version 10. In earlier versions, an empty string could be used,136           or not, depending on the authentication method and the exact137           version, and libpq would refuse to use it in any case.138           To avoid the ambiguity, specifying an empty string should be139           avoided.140         </p></div><p>141        The password is always stored encrypted in the system catalogs. The142        <code class="literal">ENCRYPTED</code> keyword has no effect, but is accepted for143        backwards compatibility. The method of encryption is determined144        by the configuration parameter <a class="xref" href="runtime-config-connection.html#GUC-PASSWORD-ENCRYPTION">password_encryption</a>.145        If the presented password string is already in MD5-encrypted or146        SCRAM-encrypted format, then it is stored as-is regardless of147        <code class="varname">password_encryption</code> (since the system cannot decrypt148        the specified encrypted password string, to encrypt it in a149        different format).  This allows reloading of encrypted passwords150        during dump/restore.151       </p></dd><dt><span class="term"><code class="literal">VALID UNTIL</code> '<em class="replaceable"><code>timestamp</code></em>'</span></dt><dd><p>152        The <code class="literal">VALID UNTIL</code> clause sets a date and153        time after which the role's password is no longer valid.  If154        this clause is omitted the password will be valid for all time.155       </p></dd><dt><span class="term"><code class="literal">IN ROLE</code> <em class="replaceable"><code>role_name</code></em></span></dt><dd><p>156        The <code class="literal">IN ROLE</code> clause causes the new role to157        be automatically added as a member of the specified existing158        roles. The new membership will have the <code class="literal">SET</code>159        option enabled and the <code class="literal">ADMIN</code> option disabled.160        The <code class="literal">INHERIT</code> option will be enabled unless the161        <code class="literal">NOINHERIT</code> option is specified.162       </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 of163        <code class="literal">IN ROLE</code>.164       </p></dd><dt><span class="term"><code class="literal">ROLE</code> <em class="replaceable"><code>role_name</code></em></span></dt><dd><p>165        The <code class="literal">ROLE</code> clause causes one or more specified166        existing roles to be automatically added as members, with the167        <code class="literal">SET</code> option enabled. This in effect makes the168        new role a <span class="quote">“<span class="quote">group</span>”</span>.  Roles named in this clause169        with the role-level <code class="literal">INHERIT</code> attribute will have170        the <code class="literal">INHERIT</code> option enabled in the new membership.171        New memberships will have the <code class="literal">ADMIN</code> option disabled.172       </p></dd><dt><span class="term"><code class="literal">ADMIN</code> <em class="replaceable"><code>role_name</code></em></span></dt><dd><p>173        The <code class="literal">ADMIN</code> clause has the same effect as174        <code class="literal">ROLE</code>, but the named roles are added as members175        of the new role with <code class="literal">ADMIN</code> enabled, giving176        them the right to grant membership in the new role to others.177       </p></dd><dt><span class="term"><code class="literal">USER</code> <em class="replaceable"><code>role_name</code></em></span></dt><dd><p>178        The <code class="literal">USER</code> clause is an obsolete spelling of179        the <code class="literal">ROLE</code> clause.180       </p></dd><dt><span class="term"><code class="literal">SYSID</code> <em class="replaceable"><code>uid</code></em></span></dt><dd><p>181        The <code class="literal">SYSID</code> clause is ignored, but is accepted182        for backwards compatibility.183       </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.78.7"><h2>Notes</h2><p>184   Use <a class="link" href="sql-alterrole.html" title="ALTER ROLE"><code class="command">ALTER ROLE</code></a> to185   change the attributes of a role, and <a class="link" href="sql-droprole.html" title="DROP ROLE"><code class="command">DROP ROLE</code></a>186   to remove a role.  All the attributes187   specified by <code class="command">CREATE ROLE</code> can be modified by later188   <code class="command">ALTER ROLE</code> commands.189  </p><p>190   The preferred way to add and remove members of roles that are being191   used as groups is to use192   <a class="link" href="sql-grant.html" title="GRANT"><code class="command">GRANT</code></a> and193   <a class="link" href="sql-revoke.html" title="REVOKE"><code class="command">REVOKE</code></a>.194  </p><p>195   The <code class="literal">VALID UNTIL</code> clause defines an expiration time for a196   password only, not for the role per se.  In197   particular, the expiration time is not enforced when logging in using198   a non-password-based authentication method.199  </p><p>200   The role attributes defined here are non-inheritable, i.e., being a201   member of a role with, e.g., <code class="literal">CREATEDB</code> will not202   allow the member to create new databases even if the membership grant203   has the <code class="literal">INHERIT</code> option.  Of course, if the membership204   grant has the <code class="literal">SET</code> option the member role would be able to205   <a class="link" href="sql-set-role.html" title="SET ROLE"><code class="command">SET ROLE</code></a> to the206   createdb role and then create a new database.207  </p><p>208   The membership grants created by the209   <code class="literal">IN ROLE</code>, <code class="literal">ROLE</code>, and <code class="literal">ADMIN</code>210   clauses have the role executing this command as the grantor.211  </p><p>212   The <code class="literal">INHERIT</code> attribute is the default for reasons of backwards213   compatibility: in prior releases of <span class="productname">PostgreSQL</span>,214   users always had access to all privileges of groups they were members of.215   However, <code class="literal">NOINHERIT</code> provides a closer match to the semantics216   specified in the SQL standard.217  </p><p>218   <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 has219   the same functionality as <code class="command">CREATE ROLE</code> (in fact,220   it calls this command) but can be run from the command shell.221  </p><p>222   The <code class="literal">CONNECTION LIMIT</code> option is only enforced approximately;223   if two new sessions start at about the same time when just one224   connection <span class="quote">“<span class="quote">slot</span>”</span> remains for the role, it is possible that225   both will fail.  Also, the limit is never enforced for superusers.226  </p><p>227   Caution must be exercised when specifying an unencrypted password228   with this command.  The password will be transmitted to the server229   in cleartext, and it might also be logged in the client's command230   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, transmits231   the password encrypted.  Also, <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a>232   contains a command233   <code class="command">\password</code> that can be used to safely change the234   password later.235  </p></div><div class="refsect1" id="id-1.9.3.78.8"><h2>Examples</h2><p>236   Create a role that can log in, but don't give it a password:237</p><pre class="programlisting">238CREATE ROLE jonathan LOGIN;239</pre><p>240  </p><p>241   Create a role with a password:242</p><pre class="programlisting">243CREATE USER davide WITH PASSWORD 'jw8s0F4';244</pre><p>245   (<code class="command">CREATE USER</code> is the same as <code class="command">CREATE ROLE</code> except246   that it implies <code class="literal">LOGIN</code>.)247  </p><p>248   Create a role with a password that is valid until the end of 2004.249   After one second has ticked in 2005, the password is no longer250   valid.251 252</p><pre class="programlisting">253CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';254</pre><p>255  </p><p>256   Create a role that can create databases and manage roles:257</p><pre class="programlisting">258CREATE ROLE admin WITH CREATEDB CREATEROLE;259</pre></div><div class="refsect1" id="id-1.9.3.78.9"><h2>Compatibility</h2><p>260   The <code class="command">CREATE ROLE</code> statement is in the SQL standard,261   but the standard only requires the syntax262</p><pre class="synopsis">263CREATE ROLE <em class="replaceable"><code>name</code></em> [ WITH ADMIN <em class="replaceable"><code>role_name</code></em> ]264</pre><p>265   Multiple initial administrators, and all the other options of266   <code class="command">CREATE ROLE</code>, are267   <span class="productname">PostgreSQL</span> extensions.268  </p><p>269   The SQL standard defines the concepts of users and roles, but it270   regards them as distinct concepts and leaves all commands defining271   users to be specified by each database implementation.  In272   <span class="productname">PostgreSQL</span> we have chosen to unify273   users and roles into a single kind of entity.  Roles therefore274   have many more optional attributes than they do in the standard.275  </p><p>276   The behavior specified by the SQL standard is most closely approximated277   creating SQL-standard users as <span class="productname">PostgreSQL</span>278   roles with the <code class="literal">NOINHERIT</code> option, and SQL-standard279   roles as <span class="productname">PostgreSQL</span> roles with the280   <code class="literal">INHERIT</code> option.281  </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE RULE</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai