Database Roles {#user-manag}
==============

PostgreSQL manages database access permissions using the concept of
roles. 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 membership in a role
to another role, thus allowing the member role to use privileges
assigned to another role.

The concept of roles subsumes the concepts of "users" and "groups". In
PostgreSQL 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.

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 [???](#ddl-priv).

Database Roles {#database-roles}
==============

role

user

CREATE ROLE

DROP ROLE

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 [`CREATE ROLE`](#sql-createrole) SQL
command: CREATE ROLE name; name 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
`LOGIN`, to the command. More details appear below.) To remove an
existing role, use the analogous [`DROP ROLE`](#sql-droprole) command:
DROP ROLE name;

createuser

dropuser

For convenience, the programs [???](#app-createuser) and
[???](#app-dropuser) are provided as wrappers around these SQL commands
that can be called from the shell command line: createuser name dropuser
name

To determine the set of existing roles, examine the pg\_roles system
catalog, for example SELECT rolname FROM pg\_roles; The [???](#app-psql)
program\'s `\du` meta-command is also useful for listing the existing
roles.

In order to bootstrap the database system, a freshly initialized system
always contains one predefined role. This role is always a "superuser",
and by default (unless altered when running `initdb`) it will have the
same name as the operating system user that initialized the database
cluster. Customarily, this role will be named `postgres`. In order to
create more roles you first have to connect as this initial role.

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 `psql` program uses the `-U` command line option to
indicate the role to connect as. Many applications assume the name of
the current operating system user by default (including `createuser` and
`psql`). Therefore it is often convenient to maintain a naming
correspondence between roles and operating system users.

The set of database roles a given client connection can connect as is
determined by the client authentication setup, as explained in
[???](#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.

Role Attributes
===============

A database role can have a number of attributes that define its
privileges and interact with the client authentication system.

login privilegelogin privilege

:   Only roles that have the `LOGIN` attribute can be used as the
    initial role name for a database connection. A role with the `LOGIN`
    attribute can be considered the same as a "database user". To create
    a role with login privilege, use either:

        CREATE ROLE name LOGIN;
        CREATE USER name;

    (`CREATE USER` is equivalent to `CREATE ROLE` except that
    `CREATE USER` includes `LOGIN` by default, while `CREATE ROLE` does
    not.)

superuser statussuperuser

:   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 `CREATE
            ROLE name SUPERUSER`. You must do this as a role that is
    already a superuser.

database creationdatabaseprivilege to create

:   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 `CREATE ROLE
            name CREATEDB`.

role creationroleprivilege to create

:   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 `CREATE ROLE
            name CREATEROLE`. A role with `CREATEROLE` privilege can
    alter and drop other roles, too, as well as grant or revoke
    membership in them. However, to create, alter, drop, or change
    membership of a superuser role, superuser status is required;
    `CREATEROLE` is insufficient for that.

initiating replicationroleprivilege to initiate replication

:   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
    `LOGIN` permission as well. To create such a role, use
    `CREATE ROLE name REPLICATION
            LOGIN`.

passwordpassword

:   A password is only significant if the client authentication method
    requires the user to supply a password when connecting to the
    database. The `password` and `md5` authentication methods make use
    of passwords. Database passwords are separate from operating system
    passwords. Specify a password upon role creation with `CREATE ROLE
            name PASSWORD 'string'`.

A role\'s attributes can be modified after creation with
`ALTER ROLE`.ALTER ROLE See the reference pages for the
[???](#sql-createrole) and [???](#sql-alterrole) commands for details.

> **Tip**
>
> It is good practice to create a role that has the `CREATEDB` and
> `CREATEROLE` privileges, but is not a superuser, and then use this
> role for all routine management of databases and roles. This approach
> avoids the dangers of operating as a superuser for tasks that do not
> really require it.

A role can also have role-specific defaults for many of the run-time
configuration settings described in [???](#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:

    ALTER ROLE myname SET enable_indexscan TO off;

This will save the setting (but not set it immediately). In subsequent
connections by this role it will appear as though
`SET enable_indexscan TO off` 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
`ALTER ROLE rolename RESET varname`. Note that role-specific defaults
attached to roles without `LOGIN` privilege are fairly useless, since
they will never be invoked.

Role Membership
===============

role

membership in

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 PostgreSQL this is done by creating a role that
represents the group, and then granting membership in the group role to
individual user roles.

To set up a group role, first create the role: CREATE ROLE name;
Typically a role being used as a group would not have the `LOGIN`
attribute, though you can set it if you wish.

Once the group role exists, you can add and remove members using the
[`GRANT`](#sql-grant) and [`REVOKE`](#sql-revoke) commands: GRANT
group\_role TO role1, \... ; REVOKE group\_role FROM role1, \... ; 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 `PUBLIC`.

The members of a group role can use the privileges of the role in two
ways. First, every member of a group can explicitly do
[`SET ROLE`](#sql-set-role) to temporarily "become" 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 the `INHERIT` attribute automatically
have use of the privileges of roles of which they are members, including
any privileges inherited by those roles. As an example, suppose we have
done:

    CREATE ROLE joe LOGIN INHERIT;
    CREATE ROLE admin NOINHERIT;
    CREATE ROLE wheel NOINHERIT;
    GRANT admin TO joe;
    GRANT wheel TO admin;

Immediately after connecting as role `joe`, a database session will have
use of privileges granted directly to `joe` plus any privileges granted
to `admin`, because `joe` "inherits" `admin`\'s privileges. However,
privileges granted to `wheel` are not available, because even though
`joe` is indirectly a member of `wheel`, the membership is via `admin`
which has the `NOINHERIT` attribute. After:

    SET ROLE admin;

the session would have use of only those privileges granted to `admin`,
and not those granted to `joe`. After:

    SET ROLE wheel;

the session would have use of only those privileges granted to `wheel`,
and not those granted to either `joe` or `admin`. The original privilege
state can be restored with any of:

    SET ROLE joe;
    SET ROLE NONE;
    RESET ROLE;

> **Note**
>
> The `SET ROLE` command always allows selecting any role that the
> original login role is directly or indirectly a member of. Thus, in
> the above example, it is not necessary to become `admin` before
> becoming `wheel`.

> **Note**
>
> 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 PostgreSQL by giving roles being
> used as SQL roles the `INHERIT` attribute, while giving roles being
> used as SQL users the `NOINHERIT` attribute. However, PostgreSQL
> defaults to giving all roles the `INHERIT` attribute, for backward
> compatibility with pre-8.1 releases in which users always had use of
> permissions granted to groups they were members of.

The role attributes `LOGIN`, `SUPERUSER`, `CREATEDB`, and `CREATEROLE`
can be thought of as special privileges, but they are never inherited as
ordinary privileges on database objects are. You must actually
`SET ROLE` 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 `CREATEDB` and `CREATEROLE` to the `admin` role. Then a session
connecting as role `joe` would not have these privileges immediately,
only after doing `SET ROLE admin`.

To destroy a group role, use [`DROP ROLE`](#sql-droprole): DROP ROLE
name; Any memberships in the group role are automatically revoked (but
the member roles are not otherwise affected).

Dropping Roles {#role-removal}
==============

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
[`DROP ROLE`](#sql-droprole). 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.

Ownership of objects can be transferred one at a time using `ALTER`
commands, for example:

    ALTER TABLE bobs_table OWNER TO alice;

Alternatively, the [`REASSIGN OWNED`](#sql-reassign-owned) command can
be used to reassign ownership of all objects owned by the
role-to-be-dropped to a single other role. Because `REASSIGN OWNED`
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 `REASSIGN OWNED` will change the ownership of any
shared-across-databases objects, that is databases or tablespaces, that
are owned by the role-to-be-dropped.)

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 [`DROP OWNED`](#sql-drop-owned) 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, `DROP
   OWNED` 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.

`DROP OWNED` also takes care of removing any privileges granted to the
target role for objects that do not belong to it. Because
`REASSIGN OWNED` does not touch such objects, it\'s typically necessary
to run both `REASSIGN OWNED` and `DROP OWNED` (in that order!) to fully
remove the dependencies of a role to be dropped.

In short then, the most general recipe for removing a role that has been
used to own objects is:

    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;

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.

If `DROP ROLE` is attempted while dependent objects still remain, it
will issue messages identifying which objects need to be reassigned or
dropped.

Predefined Roles
================

role

PostgreSQL provides a set of predefined roles that provide access to
certain, commonly needed, privileged capabilities and information.
Administrators (including roles that have the `CREATEROLE` privilege)
can `GRANT` these roles to users and/or other roles in their
environment, providing those users with access to the specified
capabilities and information.

The predefined roles are described in
[table\_title](#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.

  -------------------------------------------------------------------------------
  Role                           Allowed Access
  ------------------------------ ------------------------------------------------
  pg\_read\_all\_data            Read all data (tables, views, sequences), as if
                                 having `SELECT` rights on those objects, and
                                 USAGE rights on all schemas, even without having
                                 it explicitly. This role does not have the role
                                 attribute `BYPASSRLS` set. If RLS is being used,
                                 an administrator may wish to set `BYPASSRLS` on
                                 roles which this role is GRANTed to.

  pg\_write\_all\_data           Write all data (tables, views, sequences), as if
                                 having `INSERT`, `UPDATE`, and `DELETE` rights
                                 on those objects, and USAGE rights on all
                                 schemas, even without having it explicitly. This
                                 role does not have the role attribute
                                 `BYPASSRLS` set. If RLS is being used, an
                                 administrator may wish to set `BYPASSRLS` on
                                 roles which this role is GRANTed to.

  pg\_read\_all\_settings        Read all configuration variables, even those
                                 normally visible only to superusers.

  pg\_read\_all\_stats           Read all pg\_stat\_\* views and use various
                                 statistics related extensions, even those
                                 normally visible only to superusers.

  pg\_stat\_scan\_tables         Execute monitoring functions that may take
                                 `ACCESS SHARE` locks on tables, potentially for
                                 a long time.

  pg\_monitor                    Read/execute various monitoring views and
                                 functions. This role is a member of
                                 `pg_read_all_settings`, `pg_read_all_stats` and
                                 `pg_stat_scan_tables`.

  pg\_database\_owner            None. Membership consists, implicitly, of the
                                 current database owner.

  pg\_signal\_backend            Signal another backend to cancel a query or
                                 terminate its session.

  pg\_read\_server\_files        Allow reading files from any location the
                                 database can access on the server with COPY and
                                 other file-access functions.

  pg\_write\_server\_files       Allow writing to files in any location the
                                 database can access on the server with COPY and
                                 other file-access functions.

  pg\_execute\_server\_program   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.

  pg\_checkpointer               Allow executing the
                                 [`CHECKPOINT`](#sql-checkpoint) command.
  -------------------------------------------------------------------------------

  : Predefined Roles

The `pg_monitor`, `pg_read_all_settings`, `pg_read_all_stats` and
`pg_stat_scan_tables` 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.

The `pg_database_owner` 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
`pg_database_owner` has rights within a template database, each owner of
a database instantiated from that template will exercise those rights.
`pg_database_owner` cannot be a member of any role, and it cannot have
non-implicit members. Initially, this role owns the `public` schema, so
each database owner governs local use of the schema.

The `pg_signal_backend` 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
[???](#functions-admin-signal).

The `pg_read_server_files`, `pg_write_server_files` and
`pg_execute_server_program` 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.

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.

Administrators can grant access to these roles to users using the
[`GRANT`](#sql-grant) command, for example:

    GRANT pg_signal_backend TO admin_user;

Function Security {#perm-functions}
=================

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 "Trojan horse"
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
`search_path` any schemas that permit untrusted users to create objects.

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
"untrusted", and PostgreSQL allows only superusers to create functions
written in those languages.
