Debian Codegraph @

doc/src/sgml/ref/create_role.sgml

raw
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 are 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 <warning>
277 <para>
278 Support for MD5-encrypted passwords is deprecated and will be removed
279 in a future release of <productname>PostgreSQL</productname>. Refer
280 to <xref linkend="auth-password"/> for details about migrating to
281 another password type.
282 </para>
283 </warning>
284 </listitem>
285 </varlistentry>
286
287 <varlistentry>
288 <term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term>
289 <listitem>
290 <para>
291 The <literal>VALID UNTIL</literal> clause sets a date and
292 time after which the role's password is no longer valid. If
293 this clause is omitted the password will be valid for all time.
294 </para>
295 </listitem>
296 </varlistentry>
297
298 <varlistentry>
299 <term><literal>IN ROLE</literal> <replaceable class="parameter">role_name</replaceable></term>
300 <listitem>
301 <para>
302 The <literal>IN ROLE</literal> clause causes the new role to
303 be automatically added as a member of the specified existing
304 roles. The new membership will have the <literal>SET</literal>
305 option enabled and the <literal>ADMIN</literal> option disabled.
306 The <literal>INHERIT</literal> option will be enabled unless the
307 <literal>NOINHERIT</literal> option is specified.
308 </para>
309 </listitem>
310 </varlistentry>
311
312 <varlistentry>
313 <term><literal>ROLE</literal> <replaceable class="parameter">role_name</replaceable></term>
314 <listitem>
315 <para>
316 The <literal>ROLE</literal> clause causes one or more specified
317 existing roles to be automatically added as members, with the
318 <literal>SET</literal> option enabled. This in effect makes the
319 new role a <quote>group</quote>. Roles named in this clause
320 with the role-level <literal>INHERIT</literal> attribute will have
321 the <literal>INHERIT</literal> option enabled in the new membership.
322 New memberships will have the <literal>ADMIN</literal> option disabled.
323 </para>
324 </listitem>
325 </varlistentry>
326
327 <varlistentry>
328 <term><literal>ADMIN</literal> <replaceable class="parameter">role_name</replaceable></term>
329 <listitem>
330 <para>
331 The <literal>ADMIN</literal> clause has the same effect as
332 <literal>ROLE</literal>, but the named roles are added as members
333 of the new role with <literal>ADMIN</literal> enabled, giving
334 them the right to grant membership in the new role to others.
335 </para>
336 </listitem>
337 </varlistentry>
338
339 <varlistentry>
340 <term><literal>SYSID</literal> <replaceable class="parameter">uid</replaceable></term>
341 <listitem>
342 <para>
343 The <literal>SYSID</literal> clause is ignored, but is accepted
344 for backwards compatibility.
345 </para>
346 </listitem>
347 </varlistentry>
348 </variablelist>
349 </refsect1>
350
351 <refsect1>
352 <title>Notes</title>
353
354 <para>
355 Use <link linkend="sql-alterrole"><command>ALTER ROLE</command></link> to
356 change the attributes of a role, and <link linkend="sql-droprole"><command>DROP ROLE</command></link>
357 to remove a role. All the attributes
358 specified by <command>CREATE ROLE</command> can be modified by later
359 <command>ALTER ROLE</command> commands.
360 </para>
361
362 <para>
363 The preferred way to add and remove members of roles that are being
364 used as groups is to use
365 <link linkend="sql-grant"><command>GRANT</command></link> and
366 <link linkend="sql-revoke"><command>REVOKE</command></link>.
367 </para>
368
369 <para>
370 The <literal>VALID UNTIL</literal> clause defines an expiration time for a
371 password only, not for the role per se. In
372 particular, the expiration time is not enforced when logging in using
373 a non-password-based authentication method.
374 </para>
375
376 <para>
377 The role attributes defined here are non-inheritable, i.e., being a
378 member of a role with, e.g., <literal>CREATEDB</literal> will not
379 allow the member to create new databases even if the membership grant
380 has the <literal>INHERIT</literal> option. Of course, if the membership
381 grant has the <literal>SET</literal> option the member role would be able to
382 <link linkend="sql-set-role"><command>SET ROLE</command></link> to the
383 createdb role and then create a new database.
384 </para>
385
386 <para>
387 The membership grants created by the
388 <literal>IN ROLE</literal>, <literal>ROLE</literal>, and <literal>ADMIN</literal>
389 clauses have the role executing this command as the grantor.
390 </para>
391
392 <para>
393 The <literal>INHERIT</literal> attribute is the default for reasons of backwards
394 compatibility: in prior releases of <productname>PostgreSQL</productname>,
395 users always had access to all privileges of groups they were members of.
396 However, <literal>NOINHERIT</literal> provides a closer match to the semantics
397 specified in the SQL standard.
398 </para>
399
400 <para>
401 <productname>PostgreSQL</productname> includes a program <xref
402 linkend="app-createuser"/> that has
403 the same functionality as <command>CREATE ROLE</command> (in fact,
404 it calls this command) but can be run from the command shell.
405 </para>
406
407 <para>
408 The <literal>CONNECTION LIMIT</literal> option is only enforced approximately;
409 if two new sessions start at about the same time when just one
410 connection <quote>slot</quote> remains for the role, it is possible that
411 both will fail. Also, the limit is never enforced for superusers.
412 </para>
413
414 <para>
415 Caution must be exercised when specifying an unencrypted password
416 with this command. The password will be transmitted to the server
417 in cleartext, and it might also be logged in the client's command
418 history or the server log. The command <xref
419 linkend="app-createuser"/>, however, transmits
420 the password encrypted. Also, <xref linkend="app-psql"/>
421 contains a command
422 <command>\password</command> that can be used to safely change the
423 password later.
424 </para>
425 </refsect1>
426
427 <refsect1>
428 <title>Examples</title>
429
430 <para>
431 Create a role that can log in, but don't give it a password:
432 <programlisting>
433 CREATE ROLE jonathan LOGIN;
434 </programlisting>
435 </para>
436
437 <para>
438 Create a role with a password:
439 <programlisting>
440 CREATE USER davide WITH PASSWORD 'jw8s0F4';
441 </programlisting>
442 (<command>CREATE USER</command> is the same as <command>CREATE ROLE</command> except
443 that it implies <literal>LOGIN</literal>.)
444 </para>
445
446 <para>
447 Create a role with a password that is valid until the end of 2004.
448 After one second has ticked in 2005, the password is no longer
449 valid.
450
451 <programlisting>
452 CREATE ROLE miriam WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01';
453 </programlisting>
454 </para>
455
456 <para>
457 Create a role that can create databases and manage roles:
458 <programlisting>
459 CREATE ROLE admin WITH CREATEDB CREATEROLE;
460 </programlisting></para>
461 </refsect1>
462
463 <refsect1>
464 <title>Compatibility</title>
465
466 <para>
467 The <command>CREATE ROLE</command> statement is in the SQL standard,
468 but the standard only requires the syntax
469 <synopsis>
470 CREATE ROLE <replaceable class="parameter">name</replaceable> [ WITH ADMIN <replaceable class="parameter">role_name</replaceable> ]
471 </synopsis>
472 Multiple initial administrators, and all the other options of
473 <command>CREATE ROLE</command>, are
474 <productname>PostgreSQL</productname> extensions.
475 </para>
476
477 <para>
478 The SQL standard defines the concepts of users and roles, but it
479 regards them as distinct concepts and leaves all commands defining
480 users to be specified by each database implementation. In
481 <productname>PostgreSQL</productname> we have chosen to unify
482 users and roles into a single kind of entity. Roles therefore
483 have many more optional attributes than they do in the standard.
484 </para>
485
486 <para>
487 The behavior specified by the SQL standard is most closely approximated
488 creating SQL-standard users as <productname>PostgreSQL</productname>
489 roles with the <literal>NOINHERIT</literal> option, and SQL-standard
490 roles as <productname>PostgreSQL</productname> roles with the
491 <literal>INHERIT</literal> option.
492 </para>
493
494 <para>
495 The <literal>USER</literal> clause has the same behavior as
496 <literal>ROLE</literal> but has been deprecated:
497 <synopsis>
498 USER <replaceable class="parameter">role_name</replaceable> [, ...]
499 </synopsis>
500 </para>
501
502 <para>
503 The <literal>IN GROUP</literal> clause has the same behavior as <literal>IN
504 ROLE</literal> but has been deprecated:
505 <synopsis>
506 IN GROUP <replaceable class="parameter">role_name</replaceable> [, ...]
507 </synopsis>
508 </para>
509 </refsect1>
510
511 <refsect1>
512 <title>See Also</title>
513
514 <simplelist type="inline">
515 <member><xref linkend="sql-set-role"/></member>
516 <member><xref linkend="sql-alterrole"/></member>
517 <member><xref linkend="sql-droprole"/></member>
518 <member><xref linkend="sql-grant"/></member>
519 <member><xref linkend="sql-revoke"/></member>
520 <member><xref linkend="app-createuser"/></member>
521 <member><xref linkend="guc-createrole-self-grant"/></member>
522 </simplelist>
523 </refsect1>
524 </refentry>