Access Control
Access Control Overview
15
Copyright
© Postgres Professional, 2023
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Liudmila Mantrova
Cover photo by Oleg Bartunov (Phu monastery and Bhrikuti peak, Nepal)
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:
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
Agenda
Roles and attributes
Connecting to a server
Privileges
Row-level security policies
3
Roles and Attributes
A role is a database user
roles are not associated with OS users
Role properties are defined by attributes
LOGIN permission to connect
PASSWORD a password
SUPERUSER permission for everything
CREATEDB permission to create databases
CREATEROLE permission to create roles
REPLICATION permission to use the replication protocol
etc.
A role is a database user. (A role can also comprise a group of users, but
we’ll discuss it later.)
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 that define its general properties and rights
(unrelated to object access).
There are usually two flavors of each attribute; for example, CREATEDB
(gives the right to create a database) and NOCREATEDB (gives no such
right). As a rule, a restrictive flavor is the default one.
If a role has no LOGIN attribute, it cannot connect to a server. (Such roles
can be used as group ones.)
This slide lists only some of the available attributes. INHERIT and
BYPASSRLS attributes will be covered in more detail further in this lecture.
5
Connection
1. The rows of pg_hba.conf are searched from top to bottom
2. The first row that corresponds to the provided connection
parameters (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 database 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 parameters 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 (SELECT pg_reload_conf() in
SQL, or pg_ctl reload in the operating system terminal).
When a new client appears, the server reads the configuration file from top
to bottom to find the row 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, unavailable for Windows) or host
(a TCP/IP connection).
Database: all (this keyword corresponds to any database) or the name
of a particular database.
User: all or the name of a particular role.
Address: all, a particular IP address with a subnet mask, or a domain
name. The address is omitted for the local connection type. By default,
PostgreSQL listens for incoming connections only on localhost; the
listen_addresses parameter is usually set to * (listen on all interfaces),
while the access is controlled using pg_hba.conf settings.
6
Connection
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 all
reject — forbid all
scram-sha-256 and md5 — request a password
peer — ask OS
Once the server finds an appropriate row in the file, it performs client
authentication using the method specified in this row, and checks for the
LOGIN attribute and the CONNECT privilege. If everything is OK, the
connection is allowed; otherwise, it is forbidden (other rows won’t be
considered in this case).
If no appropriate row is found, the access is also forbidden.
Thus, more specific connection rows should precede more generic ones
while the file is viewed from top to bottom.
There are a lot of different authentication methods:
Here we mention only 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 all
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 md5 method is considered deprecated.
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 pattern).
7
Password Authentication
At the server side
the password is set when the role is created and can be altered later
a user that has no password won’t be able to connect
the password is stored in the pg_authid table of the system catalog
Entering the password on the client
manually
using the PGPASSWORD environment variable
using the ~/.pgpass file (its lines have the following 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.
Passwords are stored in the pg_authid table of the system catalog.
The user can either enter the password manually, or automate password
input using one of the following options.
The first one is to define the password on the client in the PGPASSWORD
environment variable. 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, otherwise
PostgreSQL will ignore it.
9
Privileges
Privileges define roles’ access rights for different objects
Tables and views
SELECT read data
INSERT insert rows
UPDATE update rows
REFERENCES set a foreign key
DELETE delete rows
TRUNCATE truncate a table
TRIGGER create triggers
can be used for columns
Privileges are defined for combinations of roles and database objects. They
determine the actions that roles can perform with these objects.
There are different privileges available for different object types.
The widest choice of privileges is available for tables and views. Some of
these privileges can be defined not only at the table level, but also at the
column level.
10
Privileges
Tablespaces, databases,
and schemas
Functions and procedures
EXECUTE
etc.
database
schema pg_temp
tablespace
таблица
таблица
object
CREATE
USAGE
CREATE
таблица
таблица
object
TEMPORARY
CREATE
CONNECT
For tablespaces, there is a CREATE privilege that allows creating objects in
this tablespace.
When defined for a database, the CREATE privilege allows creating schemas
in this database; for schemas, this privilege allows creating objects in this
schema.
Since the exact name of the schema for temporary objects is unknown in
advance, the privilege for creating temporary tables is defined at the
database level (TEMPORARY).
The USAGE privilege of a schema enables access to objects in this schema.
The CONNECT privilege of a database allows connections to this database.
11
Categories of Roles
Superusers
unlimited access to all objects: no checks are performed
Object owners
initially, all the privileges that can be granted on this object
actions on the owned object that are not regulated by privileges,
such as deleting objects or granting and revoking privileges
Other roles
access rights are defined by the granted privileges
In general, we can simply say that the role’s access to an object is defined
by its privileges. But it makes sense to single out three categories of roles
and discuss them separately.
1. Roles with the superuser attribute follow the most straightforward rules.
Such roles bypass access control checks and can perform any operations.
2. Each object has an owner, i.e., the role that owns this object. Initially, it is
the role that created the object, but you can change the owner later.
The object owner gets the full range of privileges on this object.
In theory, these privileges can be revoked, but the object owner also has an
inalienable right to perform some actions that are not regulated by
privileges. In particular, the owner can grant and revoke privileges (to other
roles and to itself) and delete the owned object.
3. All the other roles can access objects as defined by the privileges granted
to them.
12
Managing Privileges
Granting privileges
alice=> GRANT privileges ON objects TO bob;
Revoking privileges
alice=> REVOKE privileges ON object FROM bob;
alice bob
privileges
on an object
Privileges on a particular object can be granted and revoked by the object
owner (and a superuser).
The syntax of GRANT and REVOKE commands is quite complex. It allows
addressing different scenarios: you can specify either some particular
privileges or all the available privileges at once, grant privileges on individual
objects or on groups of objects that belong to particular schemas, etc.
14
Including a role into a group
student=> GRANT dba TO alice;
the public pseudorole implicitly includes
all the other roles
Excluding a role from a group
student=> REVOKE dba FROM alice;
public
Group Roles
student alice
dba
dba
alice
student
Apart from representing a database user, each role can also include other
roles, i.e., represent a group of roles. A role can be included into another
role, just like a Unix user can be included into a group.
It is also possible to include a group role into another group (but circular
membership loops are not allowed). In fact, PostgreSQL does not
differentiate between single-user and group roles.
The idea of such inclusion is to make group role privileges available to
single-user 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. It facilitates database
administration and access control.
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.
The following roles have the right to modify a role by including or excluding
other roles:
- the role to be included (or excluded)
- a role with the SUPERUSER attribute
- a role with the CREATEROLE attribute
15
pg_signal_backend — terminate sessions and cancel queries
pg_read_all_settings — read server parameters
pg_read_all_stats — access statistics
pg_stat_scan_tables — access statistics that take locks
pg_read_all_data — read data in all tables
pg_write_all_data — modify data in all tables
pg_read_server_files — read files on the server
pg_write_server_files — write files on the server
pg_execute_server_programs — execute programs on the server
pg_checkpoint — execute CHECKPOINT command
Predefined Group Roles
pg_monitor
PostgreSQL provides a number of predefined roles that have some
privileged capabilities. These roles can be granted by superusers to DBA
roles, allowing them to perform specific tasks that otherwise would require
SUPERUSER attribute.
The list of predefined roles increases with each version. The full list of all the
roles, including predefined ones, cat be seen by \duS command in psql.
You can create your own group roles, e.g. for managing backups etc.
17
Default Privileges
Privileges of the public pseudorole
connection to any database
access to the public schema and the right to create objects in it
access to the system catalog
execution of any routines
privileges are automatically granted on each new object
Configuring default privileges
a possibility to grant or revoke privileges on a newly created object
revoked as of
PostgreSQL 15
As we have already said, the public pseudorole includes all other roles, so
they inherit all the privileges granted to public.
And public has quite an extensive list of privileges by default. In particular:
- The right to connect to any database (that’s why the role alice could
connect to the database although the CONNECT privilege had not been
explicitly granted to this role).
- Access to the system catalog.
- Write access to the public schema (this was revoked in PostgreSQL 15).
- The right to execute any routines.
On the one hand, it enables seamless operation without having to deal with
privileges; but on the other hand, it brings extra complications if access
control is really required.
The public role automatically receives all the privileges listed above for all
newly created objects. So it is not enough to simply revoke the EXECUTE
privilege from public: once a new routine appears, public immediately
gets the right to execute it.
There is a special mechanism of default privileges that enables you to
automatically grant the required privileges on newly created objects. It can
be also used to revoke the EXECUTE privilege from the public pseudorole.
19
Row-Level Security
Policies complement privileges
to manage access to tables at the row level
A policy is applied
to particular roles
to particular commands (SELECT, INSERT, UPDATE, DELETE)
A policy defines row access conditions
permissive: allows row access if the condition is true
restrictive: forbids row access if the condition is false
different conditions (predicates) for existing rows and the rows to be inserted
While privileges provide access control at the table and column levels, row-
level security policies do it at the row level.
By default, RLS is switched off. If required, it has to be enabled for each
table explicitly.
Policies can be defined for a table, a set of commands (SELECT, INSERT,
UPDATE, DELETE), and for particular roles. In fact, each policy is a boolean
condition (a predicate), which is computed for each selected row. If the
condition is true, the access is allowed (the access must not be forbidden by
any restrictive policy and must be allowed by at least one permissive policy
at the same time). Otherwise, the row won’t be visible.
The predicates defining access to existing and newly added rows can differ
(in this case, an UPDATE operation will be successful only if both predicates
are true).
Predicates are computed with the rights of the caller.
RLS policies do not apply to the table owner (in most cases), superusers,
roles with the BYPASSRLS attribute, and cannot be used for integrity
constraints (uniqueness, foreign keys).
21
Takeaways
Roles, privileges, and policies provide a flexible access control
mechanism for different usage scenarios
you can easily allow everything to everyone
you can set up strict access control if required
When creating a new role, it is important to ensure that it can
connect to the server
22
Practice
1. Create a database, a schema, and a table with two columns:
a key and a value.
Create a role.
2. Find out the IP address and the subnet mask of the virtual
machine and configure the system so that the connection from
this address is allowed only to the created role and only to the
created database, using password authentication.
Make sure that student is still locally trusted by the server.
3. Configure access control so that the created role can query the
table and change the values in it, but not the keys.
4. Restore original authentication settings.
Task 2. Make sure you have a backup copy of the pg_hba.conf file before
making changes.
The IP address and the subnet mask can be found with the following
command:
ip a | awk '/scope global/ {print $2}'
Do not forget about the listen_addresses parameter.
