Debian Codegraph @

doc/src/sgml/user-manag.sgml

raw
1 <!-- doc/src/sgml/user-manag.sgml -->
2
3 <chapter id="user-manag">
4 <title>Database Roles</title>
5
6 <para>
7 <productname>PostgreSQL</productname> manages database access permissions
8 using the concept of <firstterm>roles</firstterm>. A role can be thought of as
9 either a database user, or a group of database users, depending on how
10 the role is set up. Roles can own database objects (for example, tables
11 and functions) and can assign privileges on those objects to other roles to
12 control who has access to which objects. Furthermore, it is possible
13 to grant <firstterm>membership</firstterm> in a role to another role, thus
14 allowing the member role to use privileges assigned to another role.
15 </para>
16
17 <para>
18 The concept of roles subsumes the concepts of <quote>users</quote> and
19 <quote>groups</quote>. In <productname>PostgreSQL</productname> versions
20 before 8.1, users and groups were distinct kinds of entities, but now
21 there are only roles. Any role can act as a user, a group, or both.
22 </para>
23
24 <para>
25 This chapter describes how to create and manage roles.
26 More information about the effects of role privileges on various
27 database objects can be found in <xref linkend="ddl-priv"/>.
28 </para>
29
30 <sect1 id="database-roles">
31 <title>Database Roles</title>
32
33 <indexterm zone="database-roles">
34 <primary>role</primary>
35 </indexterm>
36
37 <indexterm zone="database-roles">
38 <primary>user</primary>
39 </indexterm>
40
41 <indexterm>
42 <primary>CREATE ROLE</primary>
43 </indexterm>
44
45 <indexterm>
46 <primary>DROP ROLE</primary>
47 </indexterm>
48
49 <para>
50 Database roles are conceptually completely separate from
51 operating system users. In practice it might be convenient to
52 maintain a correspondence, but this is not required. Database roles
53 are global across a database cluster installation (and not
54 per individual database). To create a role use the <link
55 linkend="sql-createrole"><command>CREATE ROLE</command></link> SQL command:
56 <synopsis>
57 CREATE ROLE <replaceable>name</replaceable>;
58 </synopsis>
59 <replaceable>name</replaceable> follows the rules for SQL
60 identifiers: either unadorned without special characters, or
61 double-quoted. (In practice, you will usually want to add additional
62 options, such as <literal>LOGIN</literal>, to the command. More details appear
63 below.) To remove an existing role, use the analogous
64 <link linkend="sql-droprole"><command>DROP ROLE</command></link> command:
65 <synopsis>
66 DROP ROLE <replaceable>name</replaceable>;
67 </synopsis>
68 </para>
69
70 <indexterm>
71 <primary>createuser</primary>
72 </indexterm>
73
74 <indexterm>
75 <primary>dropuser</primary>
76 </indexterm>
77
78 <para>
79 For convenience, the programs <xref linkend="app-createuser"/>
80 and <xref linkend="app-dropuser"/> are provided as wrappers
81 around these SQL commands that can be called from the shell command
82 line:
83 <synopsis>
84 createuser <replaceable>name</replaceable>
85 dropuser <replaceable>name</replaceable>
86 </synopsis>
87 </para>
88
89 <para>
90 To determine the set of existing roles, examine the <structname>pg_roles</structname>
91 system catalog, for example:
92 <synopsis>
93 SELECT rolname FROM pg_roles;
94 </synopsis>
95 or to see just those capable of logging in:
96 <synopsis>
97 SELECT rolname FROM pg_roles WHERE rolcanlogin;
98 </synopsis>
99 The <xref linkend="app-psql"/> program's <literal>\du</literal> meta-command
100 is also useful for listing the existing roles.
101 </para>
102
103 <para>
104 In order to bootstrap the database system, a freshly initialized
105 system always contains one predefined login-capable role. This role
106 is always a <quote>superuser</quote>, and it will have
107 the same name as the operating system user that initialized the
108 database cluster with <command>initdb</command> unless a different name
109 is specified. This role is often named
110 <literal>postgres</literal>. In order to create more roles you
111 first have to connect as this initial role.
112 </para>
113
114 <para>
115 Every connection to the database server is made using the name of some
116 particular role, and this role determines the initial access privileges for
117 commands issued in that connection.
118 The role name to use for a particular database
119 connection is indicated by the client that is initiating the
120 connection request in an application-specific fashion. For example,
121 the <command>psql</command> program uses the
122 <option>-U</option> command line option to indicate the role to
123 connect as. Many applications assume the name of the current
124 operating system user by default (including
125 <command>createuser</command> and <command>psql</command>). Therefore it
126 is often convenient to maintain a naming correspondence between
127 roles and operating system users.
128 </para>
129
130 <para>
131 The set of database roles a given client connection can connect as
132 is determined by the client authentication setup, as explained in
133 <xref linkend="client-authentication"/>. (Thus, a client is not
134 limited to connect as the role matching
135 its operating system user, just as a person's login name
136 need not match his or her real name.) Since the role
137 identity determines the set of privileges available to a connected
138 client, it is important to carefully configure privileges when setting up
139 a multiuser environment.
140 </para>
141 </sect1>
142
143 <sect1 id="role-attributes">
144 <title>Role Attributes</title>
145
146 <para>
147 A database role can have a number of attributes that define its
148 privileges and interact with the client authentication system.
149
150 <variablelist>
151 <varlistentry>
152 <term>login privilege<indexterm><primary>login privilege</primary></indexterm></term>
153 <listitem>
154 <para>
155 Only roles that have the <literal>LOGIN</literal> attribute can be used
156 as the initial role name for a database connection. A role with
157 the <literal>LOGIN</literal> attribute can be considered the same
158 as a <quote>database user</quote>. To create a role with login privilege,
159 use either:
160 <programlisting>
161 CREATE ROLE <replaceable>name</replaceable> LOGIN;
162 CREATE USER <replaceable>name</replaceable>;
163 </programlisting>
164 (<command>CREATE USER</command> is equivalent to <command>CREATE ROLE</command>
165 except that <command>CREATE USER</command> includes <literal>LOGIN</literal> by
166 default, while <command>CREATE ROLE</command> does not.)
167 </para>
168 </listitem>
169 </varlistentry>
170
171 <varlistentry>
172 <term>superuser status<indexterm><primary>superuser</primary></indexterm></term>
173 <listitem>
174 <para>
175 A database superuser bypasses all permission checks, except the right
176 to log in. This is a dangerous privilege and should not be used
177 carelessly; it is best to do most of your work as a role that is not a
178 superuser. To create a new database superuser, use <literal>CREATE
179 ROLE <replaceable>name</replaceable> SUPERUSER</literal>. You must do
180 this as a role that is already a superuser.
181 </para>
182 </listitem>
183 </varlistentry>
184
185 <varlistentry>
186 <term>database creation<indexterm><primary>database</primary><secondary>privilege to create</secondary></indexterm></term>
187 <listitem>
188 <para>
189 A role must be explicitly given permission to create databases
190 (except for superusers, since those bypass all permission
191 checks). To create such a role, use <literal>CREATE ROLE
192 <replaceable>name</replaceable> CREATEDB</literal>.
193 </para>
194 </listitem>
195 </varlistentry>
196
197 <varlistentry>
198 <term id="role-creation">role creation<indexterm><primary>role</primary><secondary>privilege to create</secondary></indexterm></term>
199 <listitem>
200 <para>
201 A role must be explicitly given permission to create more roles
202 (except for superusers, since those bypass all permission
203 checks). To create such a role, use <literal>CREATE ROLE
204 <replaceable>name</replaceable> CREATEROLE</literal>.
205 A role with <literal>CREATEROLE</literal> privilege can alter and drop
206 roles which have been granted to the <literal>CREATEROLE</literal>
207 user with the <literal>ADMIN</literal> option. Such a grant occurs
208 automatically when a <literal>CREATEROLE</literal> user that is not
209 a superuser creates a new role, so that by default, a
210 <literal>CREATEROLE</literal> user can alter and drop the roles
211 which they have created.
212 Altering a role includes most changes that can be made using
213 <literal>ALTER ROLE</literal>, including, for example, changing
214 passwords. It also includes modifications to a role that can
215 be made using the <literal>COMMENT</literal> and
216 <literal>SECURITY LABEL</literal> commands.
217 </para>
218 <para>
219 However, <literal>CREATEROLE</literal> does not convey the ability to
220 create <literal>SUPERUSER</literal> roles, nor does it convey any
221 power over <literal>SUPERUSER</literal> roles that already exist.
222 Furthermore, <literal>CREATEROLE</literal> does not convey the power
223 to create <literal>REPLICATION</literal> users, nor the ability to
224 grant or revoke the <literal>REPLICATION</literal> privilege, nor the
225 ability to modify the role properties of such users. However, it does
226 allow <literal>ALTER ROLE ... SET</literal> and
227 <literal>ALTER ROLE ... RENAME</literal> to be used on
228 <literal>REPLICATION</literal> roles, as well as the use of
229 <literal>COMMENT ON ROLE</literal>,
230 <literal>SECURITY LABEL ON ROLE</literal>,
231 and <literal>DROP ROLE</literal>.
232 Finally, <literal>CREATEROLE</literal> does not
233 confer the ability to grant or revoke the <literal>BYPASSRLS</literal>
234 privilege.
235 </para>
236 </listitem>
237 </varlistentry>
238
239 <varlistentry>
240 <term>initiating replication<indexterm><primary>role</primary><secondary>privilege to initiate replication</secondary></indexterm></term>
241 <listitem>
242 <para>
243 A role must explicitly be given permission to initiate streaming
244 replication (except for superusers, since those bypass all permission
245 checks). A role used for streaming replication must
246 have <literal>LOGIN</literal> permission as well. To create such a role, use
247 <literal>CREATE ROLE <replaceable>name</replaceable> REPLICATION
248 LOGIN</literal>.
249 </para>
250 </listitem>
251 </varlistentry>
252
253 <varlistentry>
254 <term>password<indexterm><primary>password</primary></indexterm></term>
255 <listitem>
256 <para>
257 A password is only significant if the client authentication
258 method requires the user to supply a password when connecting
259 to the database. The <option>password</option> and
260 <option>md5</option> authentication methods
261 make use of passwords. Database passwords are separate from
262 operating system passwords. Specify a password upon role
263 creation with <literal>CREATE ROLE
264 <replaceable>name</replaceable> PASSWORD '<replaceable>string</replaceable>'</literal>.
265 </para>
266 </listitem>
267 </varlistentry>
268
269 <varlistentry>
270 <term>inheritance of privileges<indexterm><primary>role</primary><secondary>privilege to inherit</secondary></indexterm></term>
271 <listitem>
272 <para>
273 A role inherits the privileges of roles it is a member of, by default.
274 However, to create a role which does not inherit privileges by
275 default, use <literal>CREATE ROLE <replaceable>name</replaceable>
276 NOINHERIT</literal>. Alternatively, inheritance can be overridden
277 for individual grants by using <literal>WITH INHERIT TRUE</literal>
278 or <literal>WITH INHERIT FALSE</literal>.
279 </para>
280 </listitem>
281 </varlistentry>
282
283 <varlistentry>
284 <term>bypassing row-level security<indexterm><primary>role</primary><secondary>privilege to bypass</secondary></indexterm></term>
285 <listitem>
286 <para>
287 A role must be explicitly given permission to bypass every row-level security (RLS) policy
288 (except for superusers, since those bypass all permission checks).
289 To create such a role, use <literal>CREATE ROLE <replaceable>name</replaceable> BYPASSRLS</literal> as a superuser.
290 </para>
291 </listitem>
292 </varlistentry>
293
294 <varlistentry>
295 <term>connection limit<indexterm><primary>role</primary><secondary>privilege to limit connection</secondary></indexterm></term>
296 <listitem>
297 <para>
298 Connection limit can specify how many concurrent connections a role can make.
299 -1 (the default) means no limit. Specify connection limit upon role creation with
300 <literal>CREATE ROLE <replaceable>name</replaceable> CONNECTION LIMIT '<replaceable>integer</replaceable>'</literal>.
301 </para>
302 </listitem>
303 </varlistentry>
304 </variablelist>
305
306 A role's attributes can be modified after creation with
307 <command>ALTER ROLE</command>.<indexterm><primary>ALTER ROLE</primary></indexterm>
308 See the reference pages for the <xref linkend="sql-createrole"/>
309 and <xref linkend="sql-alterrole"/> commands for details.
310 </para>
311
312 <para>
313 A role can also have role-specific defaults for many of the run-time
314 configuration settings described in <xref
315 linkend="runtime-config"/>. For example, if for some reason you
316 want to disable index scans (hint: not a good idea) anytime you
317 connect, you can use:
318 <programlisting>
319 ALTER ROLE myname SET enable_indexscan TO off;
320 </programlisting>
321 This will save the setting (but not set it immediately). In
322 subsequent connections by this role it will appear as though
323 <literal>SET enable_indexscan TO off</literal> had been executed
324 just before the session started.
325 You can still alter this setting during the session; it will only
326 be the default. To remove a role-specific default setting, use
327 <literal>ALTER ROLE <replaceable>rolename</replaceable> RESET <replaceable>varname</replaceable></literal>.
328 Note that role-specific defaults attached to roles without
329 <literal>LOGIN</literal> privilege are fairly useless, since they will never
330 be invoked.
331 </para>
332
333 <para>
334 When a non-superuser creates a role using the <literal>CREATEROLE</literal>
335 privilege, the created role is automatically granted back to the creating
336 user, just as if the bootstrap superuser had executed the command
337 <literal>GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE,
338 INHERIT FALSE</literal>. Since a <literal>CREATEROLE</literal> user can
339 only exercise special privileges with regard to an existing role if they
340 have <literal>ADMIN OPTION</literal> on it, this grant is just sufficient
341 to allow a <literal>CREATEROLE</literal> user to administer the roles they
342 created. However, because it is created with <literal>INHERIT FALSE, SET
343 FALSE</literal>, the <literal>CREATEROLE</literal> user doesn't inherit the
344 privileges of the created role, nor can it access the privileges of that
345 role using <literal>SET ROLE</literal>. However, since any user who has
346 <literal>ADMIN OPTION</literal> on a role can grant membership in that
347 role to any other user, the <literal>CREATEROLE</literal> user can gain
348 access to the created role by simply granting that role back to
349 themselves with the <literal>INHERIT</literal> and/or <literal>SET</literal>
350 options. Thus, the fact that privileges are not inherited by default nor
351 is <literal>SET ROLE</literal> granted by default is a safeguard against
352 accidents, not a security feature. Also note that, because this automatic
353 grant is granted by the bootstrap superuser, it cannot be removed or changed by
354 the <literal>CREATEROLE</literal> user; however, any superuser could
355 revoke it, modify it, and/or issue additional such grants to other
356 <literal>CREATEROLE</literal> users. Whichever <literal>CREATEROLE</literal>
357 users have <literal>ADMIN OPTION</literal> on a role at any given time
358 can administer it.
359 </para>
360 </sect1>
361
362 <sect1 id="role-membership">
363 <title>Role Membership</title>
364
365 <indexterm zone="role-membership">
366 <primary>role</primary><secondary>membership in</secondary>
367 </indexterm>
368
369 <para>
370 It is frequently convenient to group users together to ease
371 management of privileges: that way, privileges can be granted to, or
372 revoked from, a group as a whole. In <productname>PostgreSQL</productname>
373 this is done by creating a role that represents the group, and then
374 granting <firstterm>membership</firstterm> in the group role to individual user
375 roles.
376 </para>
377
378 <para>
379 To set up a group role, first create the role:
380 <synopsis>
381 CREATE ROLE <replaceable>name</replaceable>;
382 </synopsis>
383 Typically a role being used as a group would not have the <literal>LOGIN</literal>
384 attribute, though you can set it if you wish.
385 </para>
386
387 <para>
388 Once the group role exists, you can add and remove members using the
389 <link linkend="sql-grant"><command>GRANT</command></link> and
390 <link linkend="sql-revoke"><command>REVOKE</command></link> commands:
391 <synopsis>
392 GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ;
393 REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ;
394 </synopsis>
395 You can grant membership to other group roles, too (since there isn't
396 really any distinction between group roles and non-group roles). The
397 database will not let you set up circular membership loops. Also,
398 it is not permitted to grant membership in a role to
399 <literal>PUBLIC</literal>.
400 </para>
401
402 <para>
403 The members of a group role can use the privileges of the role in two
404 ways. First, member roles that have been granted membership with the
405 <literal>SET</literal> option can do
406 <link linkend="sql-set-role"><command>SET ROLE</command></link> to
407 temporarily <quote>become</quote> the group role. In this state, the
408 database session has access to the privileges of the group role rather
409 than the original login role, and any database objects created are
410 considered owned by the group role, not the login role. Second, member
411 roles that have been granted membership with the
412 <literal>INHERIT</literal> option automatically have use of the
413 privileges of those directly or indirectly a member of, though the
414 chain stops at memberships lacking the inherit option. As an example,
415 suppose we have done:
416 <programlisting>
417 CREATE ROLE joe LOGIN;
418 CREATE ROLE admin;
419 CREATE ROLE wheel;
420 CREATE ROLE island;
421 GRANT admin TO joe WITH INHERIT TRUE;
422 GRANT wheel TO admin WITH INHERIT FALSE;
423 GRANT island TO joe WITH INHERIT TRUE, SET FALSE;
424 </programlisting>
425 Immediately after connecting as role <literal>joe</literal>, a database
426 session will have use of privileges granted directly to <literal>joe</literal>
427 plus any privileges granted to <literal>admin</literal> and
428 <literal>island</literal>, because <literal>joe</literal>
429 <quote>inherits</quote> those privileges. However, privileges
430 granted to <literal>wheel</literal> are not available, because even though
431 <literal>joe</literal> is indirectly a member of <literal>wheel</literal>, the
432 membership is via <literal>admin</literal> which was granted using
433 <literal>WITH INHERIT FALSE</literal>. After:
434 <programlisting>
435 SET ROLE admin;
436 </programlisting>
437 the session would have use of only those privileges granted to
438 <literal>admin</literal>, and not those granted to <literal>joe</literal> or
439 <literal>island</literal>. After:
440 <programlisting>
441 SET ROLE wheel;
442 </programlisting>
443 the session would have use of only those privileges granted to
444 <literal>wheel</literal>, and not those granted to either <literal>joe</literal>
445 or <literal>admin</literal>. The original privilege state can be restored
446 with any of:
447 <programlisting>
448 SET ROLE joe;
449 SET ROLE NONE;
450 RESET ROLE;
451 </programlisting>
452 </para>
453
454 <note>
455 <para>
456 The <command>SET ROLE</command> command always allows selecting any role
457 that the original login role is directly or indirectly a member of,
458 provided that there is a chain of membership grants each of which has
459 <literal>SET TRUE</literal> (which is the default).
460 Thus, in the above example, it is not necessary to become
461 <literal>admin</literal> before becoming <literal>wheel</literal>.
462 On the other hand, it is not possible to become <literal>island</literal>
463 at all; <literal>joe</literal> can only access those privileges via
464 inheritance.
465 </para>
466 </note>
467
468 <note>
469 <para>
470 In the SQL standard, there is a clear distinction between users and roles,
471 and users do not automatically inherit privileges while roles do. This
472 behavior can be obtained in <productname>PostgreSQL</productname> by giving
473 roles being used as SQL roles the <literal>INHERIT</literal> attribute, while
474 giving roles being used as SQL users the <literal>NOINHERIT</literal> attribute.
475 However, <productname>PostgreSQL</productname> defaults to giving all roles
476 the <literal>INHERIT</literal> attribute, for backward compatibility with pre-8.1
477 releases in which users always had use of permissions granted to groups
478 they were members of.
479 </para>
480 </note>
481
482 <para>
483 The role attributes <literal>LOGIN</literal>, <literal>SUPERUSER</literal>,
484 <literal>CREATEDB</literal>, and <literal>CREATEROLE</literal> can be thought of as
485 special privileges, but they are never inherited as ordinary privileges
486 on database objects are. You must actually <command>SET ROLE</command> to a
487 specific role having one of these attributes in order to make use of
488 the attribute. Continuing the above example, we might choose to
489 grant <literal>CREATEDB</literal> and <literal>CREATEROLE</literal> to the
490 <literal>admin</literal> role. Then a session connecting as role <literal>joe</literal>
491 would not have these privileges immediately, only after doing
492 <command>SET ROLE admin</command>.
493 </para>
494
495 <para>
496 </para>
497
498 <para>
499 To destroy a group role, use <link
500 linkend="sql-droprole"><command>DROP ROLE</command></link>:
501 <synopsis>
502 DROP ROLE <replaceable>name</replaceable>;
503 </synopsis>
504 Any memberships in the group role are automatically revoked (but the
505 member roles are not otherwise affected).
506 </para>
507 </sect1>
508
509 <sect1 id="role-removal">
510 <title>Dropping Roles</title>
511
512 <para>
513 Because roles can own database objects and can hold privileges
514 to access other objects, dropping a role is often not just a matter of a
515 quick <link linkend="sql-droprole"><command>DROP ROLE</command></link>. Any objects owned by the role must
516 first be dropped or reassigned to other owners; and any permissions
517 granted to the role must be revoked.
518 </para>
519
520 <para>
521 Ownership of objects can be transferred one at a time
522 using <command>ALTER</command> commands, for example:
523 <programlisting>
524 ALTER TABLE bobs_table OWNER TO alice;
525 </programlisting>
526 Alternatively, the <link linkend="sql-reassign-owned"><command>REASSIGN OWNED</command></link> command can be
527 used to reassign ownership of all objects owned by the role-to-be-dropped
528 to a single other role. Because <command>REASSIGN OWNED</command> cannot access
529 objects in other databases, it is necessary to run it in each database
530 that contains objects owned by the role. (Note that the first
531 such <command>REASSIGN OWNED</command> will change the ownership of any
532 shared-across-databases objects, that is databases or tablespaces, that
533 are owned by the role-to-be-dropped.)
534 </para>
535
536 <para>
537 Once any valuable objects have been transferred to new owners, any
538 remaining objects owned by the role-to-be-dropped can be dropped with
539 the <link linkend="sql-drop-owned"><command>DROP OWNED</command></link> command. Again, this command cannot
540 access objects in other databases, so it is necessary to run it in each
541 database that contains objects owned by the role. Also, <command>DROP
542 OWNED</command> will not drop entire databases or tablespaces, so it is
543 necessary to do that manually if the role owns any databases or
544 tablespaces that have not been transferred to new owners.
545 </para>
546
547 <para>
548 <command>DROP OWNED</command> also takes care of removing any privileges granted
549 to the target role for objects that do not belong to it.
550 Because <command>REASSIGN OWNED</command> does not touch such objects, it's
551 typically necessary to run both <command>REASSIGN OWNED</command>
552 and <command>DROP OWNED</command> (in that order!) to fully remove the
553 dependencies of a role to be dropped.
554 </para>
555
556 <para>
557 In short then, the most general recipe for removing a role that has been
558 used to own objects is:
559 </para>
560 <programlisting>
561 REASSIGN OWNED BY doomed_role TO successor_role;
562 DROP OWNED BY doomed_role;
563 -- repeat the above commands in each database of the cluster
564 DROP ROLE doomed_role;
565 </programlisting>
566
567 <para>
568 When not all owned objects are to be transferred to the same successor
569 owner, it's best to handle the exceptions manually and then perform
570 the above steps to mop up.
571 </para>
572
573 <para>
574 If <command>DROP ROLE</command> is attempted while dependent objects still
575 remain, it will issue messages identifying which objects need to be
576 reassigned or dropped.
577 </para>
578 </sect1>
579
580 <sect1 id="predefined-roles">
581 <title>Predefined Roles</title>
582
583 <indexterm zone="predefined-roles">
584 <primary>role</primary>
585 </indexterm>
586
587 <para>
588 <productname>PostgreSQL</productname> provides a set of predefined roles
589 that provide access to certain, commonly needed, privileged capabilities
590 and information. Administrators (including roles that have the
591 <literal>CREATEROLE</literal> privilege) can <command>GRANT</command> these
592 roles to users and/or other roles in their environment, providing those
593 users with access to the specified capabilities and information. For
594 example:
595
596 <programlisting>
597 GRANT pg_signal_backend TO admin_user;
598 </programlisting>
599 </para>
600
601 <warning>
602 <para>
603 Care should be taken when granting these roles to ensure they are only used
604 where needed and with the understanding that these roles grant access to
605 privileged information.
606 </para>
607 </warning>
608
609 <para>
610 The predefined roles are described below.
611 Note that the specific permissions for each of the roles may change in
612 the future as additional capabilities are added. Administrators
613 should monitor the release notes for changes.
614
615 <variablelist>
616 <varlistentry id="predefined-role-pg-checkpoint" xreflabel="pg_checkpoint">
617 <term><varname>pg_checkpoint</varname></term>
618 <listitem>
619 <para>
620 <literal>pg_checkpoint</literal> allows executing the
621 <link linkend="sql-checkpoint"><command>CHECKPOINT</command></link> command.
622 </para>
623 </listitem>
624 </varlistentry>
625
626 <varlistentry id="predefined-role-pg-create-subscription" xreflabel="pg_create_subscription">
627 <term><varname>pg_create_subscription</varname></term>
628 <listitem>
629 <para>
630 <literal>pg_create_subscription</literal> allows users with
631 <literal>CREATE</literal> permission on the database to issue
632 <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.
633 </para>
634 </listitem>
635 </varlistentry>
636
637 <varlistentry id="predefined-role-pg-database-owner" xreflabel="pg_database_owner">
638 <term><varname>pg_database_owner</varname></term>
639 <listitem>
640 <para>
641 <literal>pg_database_owner</literal> always has exactly one implicit
642 member: the current database owner. It cannot be granted membership in
643 any role, and no role can be granted membership in
644 <literal>pg_database_owner</literal>. However, like any other role, it
645 can own objects and receive grants of access privileges. Consequently,
646 once <literal>pg_database_owner</literal> has rights within a template
647 database, each owner of a database instantiated from that template will
648 possess those rights. Initially, this role owns the
649 <literal>public</literal> schema, so each database owner governs local
650 use of that schema.
651 </para>
652 </listitem>
653 </varlistentry>
654
655 <varlistentry id="predefined-role-pg-maintain" xreflabel="pg_maintain">
656 <term><varname>pg_maintain</varname></term>
657 <listitem>
658 <para>
659 <literal>pg_maintain</literal> allows executing
660 <link linkend="sql-vacuum"><command>VACUUM</command></link>,
661 <link linkend="sql-analyze"><command>ANALYZE</command></link>,
662 <link linkend="sql-cluster"><command>CLUSTER</command></link>,
663 <link linkend="sql-refreshmaterializedview"><command>REFRESH MATERIALIZED VIEW</command></link>,
664 <link linkend="sql-reindex"><command>REINDEX</command></link>,
665 and <link linkend="sql-lock"><command>LOCK TABLE</command></link> on all
666 relations, as if having <literal>MAINTAIN</literal> rights on those
667 objects.
668 </para>
669 </listitem>
670 </varlistentry>
671
672 <varlistentry id="predefined-role-pg-monitor" xreflabel="pg_monitor">
673 <term><varname>pg_monitor</varname></term>
674 <term><varname>pg_read_all_settings</varname></term>
675 <term><varname>pg_read_all_stats</varname></term>
676 <term><varname>pg_stat_scan_tables</varname></term>
677 <listitem>
678 <para>
679 These roles are intended to allow administrators to easily configure a
680 role for the purpose of monitoring the database server. They grant a
681 set of common privileges allowing the role to read various useful
682 configuration settings, statistics, and other system information
683 normally restricted to superusers.
684 </para>
685 <para>
686 <literal>pg_monitor</literal> allows reading/executing various
687 monitoring views and functions. This role is a member of
688 <literal>pg_read_all_settings</literal>,
689 <literal>pg_read_all_stats</literal> and
690 <literal>pg_stat_scan_tables</literal>.
691 </para>
692 <para>
693 <literal>pg_read_all_settings</literal> allows reading all configuration
694 variables, even those normally visible only to superusers.
695 </para>
696 <para>
697 <literal>pg_read_all_stats</literal> allows reading all pg_stat_* views
698 and use various statistics related extensions, even those normally
699 visible only to superusers.
700 </para>
701 <para>
702 <literal>pg_stat_scan_tables</literal> allows executing monitoring
703 functions that may take <literal>ACCESS SHARE</literal> locks on tables,
704 potentially for a long time (e.g., <function>pgrowlocks(text)</function>
705 in the <xref linkend="pgrowlocks"/> extension).
706 </para>
707 </listitem>
708 </varlistentry>
709
710 <varlistentry id="predefined-role-pg-read-all-data" xreflabel="pg_read_all_data">
711 <term><varname>pg_read_all_data</varname></term>
712 <term><varname>pg_write_all_data</varname></term>
713 <listitem>
714 <para>
715 <literal>pg_read_all_data</literal> allows reading all data (tables,
716 views, sequences), as if having <command>SELECT</command> rights on
717 those objects and <literal>USAGE</literal> rights on all schemas. This
718 role does not bypass row-level security (RLS) policies. If RLS is being
719 used, an administrator may wish to set <literal>BYPASSRLS</literal> on
720 roles which this role is granted to.
721 </para>
722 <para>
723 <literal>pg_write_all_data</literal> allows writing all data (tables,
724 views, sequences), as if having <command>INSERT</command>,
725 <command>UPDATE</command>, and <command>DELETE</command> rights on those
726 objects and <literal>USAGE</literal> rights on all schemas. This role
727 does not bypass row-level security (RLS) policies. If RLS is being
728 used, an administrator may wish to set <literal>BYPASSRLS</literal> on
729 roles which this role is granted to.
730 </para>
731 </listitem>
732 </varlistentry>
733
734 <varlistentry id="predefined-role-pg-read-server-files" xreflabel="pg_read_server_files">
735 <term><varname>pg_read_server_files</varname></term>
736 <term><varname>pg_write_server_files</varname></term>
737 <term><varname>pg_execute_server_program</varname></term>
738 <listitem>
739 <para>
740 These roles are intended to allow administrators to have trusted, but
741 non-superuser, roles which are able to access files and run programs on
742 the database server as the user the database runs as. They bypass all
743 database-level permission checks when accessing files directly and they
744 could be used to gain superuser-level access. Therefore, great care
745 should be taken when granting these roles to users.
746 </para>
747 <para>
748 <literal>pg_read_server_files</literal> allows reading files from any
749 location the database can access on the server using
750 <command>COPY</command> and other file-access functions.
751 </para>
752 <para>
753 <literal>pg_write_server_files</literal> allows writing to files in any
754 location the database can access on the server using
755 <command>COPY</command> and other file-access functions.
756 </para>
757 <para>
758 <literal>pg_execute_server_program</literal> allows executing programs
759 on the database server as the user the database runs as using
760 <command>COPY</command> and other functions which allow executing a
761 server-side program.
762 </para>
763 </listitem>
764 </varlistentry>
765
766 <varlistentry id="predefined-role-pg-signal-autovacuum-worker" xreflabel="pg_signal_autovacuum_worker">
767 <term><varname>pg_signal_autovacuum_worker</varname></term>
768 <listitem>
769 <para>
770 <literal>pg_signal_autovacuum_worker</literal> allows signaling
771 autovacuum workers to cancel the current table's vacuum or terminate its
772 session. See <xref linkend="functions-admin-signal"/>.
773 </para>
774 </listitem>
775 </varlistentry>
776
777 <varlistentry id="predefined-role-pg-signal-backend" xreflabel="pg_signal_backend">
778 <term><varname>pg_signal_backend</varname></term>
779 <listitem>
780 <para>
781 <literal>pg_signal_backend</literal> allows signaling another backend to
782 cancel a query or terminate its session. Note that this role does not
783 permit signaling backends owned by a superuser. See
784 <xref linkend="functions-admin-signal"/>.
785 </para>
786 </listitem>
787 </varlistentry>
788
789 <varlistentry id="predefined-role-pg-use-reserved-connections" xreflabel="pg_use_reserved_connections">
790 <term><varname>pg_use_reserved_connections</varname></term>
791 <listitem>
792 <para>
793 <literal>pg_use_reserved_connections</literal> allows use of connection
794 slots reserved via <xref linkend="guc-reserved-connections"/>.
795 </para>
796 </listitem>
797 </varlistentry>
798 </variablelist>
799 </para>
800
801 </sect1>
802
803 <sect1 id="perm-functions">
804 <title>Function Security</title>
805
806 <para>
807 Functions, triggers and row-level security policies allow users to insert
808 code into the backend server that other users might execute
809 unintentionally. Hence, these mechanisms permit users to <quote>Trojan
810 horse</quote> others with relative ease. The strongest protection is tight
811 control over who can define objects. Where that is infeasible, write
812 queries referring only to objects having trusted owners. Remove
813 from <varname>search_path</varname> any schemas that permit untrusted users
814 to create objects.
815 </para>
816
817 <para>
818 Functions run inside the backend
819 server process with the operating system permissions of the
820 database server daemon. If the programming language
821 used for the function allows unchecked memory accesses, it is
822 possible to change the server's internal data structures.
823 Hence, among many other things, such functions can circumvent any
824 system access controls. Function languages that allow such access
825 are considered <quote>untrusted</quote>, and
826 <productname>PostgreSQL</productname> allows only superusers to
827 create functions written in those languages.
828 </para>
829 </sect1>
830
831 </chapter>