1 <!-- doc/src/sgml/user-manag.sgml -->
3 <chapter id="user-manag">
4 <title>Database Roles</title>
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.
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.
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"/>.
30 <sect1 id="database-roles">
31 <title>Database Roles</title>
33 <indexterm zone="database-roles">
34 <primary>role</primary>
37 <indexterm zone="database-roles">
38 <primary>user</primary>
42 <primary>CREATE ROLE</primary>
46 <primary>DROP ROLE</primary>
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 <xref
55 linkend="sql-createrole"/> SQL command:
57 CREATE ROLE <replaceable>name</replaceable>;
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 <xref linkend="sql-droprole"/> command:
66 DROP ROLE <replaceable>name</replaceable>;
71 <primary>createuser</primary>
75 <primary>dropuser</primary>
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
84 createuser <replaceable>name</replaceable>
85 dropuser <replaceable>name</replaceable>
90 To determine the set of existing roles, examine the <structname>pg_roles</structname>
91 system catalog, for example
93 SELECT rolname FROM pg_roles;
95 The <xref linkend="app-psql"/> program's <literal>\du</literal> meta-command
96 is also useful for listing the existing roles.
100 In order to bootstrap the database system, a freshly initialized
101 system always contains one predefined role. This role is always
102 a <quote>superuser</quote>, and by default (unless altered when running
103 <command>initdb</command>) it will have the same name as the
104 operating system user that initialized the database
105 cluster. Customarily, this role will be named
106 <literal>postgres</literal>. In order to create more roles you
107 first have to connect as this initial role.
111 Every connection to the database server is made using the name of some
112 particular role, and this role determines the initial access privileges for
113 commands issued in that connection.
114 The role name to use for a particular database
115 connection is indicated by the client that is initiating the
116 connection request in an application-specific fashion. For example,
117 the <command>psql</command> program uses the
118 <option>-U</option> command line option to indicate the role to
119 connect as. Many applications assume the name of the current
120 operating system user by default (including
121 <command>createuser</command> and <command>psql</command>). Therefore it
122 is often convenient to maintain a naming correspondence between
123 roles and operating system users.
127 The set of database roles a given client connection can connect as
128 is determined by the client authentication setup, as explained in
129 <xref linkend="client-authentication"/>. (Thus, a client is not
130 limited to connect as the role matching
131 its operating system user, just as a person's login name
132 need not match his or her real name.) Since the role
133 identity determines the set of privileges available to a connected
134 client, it is important to carefully configure privileges when setting up
135 a multiuser environment.
139 <sect1 id="role-attributes">
140 <title>Role Attributes</title>
143 A database role can have a number of attributes that define its
144 privileges and interact with the client authentication system.
148 <term>login privilege<indexterm><primary>login privilege</primary></indexterm></term>
151 Only roles that have the <literal>LOGIN</literal> attribute can be used
152 as the initial role name for a database connection. A role with
153 the <literal>LOGIN</literal> attribute can be considered the same
154 as a <quote>database user</quote>. To create a role with login privilege,
157 CREATE ROLE <replaceable>name</replaceable> LOGIN;
158 CREATE USER <replaceable>name</replaceable>;
160 (<command>CREATE USER</command> is equivalent to <command>CREATE ROLE</command>
161 except that <command>CREATE USER</command> includes <literal>LOGIN</literal> by
162 default, while <command>CREATE ROLE</command> does not.)
168 <term>superuser status<indexterm><primary>superuser</primary></indexterm></term>
171 A database superuser bypasses all permission checks, except the right
172 to log in. This is a dangerous privilege and should not be used
173 carelessly; it is best to do most of your work as a role that is not a
174 superuser. To create a new database superuser, use <literal>CREATE
175 ROLE <replaceable>name</replaceable> SUPERUSER</literal>. You must do
176 this as a role that is already a superuser.
182 <term>database creation<indexterm><primary>database</primary><secondary>privilege to create</secondary></indexterm></term>
185 A role must be explicitly given permission to create databases
186 (except for superusers, since those bypass all permission
187 checks). To create such a role, use <literal>CREATE ROLE
188 <replaceable>name</replaceable> CREATEDB</literal>.
194 <term>role creation<indexterm><primary>role</primary><secondary>privilege to create</secondary></indexterm></term>
197 A role must be explicitly given permission to create more roles
198 (except for superusers, since those bypass all permission
199 checks). To create such a role, use <literal>CREATE ROLE
200 <replaceable>name</replaceable> CREATEROLE</literal>.
201 A role with <literal>CREATEROLE</literal> privilege can alter and drop
202 other roles, too, as well as grant or revoke membership in them.
203 However, to create, alter, drop, or change membership of a
204 superuser role, superuser status is required;
205 <literal>CREATEROLE</literal> is insufficient for that.
211 <term>initiating replication<indexterm><primary>role</primary><secondary>privilege to initiate replication</secondary></indexterm></term>
214 A role must explicitly be given permission to initiate streaming
215 replication (except for superusers, since those bypass all permission
216 checks). A role used for streaming replication must
217 have <literal>LOGIN</literal> permission as well. To create such a role, use
218 <literal>CREATE ROLE <replaceable>name</replaceable> REPLICATION
225 <term>password<indexterm><primary>password</primary></indexterm></term>
228 A password is only significant if the client authentication
229 method requires the user to supply a password when connecting
230 to the database. The <option>password</option> and
231 <option>md5</option> authentication methods
232 make use of passwords. Database passwords are separate from
233 operating system passwords. Specify a password upon role
234 creation with <literal>CREATE ROLE
235 <replaceable>name</replaceable> PASSWORD '<replaceable>string</replaceable>'</literal>.
241 A role's attributes can be modified after creation with
242 <command>ALTER ROLE</command>.<indexterm><primary>ALTER ROLE</primary></indexterm>
243 See the reference pages for the <xref linkend="sql-createrole"/>
244 and <xref linkend="sql-alterrole"/> commands for details.
249 It is good practice to create a role that has the <literal>CREATEDB</literal>
250 and <literal>CREATEROLE</literal> privileges, but is not a superuser, and then
251 use this role for all routine management of databases and roles. This
252 approach avoids the dangers of operating as a superuser for tasks that
253 do not really require it.
258 A role can also have role-specific defaults for many of the run-time
259 configuration settings described in <xref
260 linkend="runtime-config"/>. For example, if for some reason you
261 want to disable index scans (hint: not a good idea) anytime you
262 connect, you can use:
264 ALTER ROLE myname SET enable_indexscan TO off;
266 This will save the setting (but not set it immediately). In
267 subsequent connections by this role it will appear as though
268 <literal>SET enable_indexscan TO off</literal> had been executed
269 just before the session started.
270 You can still alter this setting during the session; it will only
271 be the default. To remove a role-specific default setting, use
272 <literal>ALTER ROLE <replaceable>rolename</replaceable> RESET <replaceable>varname</replaceable></literal>.
273 Note that role-specific defaults attached to roles without
274 <literal>LOGIN</literal> privilege are fairly useless, since they will never
279 <sect1 id="role-membership">
280 <title>Role Membership</title>
282 <indexterm zone="role-membership">
283 <primary>role</primary><secondary>membership in</secondary>
287 It is frequently convenient to group users together to ease
288 management of privileges: that way, privileges can be granted to, or
289 revoked from, a group as a whole. In <productname>PostgreSQL</productname>
290 this is done by creating a role that represents the group, and then
291 granting <firstterm>membership</firstterm> in the group role to individual user
296 To set up a group role, first create the role:
298 CREATE ROLE <replaceable>name</replaceable>;
300 Typically a role being used as a group would not have the <literal>LOGIN</literal>
301 attribute, though you can set it if you wish.
305 Once the group role exists, you can add and remove members using the
306 <xref linkend="sql-grant"/> and
307 <xref linkend="sql-revoke"/> commands:
309 GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ;
310 REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ;
312 You can grant membership to other group roles, too (since there isn't
313 really any distinction between group roles and non-group roles). The
314 database will not let you set up circular membership loops. Also,
315 it is not permitted to grant membership in a role to
316 <literal>PUBLIC</literal>.
320 The members of a group role can use the privileges of the role in two
321 ways. First, every member of a group can explicitly do
322 <xref linkend="sql-set-role"/> to
323 temporarily <quote>become</quote> the group role. In this state, the
324 database session has access to the privileges of the group role rather
325 than the original login role, and any database objects created are
326 considered owned by the group role not the login role. Second, member
327 roles that have the <literal>INHERIT</literal> attribute automatically have use
328 of the privileges of roles of which they are members, including any
329 privileges inherited by those roles.
330 As an example, suppose we have done:
332 CREATE ROLE joe LOGIN INHERIT;
333 CREATE ROLE admin NOINHERIT;
334 CREATE ROLE wheel NOINHERIT;
336 GRANT wheel TO admin;
338 Immediately after connecting as role <literal>joe</literal>, a database
339 session will have use of privileges granted directly to <literal>joe</literal>
340 plus any privileges granted to <literal>admin</literal>, because <literal>joe</literal>
341 <quote>inherits</quote> <literal>admin</literal>'s privileges. However, privileges
342 granted to <literal>wheel</literal> are not available, because even though
343 <literal>joe</literal> is indirectly a member of <literal>wheel</literal>, the
344 membership is via <literal>admin</literal> which has the <literal>NOINHERIT</literal>
349 the session would have use of only those privileges granted to
350 <literal>admin</literal>, and not those granted to <literal>joe</literal>. After:
354 the session would have use of only those privileges granted to
355 <literal>wheel</literal>, and not those granted to either <literal>joe</literal>
356 or <literal>admin</literal>. The original privilege state can be restored
367 The <command>SET ROLE</command> command always allows selecting any role
368 that the original login role is directly or indirectly a member of.
369 Thus, in the above example, it is not necessary to become
370 <literal>admin</literal> before becoming <literal>wheel</literal>.
376 In the SQL standard, there is a clear distinction between users and roles,
377 and users do not automatically inherit privileges while roles do. This
378 behavior can be obtained in <productname>PostgreSQL</productname> by giving
379 roles being used as SQL roles the <literal>INHERIT</literal> attribute, while
380 giving roles being used as SQL users the <literal>NOINHERIT</literal> attribute.
381 However, <productname>PostgreSQL</productname> defaults to giving all roles
382 the <literal>INHERIT</literal> attribute, for backward compatibility with pre-8.1
383 releases in which users always had use of permissions granted to groups
384 they were members of.
389 The role attributes <literal>LOGIN</literal>, <literal>SUPERUSER</literal>,
390 <literal>CREATEDB</literal>, and <literal>CREATEROLE</literal> can be thought of as
391 special privileges, but they are never inherited as ordinary privileges
392 on database objects are. You must actually <command>SET ROLE</command> to a
393 specific role having one of these attributes in order to make use of
394 the attribute. Continuing the above example, we might choose to
395 grant <literal>CREATEDB</literal> and <literal>CREATEROLE</literal> to the
396 <literal>admin</literal> role. Then a session connecting as role <literal>joe</literal>
397 would not have these privileges immediately, only after doing
398 <command>SET ROLE admin</command>.
405 To destroy a group role, use <xref
406 linkend="sql-droprole"/>:
408 DROP ROLE <replaceable>name</replaceable>;
410 Any memberships in the group role are automatically revoked (but the
411 member roles are not otherwise affected).
415 <sect1 id="role-removal">
416 <title>Dropping Roles</title>
419 Because roles can own database objects and can hold privileges
420 to access other objects, dropping a role is often not just a matter of a
421 quick <xref linkend="sql-droprole"/>. Any objects owned by the role must
422 first be dropped or reassigned to other owners; and any permissions
423 granted to the role must be revoked.
427 Ownership of objects can be transferred one at a time
428 using <command>ALTER</command> commands, for example:
430 ALTER TABLE bobs_table OWNER TO alice;
432 Alternatively, the <xref linkend="sql-reassign-owned"/> command can be
433 used to reassign ownership of all objects owned by the role-to-be-dropped
434 to a single other role. Because <command>REASSIGN OWNED</command> cannot access
435 objects in other databases, it is necessary to run it in each database
436 that contains objects owned by the role. (Note that the first
437 such <command>REASSIGN OWNED</command> will change the ownership of any
438 shared-across-databases objects, that is databases or tablespaces, that
439 are owned by the role-to-be-dropped.)
443 Once any valuable objects have been transferred to new owners, any
444 remaining objects owned by the role-to-be-dropped can be dropped with
445 the <xref linkend="sql-drop-owned"/> command. Again, this command cannot
446 access objects in other databases, so it is necessary to run it in each
447 database that contains objects owned by the role. Also, <command>DROP
448 OWNED</command> will not drop entire databases or tablespaces, so it is
449 necessary to do that manually if the role owns any databases or
450 tablespaces that have not been transferred to new owners.
454 <command>DROP OWNED</command> also takes care of removing any privileges granted
455 to the target role for objects that do not belong to it.
456 Because <command>REASSIGN OWNED</command> does not touch such objects, it's
457 typically necessary to run both <command>REASSIGN OWNED</command>
458 and <command>DROP OWNED</command> (in that order!) to fully remove the
459 dependencies of a role to be dropped.
463 In short then, the most general recipe for removing a role that has been
464 used to own objects is:
467 REASSIGN OWNED BY doomed_role TO successor_role;
468 DROP OWNED BY doomed_role;
469 -- repeat the above commands in each database of the cluster
470 DROP ROLE doomed_role;
474 When not all owned objects are to be transferred to the same successor
475 owner, it's best to handle the exceptions manually and then perform
476 the above steps to mop up.
480 If <command>DROP ROLE</command> is attempted while dependent objects still
481 remain, it will issue messages identifying which objects need to be
482 reassigned or dropped.
486 <sect1 id="default-roles">
487 <title>Default Roles</title>
489 <indexterm zone="default-roles">
490 <primary>role</primary>
494 <productname>PostgreSQL</productname> provides a set of default roles
495 which provide access to certain, commonly needed, privileged capabilities
496 and information. Administrators can GRANT these roles to users and/or
497 other roles in their environment, providing those users with access to
498 the specified capabilities and information.
502 The default roles are described in <xref linkend="default-roles-table"/>.
503 Note that the specific permissions for each of the default roles may
504 change in the future as additional capabilities are added. Administrators
505 should monitor the release notes for changes.
508 <table tocentry="1" id="default-roles-table">
509 <title>Default Roles</title>
514 <entry>Allowed Access</entry>
519 <entry>pg_read_all_settings</entry>
520 <entry>Read all configuration variables, even those normally visible only to
524 <entry>pg_read_all_stats</entry>
525 <entry>Read all pg_stat_* views and use various statistics related extensions,
526 even those normally visible only to superusers.</entry>
529 <entry>pg_stat_scan_tables</entry>
530 <entry>Execute monitoring functions that may take <literal>ACCESS SHARE</literal> locks on tables,
531 potentially for a long time.</entry>
534 <entry>pg_monitor</entry>
535 <entry>Read/execute various monitoring views and functions.
536 This role is a member of <literal>pg_read_all_settings</literal>,
537 <literal>pg_read_all_stats</literal> and
538 <literal>pg_stat_scan_tables</literal>.</entry>
541 <entry>pg_signal_backend</entry>
542 <entry>Signal another backend to cancel a query or terminate its session.</entry>
545 <entry>pg_read_server_files</entry>
546 <entry>Allow reading files from any location the database can access on the server with COPY and
547 other file-access functions.</entry>
550 <entry>pg_write_server_files</entry>
551 <entry>Allow writing to files in any location the database can access on the server with COPY and
552 other file-access functions.</entry>
555 <entry>pg_execute_server_program</entry>
556 <entry>Allow executing programs on the database server as the user the database runs as with
557 COPY and other functions which allow executing a server-side program.</entry>
564 The <literal>pg_monitor</literal>, <literal>pg_read_all_settings</literal>,
565 <literal>pg_read_all_stats</literal> and <literal>pg_stat_scan_tables</literal>
566 roles are intended to allow administrators to easily configure a role for the
567 purpose of monitoring the database server. They grant a set of common privileges
568 allowing the role to read various useful configuration settings, statistics and
569 other system information normally restricted to superusers.
573 The <literal>pg_signal_backend</literal> role is intended to allow
574 administrators to enable trusted, but non-superuser, roles to send signals
575 to other backends. Currently this role enables sending of signals for
576 canceling a query on another backend or terminating its session. A user
577 granted this role cannot however send signals to a backend owned by a
578 superuser. See <xref linkend="functions-admin-signal"/>.
582 The <literal>pg_read_server_files</literal>, <literal>pg_write_server_files</literal> and
583 <literal>pg_execute_server_program</literal> roles are intended to allow administrators to have
584 trusted, but non-superuser, roles which are able to access files and run programs on the
585 database server as the user the database runs as. As these roles are able to access any file on
586 the server file system, they bypass all database-level permission checks when accessing files
587 directly and they could be used to gain superuser-level access, therefore
588 great care should be taken when granting these roles to users.
592 Care should be taken when granting these roles to ensure they are only used where
593 needed and with the understanding that these roles grant access to privileged
598 Administrators can grant access to these roles to users using the
599 <xref linkend="sql-grant"/> command, for example:
602 GRANT pg_signal_backend TO admin_user;
608 <sect1 id="perm-functions">
609 <title>Function Security</title>
612 Functions, triggers and row-level security policies allow users to insert
613 code into the backend server that other users might execute
614 unintentionally. Hence, these mechanisms permit users to <quote>Trojan
615 horse</quote> others with relative ease. The strongest protection is tight
616 control over who can define objects. Where that is infeasible, write
617 queries referring only to objects having trusted owners. Remove
618 from <varname>search_path</varname> the public schema and any other schemas
619 that permit untrusted users to create objects.
623 Functions run inside the backend
624 server process with the operating system permissions of the
625 database server daemon. If the programming language
626 used for the function allows unchecked memory accesses, it is
627 possible to change the server's internal data structures.
628 Hence, among many other things, such functions can circumvent any
629 system access controls. Function languages that allow such access
630 are considered <quote>untrusted</quote>, and
631 <productname>PostgreSQL</productname> allows only superusers to
632 create functions written in those languages.