Users, Roles, and Database Security

PostgreSQL is a rock solid database, and it pays great attention to security, providing a very rich infrastructure to handle 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 attached to roles (both users and groups). You will learn how to configure every aspect of a role to carefully manage security, from connection to accessing the data within a database.

However, PostgreSQL goes far beyond this and provides a strong mechanism known as Role Level Security, 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...

Understanding roles

In Chapter 3, Managing Users and Connections, you have seen how to create new roles, a stereotype that can act both 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 have read about in Chapter 3, Managing Users and Connections, introducing 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 [ ... ] ]

Here, an option can be indicated in a positive form, that is, associating the property with the role, or in a negative form with the NO prefix, which removes the property from the 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 statement in order to see what the default value is for every property. If you...

Access control lists

PostgreSQL stores permissions assigned to roles and objects as Access Control Lists (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.

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

grantee=flags/grantor

Here, we see the following:

• grantee is the role name of the role to which the permissions are applied.
• flags is the string representing the permissions.
• grantor is the user who granted the permissions.

Whenever the granted and grantee results in the same name, the role is the owner of the database object.

The flags that can be used in an ACL are those reported in the following table. As you can see, not all the flags apply to all the objects: for example it does not make sense to have...

Granting and revoking permissions

As you have seen in Chapter 3, Managing Users and Connections, a role contains a collection of permissions that are provided by means of a GRANT statement and removed by means of a REVOKE statement. Permissions are stored internally as ACLs, as you have seen 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, permission, ..> ON <database...

Row-level security

In the previous part of the chapter, you have seen the permission mechanism by which PostgreSQL allows roles (both users and groups) to access different objects within the database and data contained in the objects. In particular, with regard to tables, you have 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: row-level security. The idea is that row-level security can decide which tuples the role can gain access to, either in read or write mode. Therefore, if the column-based permissions provides a way of limiting the vertical shape of the tabular data, the 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...

Role password encryption

The 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 md5, which means that the password is computed as MD5 hashes. The only other option available since PostgreSQL 10 is scram-sha-256, which will make the encryption much more robust.

You can quickly check the configuration from the operating system command line:

$ sudo -u postgres grep password_encryption $PGDATA/postgresql.conf
password_encryption = scram-sha-256    # md5 or scram-sha-256

Alternatively, you can inspect the pg_settings system catalog:

forumdb=# SELECT name, setting, enumvals 
         FROM pg_settings 
         WHERE name = 'password_encryption';
        name         |    setting    |      enumvals
--...

SSL connections

The Secure Socket 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. If you or your organization already have certificates, the only thing you have to do is to import the certificate and key files into your PostgreSQL server.

Assuming your certificate and key files are named server.crt and server.key, respectively, you have to configure the following parameters in the postgresql.conf configuration...

Summary

In this chapter, we learned that PostgreSQL provides a very rich infrastructure for managing permissions associated with roles. Internally, PostgreSQL handles permissions on 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 a particular subset 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.

With regard to security, we saw that PostgreSQL allows two different algorithms for password encryption, with SCRAM-SHA-256 being the most modern and robust. Lastly, when opportunely configured, the server can handle network connections via SSL, thereby encrypting all network traffic and...

References

• CREATE ROLE statement official documentation: https://www.postgresql.org/docs/12/sql-createrole.html
• ALTER ROLE statement official documentation: https://www.postgresql.org/docs/12/sql-alterrole.html

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