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 <xref
55   linkend="sql-createrole"/> SQL command:
56<synopsis>
57CREATE 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   <xref linkend="sql-droprole"/> command:
65<synopsis>
66DROP 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>
84createuser <replaceable>name</replaceable>
85dropuser <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>
93SELECT rolname FROM pg_roles;
94</synopsis>
95   The <xref linkend="app-psql"/> program's <literal>\du</literal> meta-command
96   is also useful for listing the existing roles.
97  </para>
98
99  <para>
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.
108  </para>
109
110  <para>
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.
124  </para>
125
126  <para>
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.
136  </para>
137 </sect1>
138
139 <sect1 id="role-attributes">
140  <title>Role Attributes</title>
141
142   <para>
143    A database role can have a number of attributes that define its
144    privileges and interact with the client authentication system.
145
146    <variablelist>
147     <varlistentry>
148      <term>login privilege<indexterm><primary>login privilege</primary></indexterm></term>
149      <listitem>
150       <para>
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,
155        use either:
156<programlisting>
157CREATE ROLE <replaceable>name</replaceable> LOGIN;
158CREATE USER <replaceable>name</replaceable>;
159</programlisting>
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.)
163       </para>
164      </listitem>
165     </varlistentry>
166
167     <varlistentry>
168      <term>superuser status<indexterm><primary>superuser</primary></indexterm></term>
169      <listitem>
170       <para>
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.
177       </para>
178      </listitem>
179     </varlistentry>
180
181     <varlistentry>
182      <term>database creation<indexterm><primary>database</primary><secondary>privilege to create</secondary></indexterm></term>
183      <listitem>
184       <para>
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>.
189       </para>
190      </listitem>
191     </varlistentry>
192
193     <varlistentry>
194      <term>role creation<indexterm><primary>role</primary><secondary>privilege to create</secondary></indexterm></term>
195      <listitem>
196       <para>
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.
206       </para>
207      </listitem>
208     </varlistentry>
209
210     <varlistentry>
211      <term>initiating replication<indexterm><primary>role</primary><secondary>privilege to initiate replication</secondary></indexterm></term>
212      <listitem>
213       <para>
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
219        LOGIN</literal>.
220       </para>
221      </listitem>
222     </varlistentry>
223
224     <varlistentry>
225      <term>password<indexterm><primary>password</primary></indexterm></term>
226      <listitem>
227       <para>
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>.
236       </para>
237      </listitem>
238     </varlistentry>
239    </variablelist>
240
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.
245   </para>
246
247  <tip>
248   <para>
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.
254   </para>
255  </tip>
256
257  <para>
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:
263<programlisting>
264ALTER ROLE myname SET enable_indexscan TO off;
265</programlisting>
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
275   be invoked.
276  </para>
277 </sect1>
278
279 <sect1 id="role-membership">
280  <title>Role Membership</title>
281
282  <indexterm zone="role-membership">
283   <primary>role</primary><secondary>membership in</secondary>
284  </indexterm>
285
286  <para>
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
292   roles.
293  </para>
294
295  <para>
296   To set up a group role, first create the role:
297<synopsis>
298CREATE ROLE <replaceable>name</replaceable>;
299</synopsis>
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.
302  </para>
303
304  <para>
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:
308<synopsis>
309GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ;
310REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ;
311</synopsis>
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>.
317  </para>
318
319  <para>
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:
331<programlisting>
332CREATE ROLE joe LOGIN INHERIT;
333CREATE ROLE admin NOINHERIT;
334CREATE ROLE wheel NOINHERIT;
335GRANT admin TO joe;
336GRANT wheel TO admin;
337</programlisting>
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>
345   attribute.  After:
346<programlisting>
347SET ROLE admin;
348</programlisting>
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:
351<programlisting>
352SET ROLE wheel;
353</programlisting>
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
357   with any of:
358<programlisting>
359SET ROLE joe;
360SET ROLE NONE;
361RESET ROLE;
362</programlisting>
363  </para>
364
365  <note>
366   <para>
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>.
371   </para>
372  </note>
373
374  <note>
375   <para>
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.
385   </para>
386  </note>
387
388  <para>
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>.
399  </para>
400
401  <para>
402  </para>
403
404  <para>
405   To destroy a group role, use <xref
406   linkend="sql-droprole"/>:
407<synopsis>
408DROP ROLE <replaceable>name</replaceable>;
409</synopsis>
410   Any memberships in the group role are automatically revoked (but the
411   member roles are not otherwise affected).
412  </para>
413 </sect1>
414
415 <sect1 id="role-removal">
416  <title>Dropping Roles</title>
417
418  <para>
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.
424  </para>
425
426  <para>
427   Ownership of objects can be transferred one at a time
428   using <command>ALTER</command> commands, for example:
429<programlisting>
430ALTER TABLE bobs_table OWNER TO alice;
431</programlisting>
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.)
440  </para>
441
442  <para>
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.
451  </para>
452
453  <para>
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.
460  </para>
461
462  <para>
463   In short then, the most general recipe for removing a role that has been
464   used to own objects is:
465  </para>
466<programlisting>
467REASSIGN OWNED BY doomed_role TO successor_role;
468DROP OWNED BY doomed_role;
469-- repeat the above commands in each database of the cluster
470DROP ROLE doomed_role;
471</programlisting>
472
473  <para>
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.
477  </para>
478
479  <para>
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.
483  </para>
484 </sect1>
485
486 <sect1 id="default-roles">
487  <title>Default Roles</title>
488
489  <indexterm zone="default-roles">
490   <primary>role</primary>
491  </indexterm>
492
493  <para>
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.
499  </para>
500
501  <para>
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.
506  </para>
507
508   <table tocentry="1" id="default-roles-table">
509    <title>Default Roles</title>
510    <tgroup cols="2">
511     <thead>
512      <row>
513       <entry>Role</entry>
514       <entry>Allowed Access</entry>
515      </row>
516     </thead>
517     <tbody>
518      <row>
519       <entry>pg_read_all_settings</entry>
520       <entry>Read all configuration variables, even those normally visible only to
521       superusers.</entry>
522      </row>
523      <row>
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>
527      </row>
528      <row>
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>
532      </row>
533      <row>
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>
539      </row>
540      <row>
541       <entry>pg_signal_backend</entry>
542       <entry>Signal another backend to cancel a query or terminate its session.</entry>
543      </row>
544      <row>
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>
548      </row>
549      <row>
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>
553      </row>
554      <row>
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>
558      </row>
559     </tbody>
560    </tgroup>
561   </table>
562
563  <para>
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.
570  </para>
571
572  <para>
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"/>.
579  </para>
580
581  <para>
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.
589  </para>
590
591  <para>
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
594  information.
595  </para>
596
597  <para>
598   Administrators can grant access to these roles to users using the
599   <xref linkend="sql-grant"/> command, for example:
600
601<programlisting>
602GRANT pg_signal_backend TO admin_user;
603</programlisting>
604  </para>
605
606 </sect1>
607
608 <sect1 id="perm-functions">
609  <title>Function Security</title>
610
611  <para>
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.
620  </para>
621
622  <para>
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.
633  </para>
634 </sect1>
635
636</chapter>
637