Access control
Privileges
13
Copyright
© Postgres Professional, 2015–2022
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Alexander Meleshko
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
Topics
Types of privileges for different objects
Role categories in terms of access control
Group privileges
Granting, revoking and transferring privileges
Default privileges
Access control examples
3
Privileges define the access rights of roles to objects
Tables and views
SELECT read data
INSERT insert rows
UPDATE change rows
REFERENCES foreign key
DELETE delete rows
TRUNCATE empty a table
TRIGGER create triggers
Privileges
can be set at the column level
Privileges define the relationships between cluster objects and roles. They
limit the actions the roles can perform on the objects.
The list of possible privileges depends on the object type. Privileges for the
main object types are listed on this and the following slide.
Most privileges are defined for tables and views. Some of them can be
defined not only for the entire relation, but also for individual columns.
4
Privileges
Tablespaces,
databases, schemas
Sequences
SELECT currval
UPDATE nextval setval
USAGE currval nextval
database
schema pg_temp
table
space
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.
5
Role categories
Superusers
full access to all objects, no checks performed
Owners
access within privileges
(initially receives all privileges)
actions that are not regulated by privileges, such as
deleting objects, granting and revoking privileges, etc.
Other roles
access exclusively within the granted privileges
Generally speaking, a role’s ability to access an object is defined by the
role's privileges. However, there are three categories of roles that function
differently in that regard.
1. Roles with the superuser attribute are the most straightforward: they can
do anything and bypass all access control checks.
2. The owner of an object immediately receives a full set of privileges for the
object. Technically, these privileges can be revoked, but the owner always
retains inherent rights to the object 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.
6
Privilege management
Granting privileges
alice=> GRANT privileges ON object TO bob;
the same privilege can be independently granted by multiple roles
Revoking privileges
alice=> REVOKE privileges ON object FROM bob;
alice bob
privileges
on an object
The owner of an object (and the superuser) has the right to grant and
revoke privileges on the object.
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.
8
Group privileges
A role gets the privileges of the group roles it is a member of
the INHERIT attribute makes privileges inherited automatically
with the NOINHERIT attribute, an explicit transition of privileges with the
SET ROLE command is required
The pseudo-role public
implicitly includes all other roles
A role can receive privileges to access an object not only directly, but also
from group roles in which it is included. In order to simplify administration,
you can grant the necessary set of privileges to a group role and then
include users into that role, providing them with the entire set of privileges
at once. A group role can be viewed as a privilege in itself, and group
management is done by the same GRANT and REVOKE commands as
privilege management.
A role with the INHERIT attribute (on by default) will automatically have the
privileges of all the groups it belongs to. This also applies to the pseudo-role
public, which implicitly includes all roles.
If a role is created with the NOINHERIT attribute explicitly added, then it will
have to use the SET ROLE command to switch to the group role in order to
benefit from its privileges. In this case, all actions will be performed on
behalf of the group role (for example, the group role will own any created
objects).
9
Default roles
pg_signal_backend — terminate sessions and cancel queries
pg_read_all_settings — read configuration parameters
pg_read_all_stats — access statistics
pg_stat_scan_tables — access statistics that block access
pg_read_server_iles — read files on the server
pg_write_server_iles — write files on the server
pg_execute_server_programs — run programs on the server
pg_monitor
PostgreSQL has a number of default roles that possess special privileges
required to perform tasks that usually can only be performed by a superuser.
The last three roles were first added in PostgreSQL 11.
A complete list of all roles, including default system roles, can be viewed in
psql with the \duS command.
Similarly, custom group roles can be created, for example, to manage
backups.
11
Right to re-grant
Granting privileges with the right to re-grant
alice=> GRANT privileges ON object TO bob WITH GRANT OPTION;
Revoking privileges
alice=> REVOKE privileges ON object FROM bob CASCADE;
Revoking the right to re-grant
alice=> REVOKE GRANT OPTION FOR
privileges ON object FROM bob CASCADE;
bob
charlie
alice
privileges
dave
p
r
i
v
i
l
e
g
e
s
p
r
i
v
i
l
e
g
e
s
required
if the privilege
was granted
to other roles
When granting certain privileges to a role, you can allow the role to grant (or
revoke) these privileges to other roles down the line. This is done with the
GRANT ... WITH GRANT OPTION command (a similar construction WITH
ADMIN OPTION for attributes was discussed in the topic “Roles” before).
If a role uses this right to re-grant, a hierarchy of roles is formed.
Privileges are revoked with the REVOKE command. A role can revoke only
those privileges that it has granted to others itself. In the example shown on
the slide, alice cannot revoke the privilege directly from charlie or
dave.
However, if the privilege is revoked from bob, it will be automatically
revoked from all the roles down the line in the hierarchy. This requires the
CASCADE keyword (if the hierarchy is not empty, then attempting to revoke
without the CASCADE keyword will return an error).
The right to re-grant can be revoked without revoking the privilege itself.
This is done with the REVOKE GRANT OPTION FOR command. The
CASCADE keyword works here similarly to when revoking a privilege.
12
Question
public
Alice granted the privileges on table T to Bob.
If Alice runs the command
REVOKE ALL ON T FROM bob CASCADE,
what privileges will Charlie and Dave have?
alice
bob
select, update on T
select on T
dave
charlie
select on T
update on T
Both Charlie and Dave will end up with the read privilege that they received
from public. All privileges granted by Bob to other roles will be revoked.
14
Routines
The only privilege for functions and procedures
EXECUTE execution
Security features
SECURITY INVOKER executed with the calling role’s rights
(by default)
SECURITY DEFINER executed with the owner’s rights
The privilege EXECUTE allows users to execute routines (functions and
procedures).
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 executes it. In this case, the operators inside the
routine can access only those objects that the user has the rights to access.
On the other hand, if declared with the SECURITY DEFINER keyword, 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.
15
public role privileges
By default, the public role gets a number of privileges
for databases CONNECT (connect to databases)
TEMPORARY (create temporary tables)
for the public schema CREATE (create objects)
USAGE (access objects)
for pg_catalog USAGE (access objects)
and information_schema
for routines EXECUTE (run routines)
Convenient, but not really secure
By default, the pseudo-role public has a number of privileges (this means
that all roles get them):
●
connecting and creating temporary tables for all databases,
●
using the public schema and creating objects in it,
●
using schemas pg_catalog and information_schema,
●
executing all functions and procedures.
(In PostgreSQL 15, the public role loses the right to create objects.)
Such behavior may be undesirable. In this case, you must explicitly revoke
some of the privileges from public. You can also do so in the template
database template1, so that the changes persist in any newly created
databases. However, revoking routine rights demands the use of the default
privileges mechanism, which will be discussed below.
17
Default privileges
A way to grant or revoke privileges when creating an object
ALTER DEFAULT PRIVILEGES
[ FOR ROLE target_roles_list ]
[ IN SCHEMA schema ]
GRANT privileges ON object_class TO role;
ALTER DEFAULT PRIVILEGES
REVOKE privileges ON object_class FROM role;
REVOKE
EXECUTE ON ROUTINES
FROM public
You can define additional privileges to be granted or revoked when creating
an object. This is done with the ALTER DEFAULT PRIVILEGES command.
The default privileges mechanism triggers when a t arget_role (the current
user by default) creates an object that belongs to the specified object_class
(i. e. a table or a function) in the specified schema (or in any schema by
default). The GRANT clause here means that the created object should be
granted the specified privileges for the specified role. The REVOKE clause,
on the other hand, is used to revoke the privileges.
The default privileges mechanism grants public the privilege to execute
routines when they are created. To avoid this behavior, run the command
ALTER DEFAULT PRIVILEGES
FOR ROLE …
REVOKE EXECUTE ON ROUTINES FROM public;
Note that under the FOR ROLE clause, all the roles that can create routines
must be listed.
19
Example 1
alice
create, usage
alice
bob
anderson
create, usage
anderson
s
e
l
e
c
t
bob
create, usage
s
e
l
e
c
t
Access control mechanisms (roles, attributes and privileges, and schemas)
are flexible enough to let you organize any sort of operations conveniently.
Here’s a simple example.
Professor Anderson and his students Alice and Bob are engaged in
research. They store their research data in a database.
On the university database server, the administrator created a user and a
schema under the same name for each of them. The default research path
was never modified. No group roles are used.
Each user owns the objects they create in their schemas. In addition to that,
Alice and Bob grant access to some of their tables to Professor Anderson so
that he can review their data.
20
Example 2
inventory
usage
inv
fin
financials
usage
picker
carry
bigboss
rich
sterling
A more complex example.
A dedicated DB server runs an ERP system. It comprises an inventory
module and a financial module.
Each module has a schema (inv and fin) and a group role (inventory
and financials). The group roles own all objects in their corresponding
schemas.
The warehouse employs Mr Picker and Mr Putter. Each have a user
included into inventory.
The financial department employs Mrs Rich and Mr Sterling. Each have a
user included into financials.
There is also a user for the CEO included into both roles on the off chance
that he'd try and use the system.
21
Example 3
app
usage
application
support
select on all tables
application
server
web users
User authentication is often set up on an application server outside of the
actual database. With thousands of users and online sign-up functionality,
it is more efficient to offload user management from the database to an
external service.
In this case, the application server connects to the database under one pre-
configured role, and the information about the user is translated as context,
if necessary.
But even in this case, the database still needs some roles: A tech support
role, for example, that will need the rights to read main server tables to
troubleshoot possible issues.
22
Takeaways
Privileges define the access rights of roles to objects
Roles, attributes and privileges, and schemas together form
a flexible mechanism that allows you to set up access control
in different ways
easy to allow access to everything for everyone
can restrict access heavily, if necessary
23
Practice
Set up privileges so that some users have full access to the tables,
while others can only query, but not modify the data.
1. Create a new database and two roles: writer and reader.
2. Revoke all privileges for the schema public from the role public,
grant both privileges to writer, and only the usage privilege to
reader.
3. Set up the default privileges so that reader gets read access to the
tables owned by writer in the schema public.
4. Create users w1 in the writer group and r1 in the reader group.
5. As writer, create a table.
6. Verify that r1 has read-only access to the table, and w1 has full access
to it, including the ability to remove it.
24
Practice+
1. Create the role alice. Create a table.
Grant alice the privilege to read the table and the privilege to
change it with the right to re-grant.
2. View the access rights to the created table using the
table_privileges view in the information schema.
Compare with the \dp command output.
3. View the access rights to the created table using the
has_table_privileges function.
2. Other views of the information schema:
3. Other functions for checking privileges:
