Users, Roles, and Database Security

PostgreSQL is a rock-solid database, and it pays great attention to security, providing a very rich infrastructure for handling permissions, privileges, and security policies. This chapter builds on the basic concepts introduced in Chapter 3, Managing Users and Connections, revisiting the role concept and extending knowledge with a particular focus on security and privileges granted to roles (a role can be both a user and a group of users). You will learn how to configure every aspect of a role to carefully manage security, from connection to accessing the data within a database.

PostgreSQL also provides a strong mechanism known as Row-Level Security (RLS), which allows a fine-grain definition of policies to mask out part of the data to certain users.

In this chapter, you will also learn about the Access Control List (ACL) and the way PostgreSQL handles permissions internally, which is the result of granting or revoking privileges. Finally...

Technical requirements

The chapter examples can be run on the chapter_10 Docker image that you can find in the book’s GitHub repository: https://github.com/PacktPublishing/Learn-PostgreSQL-Second-Edition.

Understanding roles

In Chapter 3, Managing Users and Connections, you saw how to create new roles, a stereotype that can act either as a single user or a group of users. The CREATE ROLE statement was used to create the role, and you learned about the main properties a role can be associated with.

This section extends the concepts you read about in Chapter 3, Managing Users and Connections, introducing the more interesting and security-related properties of a role.

Just as a quick reminder, the synopsis for creating a new role is the following:

CREATE ROLE name [ [ WITH ] option [ ... ] ]

The name assigned to the role has to be unique within the whole cluster.

An option can be indicated in a positive form, that is, associating a property with a role, or in a negative form with the NO prefix, which removes a property from a role. Some properties are not assigned to new roles by default, so you should take your time and consult the documentation of the CREATE ROLE...

ACLs

PostgreSQL stores permissions assigned to roles and objects as ACLs, and, when needed, it examines the ACLs for a specific role and a database object in order to understand whether the command or query can be performed. In this section, you will learn what ACLs are, how they are stored, and how to interpret them to understand what permissions an ACL provides.

It is important to note that ACLs, and therefore permissions, are strictly tied to the role and the database object, which means that granting a specific permission to an object does not mean that the grantee role will have the same permission within another database, even if an object with the same name and nature exists in that database. For example, permitting a role to run PL/Perl code within a database does not automatically endorse it to run PL/Perl code in other databases.

An ACL is a representation of a group of permissions with the following structure:

grantee=flags/grantor

Where:

• grantee...

Granting and revoking permissions

As you saw in Chapter 3, Managing Users and Connections, a role is associated with a collection of permissions, which are provided by means of a GRANT statement and removed by means of a REVOKE statement. Permissions are stored internally as ACLs, as you saw in the previous section.

This section revisits the GRANT and REVOKE statements to better help you understand how to use them, with respect to different database objects.

The GRANT statement has the following synopsis:

GRANT <permission, permission, ...> ON <database-object> TO <role>;

Here, you list all the permissions you want to associate with the target role for the specified database object. It is also possible to extend the GRANT statement with the WITH GRANT OPTION clause, which will cause the target role to be able to grant the same permissions it has received to another role.

The REVOKE statement has a similar synopsis:

REVOKE <permission,...

RLS

In the previous part of the chapter, you saw the permission mechanism by which PostgreSQL allows roles (both users and groups) to access different objects within the database and the data contained in those objects.

In particular, with regard to tables, you learned how to restrict access to just a specific column list within the tabular data.

PostgreSQL provides another interesting mechanism to restrict access to tabular data: RLS. The idea is that RLS decides which tuples the role can have access to, either in read or write mode. Therefore, if the column-based permissions provide a way of limiting the vertical shape of the tabular data, RLS provides a way to restrict the horizontal shape of the data itself.

When is it appropriate to use RLS? Imagine you have a table that contains data related to users, and you don’t want your users to be able to tamper with other users’ data. In such a case, restricting the access of every user to just their own tuples...

Role password encryption

The login passwords associated with roles are always stored in an encrypted form, even if the role is created without the ENCRYPTED PASSWORD property. PostgreSQL determines the algorithm to use in order to encrypt the password via the password_encryption option in the postgresql.conf configuration file. By default, the value of the option is set to scram-sha-256:

forumdb=> show password_encryption;
password_encryption
---------------------
scram-sha-256
(1 row)

PostgreSQL introduced the SCRAM-SHA-256 encryption algorithm in version 10; before that, the encryption algorithm was set to a less robust md5 one, which is also the only other (but now discouraged) available option.

It is important to note that you cannot change the password encryption algorithm of a live system without resetting all the passwords of the active roles. In other words, if you decide to migrate from an old md5 to a more recent SCRAM-SHA-256 ( or vice versa ), you need...

SSL connections

The Secure Sockets Layer (SSL) allows PostgreSQL to accept encrypted network connections, which means every single piece of data in every packet is encrypted and therefore protected against network spoofing, as long as you handle your keys and certificates appropriately.

In order to enable the SSL extension, you first need to configure the server, then accept incoming SSL connections, and finally instrument the clients to connect in SSL mode.

Configuring the cluster for SSL

In order to let SSL do the encryption, the server must have private and public certificates. Creating and managing certificates is beyond the scope of this book and is a complex topic; you can check the PostgreSQL official documentation for the steps needed to create your own certificates. Once you or your organization have the certificates, the only thing you need to do is import the certificate and key files into your PostgreSQL server.

Assuming your certificate and key files are...

Summary

In this chapter, we learned that PostgreSQL provides a very rich infrastructure for managing permissions associated with roles. Internally, PostgreSQL handles permissions for different database objects by means of ACLs, and every ACL contains information about the set of permissions, the users to whom permissions are granted, and the user who granted such permissions. In terms of tabular data, it is even possible to define column-based permissions and row-level permissions to exclude users from having access to particular subsets of data.

Permissions are granted by nested roles in a dynamically inherited way or on demand, leaving you the option to fine-tune how a role should exploit privileges.

Lastly, when opportunely configured, a server can handle network connections via SSL, thereby encrypting all network traffic and data.

In the next chapter, you will learn all about transactions and how PostgreSQL manages them in a concurrent scenario, providing rock-solid...

Verify your knowledge

• What is a role?

  A role can be a single user or a group of users that have access to the cluster and its databases. A role is the basic unit to grant access and define permissions. See the Understanding roles section for more details.

• What does the INHERITS clause do?

  The INHERITS clause makes a role inherit, that is get, instantly and dynamically all the permissions granted to the role from which it inherits. Without the INHERITS clause, the role still has the permissions of the role it belongs to, but an explicit SET ROLE is required in order to use such permissions. See the Roles that inherit from other roles section for more details.

• What is an Access Control List (ACL)?

  An ACL is the specification of a set of permissions attached to a database object, and is the way PostgreSQL implements and store the permissions. See the ACLs section for more details.

• What are the statements to add...

References

• CREATE ROLE statement official documentation: https://www.postgresql.org/docs/current/sql-createrole.html
• ALTER ROLE statement official documentation: https://www.postgresql.org/docs/current/sql-alterrole.html
• DROP ROLE statement official documentation: https://www.postgresql.org/docs/current/sql-droprole.html
• GRANT statement official documentation: https://www.postgresql.org/docs/current/sql-grant.html
• REVOKE statement official documentation: https://www.postgresql.org/docs/current/sql-revoke.html
• PostgreSQL pg_roles catalog details: https://www.postgresql.org/docs/current/view-pg-roles.html
• PostgreSQL pg_authid catalog details: https://www.postgresql.org/docs/current/catalog-pg-authid.html
• PostgreSQL ACL documentation: https://www.postgresql.org/docs/current/ddl-priv.html
• PostgreSQL host-based access rule details: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
• PostgreSQL ACL utility functions...

Learn more on Discord

To join the Discord community for this book – where you can share feedback, ask questions to the author, and learn about new releases – follow the QR code below:

https://discord.gg/jYWCjF6Tku