diff options
Diffstat (limited to '')
| -rw-r--r-- | doc/src/sgml/user-manag.sgml | 785 |
1 files changed, 785 insertions, 0 deletions
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml new file mode 100644 index 0000000..1f7d7e7 --- /dev/null +++ b/doc/src/sgml/user-manag.sgml @@ -0,0 +1,785 @@ +<!-- doc/src/sgml/user-manag.sgml --> + +<chapter id="user-manag"> + <title>Database Roles</title> + + <para> + <productname>PostgreSQL</productname> manages database access permissions + using the concept of <firstterm>roles</firstterm>. A role can be thought of as + either a database user, or a group of database users, depending on how + the role is set up. Roles can own database objects (for example, tables + and functions) and can assign privileges on those objects to other roles to + control who has access to which objects. Furthermore, it is possible + to grant <firstterm>membership</firstterm> in a role to another role, thus + allowing the member role to use privileges assigned to another role. + </para> + + <para> + The concept of roles subsumes the concepts of <quote>users</quote> and + <quote>groups</quote>. In <productname>PostgreSQL</productname> versions + before 8.1, users and groups were distinct kinds of entities, but now + there are only roles. Any role can act as a user, a group, or both. + </para> + + <para> + This chapter describes how to create and manage roles. + More information about the effects of role privileges on various + database objects can be found in <xref linkend="ddl-priv"/>. + </para> + + <sect1 id="database-roles"> + <title>Database Roles</title> + + <indexterm zone="database-roles"> + <primary>role</primary> + </indexterm> + + <indexterm zone="database-roles"> + <primary>user</primary> + </indexterm> + + <indexterm> + <primary>CREATE ROLE</primary> + </indexterm> + + <indexterm> + <primary>DROP ROLE</primary> + </indexterm> + + <para> + Database roles are conceptually completely separate from + operating system users. In practice it might be convenient to + maintain a correspondence, but this is not required. Database roles + are global across a database cluster installation (and not + per individual database). To create a role use the <link + linkend="sql-createrole"><command>CREATE ROLE</command></link> SQL command: +<synopsis> +CREATE ROLE <replaceable>name</replaceable>; +</synopsis> + <replaceable>name</replaceable> follows the rules for SQL + identifiers: either unadorned without special characters, or + double-quoted. (In practice, you will usually want to add additional + options, such as <literal>LOGIN</literal>, to the command. More details appear + below.) To remove an existing role, use the analogous + <link linkend="sql-droprole"><command>DROP ROLE</command></link> command: +<synopsis> +DROP ROLE <replaceable>name</replaceable>; +</synopsis> + </para> + + <indexterm> + <primary>createuser</primary> + </indexterm> + + <indexterm> + <primary>dropuser</primary> + </indexterm> + + <para> + For convenience, the programs <xref linkend="app-createuser"/> + and <xref linkend="app-dropuser"/> are provided as wrappers + around these SQL commands that can be called from the shell command + line: +<synopsis> +createuser <replaceable>name</replaceable> +dropuser <replaceable>name</replaceable> +</synopsis> + </para> + + <para> + To determine the set of existing roles, examine the <structname>pg_roles</structname> + system catalog, for example: +<synopsis> +SELECT rolname FROM pg_roles; +</synopsis> + or to see just those capable of logging in: +<synopsis> +SELECT rolname FROM pg_roles WHERE rolcanlogin; +</synopsis> + The <xref linkend="app-psql"/> program's <literal>\du</literal> meta-command + is also useful for listing the existing roles. + </para> + + <para> + In order to bootstrap the database system, a freshly initialized + system always contains one predefined login-capable role. This role + is always a <quote>superuser</quote>, and it will have + the same name as the operating system user that initialized the + database cluster with <command>initdb</command> unless a different name + is specified. This role is often named + <literal>postgres</literal>. In order to create more roles you + first have to connect as this initial role. + </para> + + <para> + Every connection to the database server is made using the name of some + particular role, and this role determines the initial access privileges for + commands issued in that connection. + The role name to use for a particular database + connection is indicated by the client that is initiating the + connection request in an application-specific fashion. For example, + the <command>psql</command> program uses the + <option>-U</option> command line option to indicate the role to + connect as. Many applications assume the name of the current + operating system user by default (including + <command>createuser</command> and <command>psql</command>). Therefore it + is often convenient to maintain a naming correspondence between + roles and operating system users. + </para> + + <para> + The set of database roles a given client connection can connect as + is determined by the client authentication setup, as explained in + <xref linkend="client-authentication"/>. (Thus, a client is not + limited to connect as the role matching + its operating system user, just as a person's login name + need not match his or her real name.) Since the role + identity determines the set of privileges available to a connected + client, it is important to carefully configure privileges when setting up + a multiuser environment. + </para> + </sect1> + + <sect1 id="role-attributes"> + <title>Role Attributes</title> + + <para> + A database role can have a number of attributes that define its + privileges and interact with the client authentication system. + + <variablelist> + <varlistentry> + <term>login privilege<indexterm><primary>login privilege</primary></indexterm></term> + <listitem> + <para> + Only roles that have the <literal>LOGIN</literal> attribute can be used + as the initial role name for a database connection. A role with + the <literal>LOGIN</literal> attribute can be considered the same + as a <quote>database user</quote>. To create a role with login privilege, + use either: +<programlisting> +CREATE ROLE <replaceable>name</replaceable> LOGIN; +CREATE USER <replaceable>name</replaceable>; +</programlisting> + (<command>CREATE USER</command> is equivalent to <command>CREATE ROLE</command> + except that <command>CREATE USER</command> includes <literal>LOGIN</literal> by + default, while <command>CREATE ROLE</command> does not.) + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>superuser status<indexterm><primary>superuser</primary></indexterm></term> + <listitem> + <para> + A database superuser bypasses all permission checks, except the right + to log in. This is a dangerous privilege and should not be used + carelessly; it is best to do most of your work as a role that is not a + superuser. To create a new database superuser, use <literal>CREATE + ROLE <replaceable>name</replaceable> SUPERUSER</literal>. You must do + this as a role that is already a superuser. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>database creation<indexterm><primary>database</primary><secondary>privilege to create</secondary></indexterm></term> + <listitem> + <para> + A role must be explicitly given permission to create databases + (except for superusers, since those bypass all permission + checks). To create such a role, use <literal>CREATE ROLE + <replaceable>name</replaceable> CREATEDB</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term id="role-creation">role creation<indexterm><primary>role</primary><secondary>privilege to create</secondary></indexterm></term> + <listitem> + <para> + A role must be explicitly given permission to create more roles + (except for superusers, since those bypass all permission + checks). To create such a role, use <literal>CREATE ROLE + <replaceable>name</replaceable> CREATEROLE</literal>. + A role with <literal>CREATEROLE</literal> privilege can alter and drop + roles which have been granted to the <literal>CREATEROLE</literal> + user with the <literal>ADMIN</literal> option. Such a grant occurs + automatically when a <literal>CREATEROLE</literal> user that is not + a superuser creates a new role, so that by default, a + <literal>CREATEROLE</literal> user can alter and drop the roles + which they have created. + Altering a role includes most changes that can be made using + <literal>ALTER ROLE</literal>, including, for example, changing + passwords. It also includes modifications to a role that can + be made using the <literal>COMMENT</literal> and + <literal>SECURITY LABEL</literal> commands. + </para> + <para> + However, <literal>CREATEROLE</literal> does not convey the ability to + create <literal>SUPERUSER</literal> roles, nor does it convey any + power over <literal>SUPERUSER</literal> roles that already exist. + Furthermore, <literal>CREATEROLE</literal> does not convey the power + to create <literal>REPLICATION</literal> users, nor the ability to + grant or revoke the <literal>REPLICATION</literal> privilege, nor the + ability to modify the role properties of such users. However, it does + allow <literal>ALTER ROLE ... SET</literal> and + <literal>ALTER ROLE ... RENAME</literal> to be used on + <literal>REPLICATION</literal> roles, as well as the use of + <literal>COMMENT ON ROLE</literal>, + <literal>SECURITY LABEL ON ROLE</literal>, + and <literal>DROP ROLE</literal>. + Finally, <literal>CREATEROLE</literal> does not + confer the ability to grant or revoke the <literal>BYPASSRLS</literal> + privilege. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>initiating replication<indexterm><primary>role</primary><secondary>privilege to initiate replication</secondary></indexterm></term> + <listitem> + <para> + A role must explicitly be given permission to initiate streaming + replication (except for superusers, since those bypass all permission + checks). A role used for streaming replication must + have <literal>LOGIN</literal> permission as well. To create such a role, use + <literal>CREATE ROLE <replaceable>name</replaceable> REPLICATION + LOGIN</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>password<indexterm><primary>password</primary></indexterm></term> + <listitem> + <para> + A password is only significant if the client authentication + method requires the user to supply a password when connecting + to the database. The <option>password</option> and + <option>md5</option> authentication methods + make use of passwords. Database passwords are separate from + operating system passwords. Specify a password upon role + creation with <literal>CREATE ROLE + <replaceable>name</replaceable> PASSWORD '<replaceable>string</replaceable>'</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>inheritance of privileges<indexterm><primary>role</primary><secondary>privilege to inherit</secondary></indexterm></term> + <listitem> + <para> + A role inherits the privileges of roles it is a member of, by default. + However, to create a role which does not inherit privileges by + default, use <literal>CREATE ROLE <replaceable>name</replaceable> + NOINHERIT</literal>. Alternatively, inheritance can be overridden + for individual grants by using <literal>WITH INHERIT TRUE</literal> + or <literal>WITH INHERIT FALSE</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>bypassing row-level security<indexterm><primary>role</primary><secondary>privilege to bypass</secondary></indexterm></term> + <listitem> + <para> + A role must be explicitly given permission to bypass every row-level security (RLS) policy + (except for superusers, since those bypass all permission checks). + To create such a role, use <literal>CREATE ROLE <replaceable>name</replaceable> BYPASSRLS</literal> as a superuser. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term>connection limit<indexterm><primary>role</primary><secondary>privilege to limit connection</secondary></indexterm></term> + <listitem> + <para> + Connection limit can specify how many concurrent connections a role can make. + -1 (the default) means no limit. Specify connection limit upon role creation with + <literal>CREATE ROLE <replaceable>name</replaceable> CONNECTION LIMIT '<replaceable>integer</replaceable>'</literal>. + </para> + </listitem> + </varlistentry> + </variablelist> + + A role's attributes can be modified after creation with + <command>ALTER ROLE</command>.<indexterm><primary>ALTER ROLE</primary></indexterm> + See the reference pages for the <xref linkend="sql-createrole"/> + and <xref linkend="sql-alterrole"/> commands for details. + </para> + + <para> + A role can also have role-specific defaults for many of the run-time + configuration settings described in <xref + linkend="runtime-config"/>. For example, if for some reason you + want to disable index scans (hint: not a good idea) anytime you + connect, you can use: +<programlisting> +ALTER ROLE myname SET enable_indexscan TO off; +</programlisting> + This will save the setting (but not set it immediately). In + subsequent connections by this role it will appear as though + <literal>SET enable_indexscan TO off</literal> had been executed + just before the session started. + You can still alter this setting during the session; it will only + be the default. To remove a role-specific default setting, use + <literal>ALTER ROLE <replaceable>rolename</replaceable> RESET <replaceable>varname</replaceable></literal>. + Note that role-specific defaults attached to roles without + <literal>LOGIN</literal> privilege are fairly useless, since they will never + be invoked. + </para> + + <para> + When a non-superuser creates a role using the <literal>CREATEROLE</literal> + privilege, the created role is automatically granted back to the creating + user, just as if the bootstrap superuser had executed the command + <literal>GRANT created_user TO creating_user WITH ADMIN TRUE, SET FALSE, + INHERIT FALSE</literal>. Since a <literal>CREATEROLE</literal> user can + only exercise special privileges with regard to an existing role if they + have <literal>ADMIN OPTION</literal> on it, this grant is just sufficient + to allow a <literal>CREATEROLE</literal> user to administer the roles they + created. However, because it is created with <literal>INHERIT FALSE, SET + FALSE</literal>, the <literal>CREATEROLE</literal> user doesn't inherit the + privileges of the created role, nor can it access the privileges of that + role using <literal>SET ROLE</literal>. However, since any user who has + <literal>ADMIN OPTION</literal> on a role can grant membership in that + role to any other user, the <literal>CREATEROLE</literal> user can gain + access to the created role by simply granting that role back to + themselves with the <literal>INHERIT</literal> and/or <literal>SET</literal> + options. Thus, the fact that privileges are not inherited by default nor + is <literal>SET ROLE</literal> granted by default is a safeguard against + accidents, not a security feature. Also note that, because this automatic + grant is granted by the bootstrap user, it cannot be removed or changed by + the <literal>CREATEROLE</literal> user; however, any superuser could + revoke it, modify it, and/or issue additional such grants to other + <literal>CREATEROLE</literal> users. Whichever <literal>CREATEROLE</literal> + users have <literal>ADMIN OPTION</literal> on a role at any given time + can administer it. + </para> + </sect1> + + <sect1 id="role-membership"> + <title>Role Membership</title> + + <indexterm zone="role-membership"> + <primary>role</primary><secondary>membership in</secondary> + </indexterm> + + <para> + It is frequently convenient to group users together to ease + management of privileges: that way, privileges can be granted to, or + revoked from, a group as a whole. In <productname>PostgreSQL</productname> + this is done by creating a role that represents the group, and then + granting <firstterm>membership</firstterm> in the group role to individual user + roles. + </para> + + <para> + To set up a group role, first create the role: +<synopsis> +CREATE ROLE <replaceable>name</replaceable>; +</synopsis> + Typically a role being used as a group would not have the <literal>LOGIN</literal> + attribute, though you can set it if you wish. + </para> + + <para> + Once the group role exists, you can add and remove members using the + <link linkend="sql-grant"><command>GRANT</command></link> and + <link linkend="sql-revoke"><command>REVOKE</command></link> commands: +<synopsis> +GRANT <replaceable>group_role</replaceable> TO <replaceable>role1</replaceable>, ... ; +REVOKE <replaceable>group_role</replaceable> FROM <replaceable>role1</replaceable>, ... ; +</synopsis> + You can grant membership to other group roles, too (since there isn't + really any distinction between group roles and non-group roles). The + database will not let you set up circular membership loops. Also, + it is not permitted to grant membership in a role to + <literal>PUBLIC</literal>. + </para> + + <para> + The members of a group role can use the privileges of the role in two + ways. First, member roles that have been granted membership with the + <literal>SET</literal> option can do + <link linkend="sql-set-role"><command>SET ROLE</command></link> to + temporarily <quote>become</quote> the group role. In this state, the + database session has access to the privileges of the group role rather + than the original login role, and any database objects created are + considered owned by the group role not the login role. Second, member + roles that have been granted membership with the + <literal>INHERIT</literal> option automatically have use of the + privileges of those directly or indirectly a member of, though the + chain stops at memberships lacking the inherit option. As an example, + suppose we have done: +<programlisting> +CREATE ROLE joe LOGIN; +CREATE ROLE admin; +CREATE ROLE wheel; +CREATE ROLE island; +GRANT admin TO joe WITH INHERIT TRUE; +GRANT wheel TO admin WITH INHERIT FALSE; +GRANT island TO joe WITH INHERIT TRUE, SET FALSE; +</programlisting> + Immediately after connecting as role <literal>joe</literal>, a database + session will have use of privileges granted directly to <literal>joe</literal> + plus any privileges granted to <literal>admin</literal> and + <literal>island</literal>, because <literal>joe</literal> + <quote>inherits</quote> those privileges. However, privileges + granted to <literal>wheel</literal> are not available, because even though + <literal>joe</literal> is indirectly a member of <literal>wheel</literal>, the + membership is via <literal>admin</literal> which was granted using + <literal>WITH INHERIT FALSE</literal>. After: +<programlisting> +SET ROLE admin; +</programlisting> + the session would have use of only those privileges granted to + <literal>admin</literal>, and not those granted to <literal>joe</literal> or + <literal>island</literal>. After: +<programlisting> +SET ROLE wheel; +</programlisting> + the session would have use of only those privileges granted to + <literal>wheel</literal>, and not those granted to either <literal>joe</literal> + or <literal>admin</literal>. The original privilege state can be restored + with any of: +<programlisting> +SET ROLE joe; +SET ROLE NONE; +RESET ROLE; +</programlisting> + </para> + + <note> + <para> + The <command>SET ROLE</command> command always allows selecting any role + that the original login role is directly or indirectly a member of, + provided that there is a chain of membership grants each of which has + <literal>SET TRUE</literal> (which is the default). + Thus, in the above example, it is not necessary to become + <literal>admin</literal> before becoming <literal>wheel</literal>. + On the other hand, it is not possible to become <literal>island</literal> + at all; <literal>joe</literal> can only access those privileges via + inheritance. + </para> + </note> + + <note> + <para> + In the SQL standard, there is a clear distinction between users and roles, + and users do not automatically inherit privileges while roles do. This + behavior can be obtained in <productname>PostgreSQL</productname> by giving + roles being used as SQL roles the <literal>INHERIT</literal> attribute, while + giving roles being used as SQL users the <literal>NOINHERIT</literal> attribute. + However, <productname>PostgreSQL</productname> defaults to giving all roles + the <literal>INHERIT</literal> attribute, for backward compatibility with pre-8.1 + releases in which users always had use of permissions granted to groups + they were members of. + </para> + </note> + + <para> + The role attributes <literal>LOGIN</literal>, <literal>SUPERUSER</literal>, + <literal>CREATEDB</literal>, and <literal>CREATEROLE</literal> can be thought of as + special privileges, but they are never inherited as ordinary privileges + on database objects are. You must actually <command>SET ROLE</command> to a + specific role having one of these attributes in order to make use of + the attribute. Continuing the above example, we might choose to + grant <literal>CREATEDB</literal> and <literal>CREATEROLE</literal> to the + <literal>admin</literal> role. Then a session connecting as role <literal>joe</literal> + would not have these privileges immediately, only after doing + <command>SET ROLE admin</command>. + </para> + + <para> + </para> + + <para> + To destroy a group role, use <link + linkend="sql-droprole"><command>DROP ROLE</command></link>: +<synopsis> +DROP ROLE <replaceable>name</replaceable>; +</synopsis> + Any memberships in the group role are automatically revoked (but the + member roles are not otherwise affected). + </para> + </sect1> + + <sect1 id="role-removal"> + <title>Dropping Roles</title> + + <para> + Because roles can own database objects and can hold privileges + to access other objects, dropping a role is often not just a matter of a + quick <link linkend="sql-droprole"><command>DROP ROLE</command></link>. Any objects owned by the role must + first be dropped or reassigned to other owners; and any permissions + granted to the role must be revoked. + </para> + + <para> + Ownership of objects can be transferred one at a time + using <command>ALTER</command> commands, for example: +<programlisting> +ALTER TABLE bobs_table OWNER TO alice; +</programlisting> + Alternatively, the <link linkend="sql-reassign-owned"><command>REASSIGN OWNED</command></link> command can be + used to reassign ownership of all objects owned by the role-to-be-dropped + to a single other role. Because <command>REASSIGN OWNED</command> cannot access + objects in other databases, it is necessary to run it in each database + that contains objects owned by the role. (Note that the first + such <command>REASSIGN OWNED</command> will change the ownership of any + shared-across-databases objects, that is databases or tablespaces, that + are owned by the role-to-be-dropped.) + </para> + + <para> + Once any valuable objects have been transferred to new owners, any + remaining objects owned by the role-to-be-dropped can be dropped with + the <link linkend="sql-drop-owned"><command>DROP OWNED</command></link> command. Again, this command cannot + access objects in other databases, so it is necessary to run it in each + database that contains objects owned by the role. Also, <command>DROP + OWNED</command> will not drop entire databases or tablespaces, so it is + necessary to do that manually if the role owns any databases or + tablespaces that have not been transferred to new owners. + </para> + + <para> + <command>DROP OWNED</command> also takes care of removing any privileges granted + to the target role for objects that do not belong to it. + Because <command>REASSIGN OWNED</command> does not touch such objects, it's + typically necessary to run both <command>REASSIGN OWNED</command> + and <command>DROP OWNED</command> (in that order!) to fully remove the + dependencies of a role to be dropped. + </para> + + <para> + In short then, the most general recipe for removing a role that has been + used to own objects is: + </para> +<programlisting> +REASSIGN OWNED BY doomed_role TO successor_role; +DROP OWNED BY doomed_role; +-- repeat the above commands in each database of the cluster +DROP ROLE doomed_role; +</programlisting> + + <para> + When not all owned objects are to be transferred to the same successor + owner, it's best to handle the exceptions manually and then perform + the above steps to mop up. + </para> + + <para> + If <command>DROP ROLE</command> is attempted while dependent objects still + remain, it will issue messages identifying which objects need to be + reassigned or dropped. + </para> + </sect1> + + <sect1 id="predefined-roles"> + <title>Predefined Roles</title> + + <indexterm zone="predefined-roles"> + <primary>role</primary> + </indexterm> + + <para> + <productname>PostgreSQL</productname> provides a set of predefined roles + that provide access to certain, commonly needed, privileged capabilities + and information. Administrators (including roles that have the + <literal>CREATEROLE</literal> privilege) can <command>GRANT</command> these + roles to users and/or other roles in their environment, providing those + users with access to the specified capabilities and information. + </para> + + <para> + The predefined roles are described in <xref linkend="predefined-roles-table"/>. + Note that the specific permissions for each of the roles may change in + the future as additional capabilities are added. Administrators + should monitor the release notes for changes. + </para> + + <table tocentry="1" id="predefined-roles-table"> + <title>Predefined Roles</title> + <tgroup cols="2"> + <colspec colname="col1" colwidth="1*"/> + <colspec colname="col2" colwidth="2*"/> + <thead> + <row> + <entry>Role</entry> + <entry>Allowed Access</entry> + </row> + </thead> + <tbody> + <row> + <entry>pg_read_all_data</entry> + <entry>Read all data (tables, views, sequences), as if having + <command>SELECT</command> rights on those objects, and USAGE rights on + all schemas, even without having it explicitly. This role does not have + the role attribute <literal>BYPASSRLS</literal> set. If RLS is being + used, an administrator may wish to set <literal>BYPASSRLS</literal> on + roles which this role is GRANTed to.</entry> + </row> + <row> + <entry>pg_write_all_data</entry> + <entry>Write all data (tables, views, sequences), as if having + <command>INSERT</command>, <command>UPDATE</command>, and + <command>DELETE</command> rights on those objects, and USAGE rights on + all schemas, even without having it explicitly. This role does not have + the role attribute <literal>BYPASSRLS</literal> set. If RLS is being + used, an administrator may wish to set <literal>BYPASSRLS</literal> on + roles which this role is GRANTed to.</entry> + </row> + <row> + <entry>pg_read_all_settings</entry> + <entry>Read all configuration variables, even those normally visible only to + superusers.</entry> + </row> + <row> + <entry>pg_read_all_stats</entry> + <entry>Read all pg_stat_* views and use various statistics related extensions, + even those normally visible only to superusers.</entry> + </row> + <row> + <entry>pg_stat_scan_tables</entry> + <entry>Execute monitoring functions that may take <literal>ACCESS SHARE</literal> locks on tables, + potentially for a long time.</entry> + </row> + <row> + <entry>pg_monitor</entry> + <entry>Read/execute various monitoring views and functions. + This role is a member of <literal>pg_read_all_settings</literal>, + <literal>pg_read_all_stats</literal> and + <literal>pg_stat_scan_tables</literal>.</entry> + </row> + <row> + <entry>pg_database_owner</entry> + <entry>None. Membership consists, implicitly, of the current database owner.</entry> + </row> + <row> + <entry>pg_signal_backend</entry> + <entry>Signal another backend to cancel a query or terminate its session.</entry> + </row> + <row> + <entry>pg_read_server_files</entry> + <entry>Allow reading files from any location the database can access on the server with COPY and + other file-access functions.</entry> + </row> + <row> + <entry>pg_write_server_files</entry> + <entry>Allow writing to files in any location the database can access on the server with COPY and + other file-access functions.</entry> + </row> + <row> + <entry>pg_execute_server_program</entry> + <entry>Allow executing programs on the database server as the user the database runs as with + COPY and other functions which allow executing a server-side program.</entry> + </row> + <row> + <entry>pg_checkpoint</entry> + <entry>Allow executing + the <link linkend="sql-checkpoint"><command>CHECKPOINT</command></link> + command.</entry> + </row> + <row> + <entry>pg_use_reserved_connections</entry> + <entry>Allow use of connection slots reserved via + <xref linkend="guc-reserved-connections"/>.</entry> + </row> + <row> + <entry>pg_create_subscription</entry> + <entry>Allow users with <literal>CREATE</literal> permission on the + database to issue + <link linkend="sql-createsubscription"><command>CREATE SUBSCRIPTION</command></link>.</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + The <literal>pg_monitor</literal>, <literal>pg_read_all_settings</literal>, + <literal>pg_read_all_stats</literal> and <literal>pg_stat_scan_tables</literal> + roles are intended to allow administrators to easily configure a role for the + purpose of monitoring the database server. They grant a set of common privileges + allowing the role to read various useful configuration settings, statistics and + other system information normally restricted to superusers. + </para> + + <para> + The <literal>pg_database_owner</literal> role has one implicit, + situation-dependent member, namely the owner of the current database. Like + any role, it can own objects or receive grants of access privileges. + Consequently, once <literal>pg_database_owner</literal> has rights within a + template database, each owner of a database instantiated from that template + will exercise those rights. <literal>pg_database_owner</literal> cannot be + a member of any role, and it cannot have non-implicit members. Initially, + this role owns the <literal>public</literal> schema, so each database owner + governs local use of the schema. + </para> + + <para> + The <literal>pg_signal_backend</literal> role is intended to allow + administrators to enable trusted, but non-superuser, roles to send signals + to other backends. Currently this role enables sending of signals for + canceling a query on another backend or terminating its session. A user + granted this role cannot however send signals to a backend owned by a + superuser. See <xref linkend="functions-admin-signal"/>. + </para> + + <para> + The <literal>pg_read_server_files</literal>, <literal>pg_write_server_files</literal> and + <literal>pg_execute_server_program</literal> roles are intended to allow administrators to have + trusted, but non-superuser, roles which are able to access files and run programs on the + database server as the user the database runs as. As these roles are able to access any file on + the server file system, they bypass all database-level permission checks when accessing files + directly and they could be used to gain superuser-level access, therefore + great care should be taken when granting these roles to users. + </para> + + <para> + Care should be taken when granting these roles to ensure they are only used where + needed and with the understanding that these roles grant access to privileged + information. + </para> + + <para> + Administrators can grant access to these roles to users using the + <link linkend="sql-grant"><command>GRANT</command></link> command, for example: + +<programlisting> +GRANT pg_signal_backend TO admin_user; +</programlisting> + </para> + + </sect1> + + <sect1 id="perm-functions"> + <title>Function Security</title> + + <para> + Functions, triggers and row-level security policies allow users to insert + code into the backend server that other users might execute + unintentionally. Hence, these mechanisms permit users to <quote>Trojan + horse</quote> others with relative ease. The strongest protection is tight + control over who can define objects. Where that is infeasible, write + queries referring only to objects having trusted owners. Remove + from <varname>search_path</varname> any schemas that permit untrusted users + to create objects. + </para> + + <para> + Functions run inside the backend + server process with the operating system permissions of the + database server daemon. If the programming language + used for the function allows unchecked memory accesses, it is + possible to change the server's internal data structures. + Hence, among many other things, such functions can circumvent any + system access controls. Function languages that allow such access + are considered <quote>untrusted</quote>, and + <productname>PostgreSQL</productname> allows only superusers to + create functions written in those languages. + </para> + </sect1> + +</chapter> |