18
Access Control
Overview
Copyright
© Postgres Professional, 2017–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Cover photo by Oleg Bartunov (Nepal peak, 7143 m, Lhonak)
Use of course materials
Non-commercial use of course materials (presentations, demonstrations) is
allowed without restrictions. Commercial use is possible only with the written
permission of Postgres Professional. It is prohibited to make changes to the
course materials.
Feedback
Please send your feedback, comments and suggestions to:
edu@postgrespro.ru
Disclaimer
Postgres Professional assumes no responsibility for any damages and losses,
including loss of income, caused by direct or indirect, intentional or accidental
use of course materials. Postgres Professional company specifically disclaims
any warranties on course materials. Course materials are provided “as is,” and
Postgres Professional company has no obligations to provide maintenance,
support, updates, enhancements, or modifications.
2
Topics
Roles and Attributes
Connecting to a Server
Password Authentication
Privileges and Ownership
Privilege Management
Granting Membership
Switching to a Role
Obtaining Privileges
3
Roles and Attributes
Role is an account in DBMS
can be included into other roles
not associated with the OS user
Attributes define the properties of a role
LOGIN can log in
SUPERUSER superuser privileges
CREATEDB can create databases
CREATEROLE can create roles
and others
Role in PostgreSQL is an account which is essentially a named set of
access rights. Role can be a member of another role — it is convenient
when setting up access.
Formally, roles are not associated with operating system users in any way,
but many programs imply it when choosing default values. For example, if
psql is started on behalf of the student OS user, the connection is
established on behalf of the database role with the same name, i.e., student
(unless another role is explicitly specified in the psql options).
At the time of cluster initialization, an initial role is defined, which has
superuser privileges (this role is usually called postgres). Later on, you can
create, modify, and delete roles.
A role has several attributes which define its general properties and rights
(unrelated to particular objects).
Generally, attributes come in two opposite variations; for example,
CREATEDB (can create databases) and NOCREATEDB (not allowed to
create databases).
A role with the LOGIN attribute is considered a user role. A role with
NOLOGIN cannot connect to the server and is typically used for grouping
other roles.
The table lists only some of the possible attributes.
5
Connecting to a Server
1. The lines of pg_hba.conf are searched from top to bottom
2. The first line that corresponds to the provided connection
settings (type, database, user, address) will be used
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
local — socket all — any role
host — TCP/IP role name
all — any DB all — any IP
database name IP/mask
domain name
listen_addresses
For each new client, the server has to evaluate whether a database
connection should be allowed. Connection settings are defined in the
pg_hba.conf configuration file (hba stands for host-based authentication).
As with the main configuration file (postgresql.conf), changes come into
effect only after the server reloads this file, either via the pg_reload_conf()
SQL function or the reload command of the management utility.
When a new client appears, the server reads the configuration file from top
to bottom to find the line that matches the requested connection. The match
is defined by four fields: connection type, database name, user name, and
IP address.
Here we list only the main basic options.
Connection: local (unix sockets) or host (a TCP/IP connection).
Database: all (corresponds to any database) or the name of a particular
database.
User: all or the name of a particular role.
Address: all, IP address range, or a domain name. The address is omitted
for the local connection type. By default, PostgreSQL listens only for
connections coming from localhost; the listen_addresses parameter is
usually set to * (listen on all interfaces), while the access is controlled using
pg_hba.conf settings.
6
Connecting to a Server
3. The server performs authentication using the chosen method
4. If successful, access is allowed; otherwise, it is forbidden
(if no rows match the given parameters, access is forbidden)
# TYPE DATABASE USER ADDRESS METHOD
local all postgres peer
local all all peer
host all all 127.0.0.1/32 scram-sha-256
host all all ::1/128 scram-sha-256
trust — allow
reject — forbid
scram-sha-256 — request a password
peer — ask OS
Once the server finds an appropriate line in the file, it performs client
authentication using the method specified in this line, and checks for the
LOGIN attribute. If everything is OK, the connection is allowed; otherwise,
it is forbidden (other lines won’t be considered in this case).
If no appropriate line is found, the access is also forbidden.
Thus, more specific connection lines should precede more generic ones.
There are a lot of different authentication methods:
Here are some of the main ones.
The trust method allows connections unconditionally. If security is not a
concern, you can specify the trust method and use all for all the other
parameters; then all connections will be allowed.
The reject method, on the contrary, unconditionally forbids connections.
The scram-sha-256 method asks for a password and checks that the
provided password matches the one stored in the system catalog of the
database cluster.
The peer method checks the name of the operating system user and allows
connections on behalf of the database user with the same name (you can
also define a different name mapping).
7
Password Authentication
At the server side
password is set when the role is created and can be changed later
user that has no password won’t be able to connect
password is stored in pg_authid system catalog
Providing password on the client
enter manually
PGPASSWORD environment variable
~/.pgpass file (lines format: node:port:database:role:password)
If password authentication is used, there must be a reference password
stored for the user; otherwise the connection will be rejected.
Password hashes are stored in the pg_authid table in the system catalog.
The user can either enter the password manually, or automate password
input using one of the following options.
First, the password can be set in the PGPASSWORD environment variable
(on the client). However, it is inconvenient if you have to connect to several
databases, and it is not recommended for security reasons.
The second option to store passwords on the client is to use the ~/.pgpass
file. The access to this file must be allowed to its owner only (chmod 600),
otherwise PostgreSQL will ignore it.
9
Privilege and Ownership
Privilege
grants the role permission to perform specific actions on an object
Object owner
initially — the role that created it, can be reassigned
receives all privileges on that object
has the right to perform actions not governed by privileges
A privilege on an object grants the role permission to perform specific
actions on that object. For example, the SELECT privilege on a table allows
the role to read data from it.
When a role creates an object, it becomes the owner of that object by
default. If needed, the object’s owner can be changed using the ALTER
command with the OWNER TO clause.
Some actions are not governed by privileges. These include actions such as
dropping or altering an object, granting and revoking privileges, and building
an index on a table. Only the owner — besides superusers — can perform
these actions, and this right cannot be taken away from them.
In addition to these inherent rights, when an object is created, its owner
receives all possible privileges on it. Formally, a superuser can revoke these
privileges, but the owner can always restore them, since they have the right
to grant privileges — including to themselves.
10
Privileges
Tables and views
SELECT read data
INSERT insert rows
UPDATE change rows
REFERENCES foreign key (for tables)
DELETE delete rows
TRUNCATE empty (for tables)
TRIGGER create triggers
MAINTAIN perform VACUUM, ANALYZE, REINDEX etc.
(for tables)
can be set per column
There are different privileges available for different object types. This slide
and the following one list privileges for basic database objects.
The widest choice of privileges is available for tables and views. Some of
these privileges can be set not only at the table level, but also at the column
level.
11
Privileges
Tablespaces, databases, schemas
database
schema pg_temp
tablespace
table
table
object
CREATE
USAGE
CREATE
table
table
object
TEMPORARY
CREATE
CONNECT
For tablespaces, there is a CREATE privilege that allows the creation of
objects in this tablespace.
For databases, the CREATE privilege allows you to create schemas in this
database, and for a schema, the CREATE privilege allows you to create
objects in this schema.
Since the exact name of the schema for temporary objects is unknown in
advance, the privilege to create temporary tables has been moved to the
database level (TEMPORARY).
The USAGE schema privilege allows access to objects in this schema.
The CONNECT database privilege is required (in addition to pg_hba.conf
settings) to be able to connect to this database.
12
Privilege Management
Granting privileges
alice=# GRANT privileges TO bob;
Revoking privileges
alice=# REVOKE privileges FROM bob;
alice
bob
owner
privileges
to object
alice
bob
Privileges are granted using the GRANT command and revoked using the
REVOKE command. To grant or revoke a privilege on an object, you must
have the right to do so. The owner has this right by default.
The syntax of the GRANT and REVOKE commands is quite complex. You
can specify both individual and all possible privileges, both individual objects
and groups of objects included in certain schemas, etc.
14
Inclusion to role Exclusion from role
student=# GRANT dba TO alice; student=# REVOKE dba FROM alice;
The PUBLIC pseudo-role implicitly includes all other roles
Granting Membership
alice
dba
alice
dba
loops are
forbidden
A role can be granted membership in another role. In this case, the second
role acts as a group, and the first role is said to become a member of it.
A role can be a member of multiple roles; in turn, a member role may include
other roles, but circular dependencies are not permitted.
By default, a role inherits the privileges of any group roles it is a member of.
This behavior can be changed by using the NOINHERIT attribute for the
role. This makes the user have to explicitly switch to the group role using
SET ROLE in order to use its privileges. Role attributes are not inherited, but
it is possible to switch to a parent role to use its attributes.
Roles that include other roles typically have the NOLOGIN attribute and are
referred to as “group roles”. We can think of a group role as a predefined set
of privileges that can be granted to a role just like any regular privilege. This
simplifies access control and administration.
There is also a pseudorole called public, which implicitly includes all the
other roles. Any privilege granted to the public role is automatically granted
to all the other roles as well.
15
Switching to a role Returning to the original role
alice=> SET ROLE dba; alice=> RESET ROLE;
Switching to a Role
alice
dba
alice
dba
current_user
session_user
By default a role can execute the SET ROLE command to (temporarily)
switch to a group role, gaining all of its attributes and object privileges. All
actions will be performed on behalf of the group role, and any objects
created will be owned by that group role. The privileges of the original role
are not available while doing so.
The session_user function returns the name of the user who connected to
the server. The name of the current role can be obtained using the
current_user function (or current_role, which is the same thing).
16
Obtaining Privileges
Superuser Access
superusers are not restricted in their actions
Ownership
Holding a Privilege
Role Membership
inheritance of the group role’s privileges
switching to the group role
A role has several ways to obtain the right for particular actions on an object.
Superusers (roles with the SUPERUSER attribute) are a special case: no
privilege checks are performed for them.
By becoming the owner of an object, a role gains the right to perform
virtually any action on it.
A role may also gain the right to perform an action on an object by being
granted a privilege on that object (either automatically upon creating the
object, or explicitly).
Finally, a role can gain additional rights by being made a member of another
role, in which case the role inherits group role’s privileges by default. The
role can switch to the group role, thus gaining not only its privileges but also
attributes.
To check if a role has the necessary privilege with respect to some object,
you can use the has_*_privilege functions:
18
Takeaways
Roles, attributes and privileges together form a flexible
mechanism that allows to set up access control in different ways
can grant all access widely
can restrict access heavily if necessary
When creating a new role, you need to ensure that it can connect
to the server
19
Practice
1. Set up access so that Alice and Bob have their personal schemas
in which they can create and use their own tables with no
restrictions. Neither Alice nor Bob must have access to each
other’s objects.
2. Create a table in public schema and let Alice and Bob read its
content, but not modify it.
3. Think of another solution to task 2.
1. Remember the default setting for search_path configuration parameter
(see Logical Structure lesson of Data Organization module).
3. Use group role. It looks like an overkill in this situation, but imagine that
instead of two users there are hundreds of them, each requiring the same
set of dozen privileges.
