798 lines
34 KiB
Plaintext
798 lines
34 KiB
Plaintext
<!-- 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 superuser, 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_maintain</entry>
|
|
<entry>Allow executing
|
|
<link linkend="sql-vacuum"><command>VACUUM</command></link>,
|
|
<link linkend="sql-analyze"><command>ANALYZE</command></link>,
|
|
<link linkend="sql-cluster"><command>CLUSTER</command></link>,
|
|
<link linkend="sql-refreshmaterializedview"><command>REFRESH MATERIALIZED VIEW</command></link>,
|
|
<link linkend="sql-reindex"><command>REINDEX</command></link>,
|
|
and <link linkend="sql-lock"><command>LOCK TABLE</command></link> on all
|
|
relations, as if having <literal>MAINTAIN</literal> rights on those
|
|
objects, even without having it explicitly.</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>
|