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, you will look briefly at the password encryption algorithms that PostgreSQL provides for storing role passwords safely.
This chapter covers the following topics:
- Understanding roles
- ACLs
- Granting and revoking permissions
- RLS
- Role password encryption
- SSL connections
