Access Control
Overview
16
Copyright
© Postgres Professional, 2017–2025
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Photo: 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:
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 Privilege Management
Role Categories
Group and Predefined Roles
Default Privileges
Privileges and Routines
3
Roles and Attributes
Role can be a DBMS user
not associated with the OS user
Roles can be included into other roles
simplifies access setup
Attributes define the properties of a role
LOGIN can log in
SUPERUSER superuser privileges
CREATEDB can create databases
CREATEROLE can create roles
and others
Roles in PostgreSQL are used for two purposes. Firstly, a role can be a
DBMS user. Secondly, roles can be members of other roles — 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 и md5 — 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 and the CONNECT privilege. 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 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).
7
Password Authentication
At the server side
the password is set when the role is created and can be changed later
a user that has no password won’t be able to connect
the password is stored in the pg_authid system catalog
Entering the password on the client
manually
using the PGPASSWORD environment variable
using the ~/.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
Privileges
Privileges define access rights of roles to objects
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
can be set per column
Privileges establish a relation between subjects (roles) and objects in the
cluster. They determine the actions that roles can perform with these
objects.
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.
10
Privileges
Tablespaces,
database, schemas
Sequences
SELECT currval
UPDATE nextval setval
USAGE currval nextval
database
schema pg_temp
tablespace
table
table
object
CREATE
USAGE
CREATE
table
table
object
TEMPORARY
CREATE
CONNECT
Sequences have a somewhat unexpected set of privileges. They serve to
allow or restrict access to the three control functions.
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 allows connection to this database.
11
Role Categories
Superusers
full access to all objects, no checks performed
Owners
initially, all privileges on the object (can be revoked)
actions that are not regulated by privileges, such as deleting objects, granting
and revoking privileges, etc.
Other roles
access within the granted privileges
Generally speaking, a role’s ability to access an object is defined by the
role’s privileges. But it makes sense to single out three categories of roles
and discuss them separately.
1. Roles with the SUPERUSER attribute (superusers). These roles can do
anything and ignore all access control checks.
2. Object owner. Initially, this is the role that created the object, although it
can be changed later. It’s not just the object creator role that becomes the
owner, but also any other role included in it. The object owner gets the full
range of privileges on this object.
Technically, these privileges can be revoked, but the owner always retains
inherent rights on the actions that are not regulated by any privileges. In
particular, the owner can grant and revoke privileges (including to and
from themselves), delete the object, etc.
3. All other roles have access to the object only as far as the privileges
granted to them allow it.
To check if a role has the necessary privilege with respect to some object,
you can use the has_*_privilege functions:
12
Privilege Management
Granting privileges
alice: GRANT privileges ON object TO bo b;
Revoking privileges
alice: REVOKE privile ges ON object FROM bob;
alice
bob
privileges
on object
The right to grant and revoke privileges on an object belongs to the owner of
that object (and the superuser).
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
public
Granting membership
alice: GRANT dba TO bob;
public pseudo-role implicitly includes
all other roles
Revoking membership
alice: REVOKE dba FROM bob;
alice
bob
dba
dba
bob
alice
Granting Membership
Any role can include other roles as members. In this case, the role acts as a
group. PostgreSQL does not have a separate “group” entity.
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
Predefined Roles
pg_read_all_settings read all server parameters
pg_read_all_stats access statistics
pg_stat_scan_tables monitoring and locking tables
pg_read_all_data read data in all tables
pg_write_all_data change data in all tables
pg_read_server_files read files on the server
pg_write_server_files write to files on the server
pg_execute_server_programs run programs on the server
...
pg_monitor
PostgreSQL provides a number of predefined roles that have access to
some frequently used, but privileged capabilities and data. Membership in
these roles may be granted to users in order to facilitate administrative and
maintenance tasks without providing them superuser capabilities.
The list of predefined roles increases with each PostgreSQL version. The full
list of all the roles, including predefined ones, cat be seen by \duS
command in psql.
You can create your own administrative group roles, e.g. for managing
backups.
17
Routines
The only privilege for functions and procedures
EXECUTE execution
Security modes
SECURITY INVOKER executed with the privileges of the caller
(by default)
SECURITY DEFINER executed with the privileges of the owner
The privilege EXECUTE, the only privilege for functions and procedures,
allows them to execute.
The user on behalf of which the routine is executed is important. If a routine
is declared as a SECURITY INVOKER (by default), it is executed with the
rights of the user that runs it. In this case, the statements inside the routine
can access only those objects that are accessible to the calling user.
On the other hand, if declared with the SECURITY DEFINER, the routine will
use the rights of its owner. This is a way to allow certain users perform
certain actions on objects they personally have no access to.
18
Default Privileges
Privileges of the public pseudo-role
connect to any database
access to the system catalog
execution of any routines
privileges are granted automatically on each new object
Configurable default privileges
the possibility to additionally grant or revoke privileges on a newly created
object
As we have already said, the public pseudo-role 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
●
Execution of 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 and revoke the required privileges on newly created
objects. It can be also used to revoke the EXECUTE privilege from the
public pseudo-role.
20
Takeaways
Roles, attributes and privileges together form a flexible
mechanism that allows you 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
21
Practice
1. Create two roles (the password must match the name):
– employee — store employee,
– buyer — customer.
Make sure that the created roles can connect to the database.
2. Revoke the rights of the role public to perform all functions and
connect to the database.
3. Delimit access in such a way that:
– the employee could only order books and add authors and
books,
– the buyer could only purchase books.
Check the changes in the application.
1. The employee is an internal user of the application, authentication is
performed at the database level.
The buyer is an external user. In a real online store, the management of
such users falls to the application, and all queries come to the database
from one “generalized” role (buyer). The identifier of a specific customer can
be passed as a parameter (but we do not do this in our application).
3. Generally speaking, access control should be embedded in the
application. In our educational application, it is not implemented on purpose:
instead, on the web page, you can explicitly select the role on behalf of
which the query will be sent to the database.
This allows you to see how the server side will behave if the application is
not working correctly.
So, users need to get:
●
The right to connect to the bookstore database and access the bookstore
schema.
●
Access to views that are accessed directly.
●
Access to functions that are called as part of the API. If you leave the
SECURITY INVOKER functions in, you will have to grant access to all the
“underlying” objects (tables, other functions). However, it is more
convenient to simply declare API functions as SECURITY DEFINER.
Of course, roles need to be granted privileges only on those objects that
they should have access to.
23
Practice+
1. Register the user roles alice and bob.
2. Modify the pg_hba.conf file to allow access without password
only for the postgres and student users, ensuring that access for
alice and bob remains restricted.
3. Enable peer authentication method for alice and bob. Check that
connection attempts fail without OS user mapping. Create such
mapping for alice.
4. Check the capability to use the same OS user mapping for
different database roles.
2. Using a text editor, insert the new entry before the first uncommented
directive in pg_hba.conf.
local all postgres,student trust
Reload configuration.
3. Modify the inserted directive in pg_hba.conf by replacing it with the
following:
local all postgres,student trust
local all alice,bob peer
Append the following to the end of the pg_ident.conf file:
stmap student alice
4. For alice and bob roles to share a single mapping, the pg_ident.conf file
shall be as follows:
stmap student alice
stmap student bob
