Managing PostgreSQL Security

Chapter 7, Writing Stored Procedures, was all about stored procedures and writing server-side code. After introducing you to many important topics, it is now time to shift our focus to PostgreSQL security. You will learn how to secure a server and configure permissions.

The following topics will be covered:

• Configuring network access
• Managing authentication
• Handling users and roles
• Configuring database security
• Managing schemas, tables, and columns
• Row-level security

At the end of the chapter, you will be able to write good and efficient procedures.

Managing network security

Before moving on to real-world, practical examples, I want to briefly shift your attention to the various layers of security we will be dealing with. When dealing with security, it makes sense to keep those levels in mind in order to approach security-related issues in an organized way.

Here is my mental model:

• Bind addresses: listen_addresses in the postgresql.conf file
• Host-based access control: The pg_hba.conf file
• Instance-level permissions: Users, roles, database creation, login, and replication
• Database-level permissions: Connecting, creating schemas, and so on
• Schema-level permissions: Using schemas and creating objects inside a schema
• Table-level permissions: Selecting, inserting, updating, and so on
• Column-level permissions: Allowing or restricting access to columns
• Row-level security: Restricting access to rows

In order to read a value, PostgreSQL...

Digging into row-level security - RLS

Up to this point, a table has always been shown as a whole. When the table contained 1 million rows, it was possible to retrieve 1 million rows from it. If somebody had the rights to read a table, it was all about the entire table. In many cases, this is not enough. Often, it is desirable that a user is not allowed to see all the rows.

Consider the following real-world example: an accountant is doing accounting work for many people. The table containing tax rates should really be visible to everybody as everybody has to pay the same rates. However, when it comes to the actual transactions, the accountant might want to ensure that everybody is only allowed to see his or her own transactions. Person A should not be allowed to see person B's data. In addition to that, it might also make sense that the boss of a division is allowed to see...

Inspecting permissions

When all permissions are set, it is sometimes necessary to know who has which permissions. It is vital for administrators to find out who is allowed to do what. Unfortunately, this process is not so easy and requires a bit of knowledge. Usually, I am a big fan of command-line usage. However, in the case of the permission system, it can really make sense to use a graphical user interface to do things.

Before I show you how to read PostgreSQL permissions, I will assign rights to the joe role so that we can inspect them in the next step:

test=# GRANT ALL ON t_person TO joe;  
GRANT 

Retrieving information about permissions can be done using the z command in psql:

test=# \x 
Expanded display is on.  
test=# \z t_person 
Access privileges 
-[ RECORD 1 ]-----+-------------------------------------------------------- 
---- 
Schema            | public 
Name      ...

Reassigning objects and dropping users

After assigning permissions and restricting access, it can happen that users will be dropped from the system. Unsurprisingly, the commands to do that are the DROP ROLE and DROP USER commands:

test=# \h DROP ROLE  
Command:  DROP ROLE 
Description: remove a database role 
Syntax: 
DROP ROLE  [ IF EXISTS ] name  [, ...] 

Let's give it a try:

test=# DROP ROLE joe; 
ERROR:  role  "joe"  cannot be dropped because some  objects depend on it 
DETAIL:  target of policy joe_pol_3 on table t_person  
target of policy joe_pol_2 on table t_person 
target of policy joe_pol_1 on table t_person  
privileges for table t_person 
owner of table t_user 
owner of sequence t_user_id_seq 
owner of default privileges on new relations belonging to role joe in schema public 
owner of table t_useful 

PostgreSQL will issue error messages because a user...

Summary

Database security is a wide field, and a 30-page chapter can hardly cover all the aspects of PostgreSQL security. Many things, such as SELinux, security definer/invoker, and so on, were left untouched. However, in this chapter, you learned the most common things you will face as a PostgreSQL developer and DBA. You learned how to avoid the basic pitfalls and how to make your systems more secure.

In Chapter 9, Handling Backup and Recovery, you will learn about PostgreSQL streaming replication and incremental backups. The chapter will also cover failover scenarios.