Access Control
Roles and attributes
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
Roles
Attributes
Group role membership
Object owners
3
Roles
A role is a DBMS user
A role is in no way associated with the OS user
although many programs use the OS user name
as the default role name
Roles are defined at the cluster level
Essentially, a role is a DBMS user. (A role can also act as a user group, as
discussed later in this topic.)
Roles have nothing to do with OS user names, although many stock
programs assume the OS user name as the default role name. For example,
if you do not specify the role name when starting psql, your OS user name
will be used.
Roles are shared cluster objects. As such, one role can connect to different
databases and own objects in different databases.
4
Attributes
Attributes define the properties of a role
CREATE ROLE role [WITH] attribute [attribute ...]
LOGIN can log in
SUPERUSER superuser privileges
CREATEDB can create databases
CREATEROLE can create roles
REPLICATION can use the replication protocol
and others
A role possesses a number of attributes that define its general properties
and rights (not related to object access rights).
Generally, attributes come in two opposite variations, for example,
CREATEDB (can create databases) and NOCREATEDB (not allowed to
create databases). Usually, the restrictive option is the default.
The table lists only some of the possible attributes. The INHERIT and
BYPASSRLS attributes are discussed later in this module.
6
Group roles
Granting group membership to a role
student=> GRANT dba TO alice;
Revoking group membership from a role
student=> REVOKE dba FROM alice;
Group role membership control
any role can grant membership in itself to another role
a role with the SUPERUSER attribute can grant membership
in any role to any role
a role with the CREATEROLE attribute can grant membership
in any non-superuser role to any role
student alice
dba
dba
alice
A role can be granted membership in another role, just as a Unix user can
be included in a group.
PostgreSQL does not distinguish between user roles and group roles,
allowing any role to be a member of any other. Cascading grants may occur,
unless they result in a cycle.
When a role is granted membership in another role, it obtains access to all
the attributes (and privileges, more on them later) of the group role. The
inclusion done with the GRANT command.
The role that executes the GRANT command is paramount. The roles that
may grant (or revoke) membership in a given role are:
●
the role itself,
●
roles with the SUPERUSER attribute,
●
roles with the CREATEROLE attribute (as long as the given role is not a
superuser).
To take advantage of the newly acquired properties, you must first switch to
the role by using the SET ROLE command.
8
Owners
Object owner
the role that created the object
(as well as the role’s members)
can be changed with the ALTER ... OWNER TO role command
When a role creates any objects in a database, it becomes their owner.
In addition to that, any members of this role also become owners of these
objects.
If necessary, the owner of an object can be changed with the ALTER
command for the object with the OWNER TO clause.
The concept of ownership is especially important when discussing
privileges, the next topic of this module.
10
Takeaways
Roles can be considered as both users and groups of users
The properties of a role are defined by its attributes
Roles can be members of other roles
Each database object has an owner role
11
Practice
1. Create a role swan without login privileges, but with the rights to
create databases and roles.
Create a user duckling with login privileges.
2. Verify that duckling cannot create a database.
3. Grant duckling membership in the swan group.
Create a new database as swan.
12
Practice+
1. Create roles alice and bob with login privileges.
Create a table on behalf of alice.
2. Set it up so that both roles could modify the table structure
(for example, add columns with the ALTER TABLE command).
2. Only the owners of a table can change its structure. You need to get not
only Alice, but also Bob to be an owner of the table.
