Managing PostgreSQL Security

In Chapter 7, Writing Stored Procedures, we learned about stored procedures and writing server-side code. After being introduced to many other important topics, it is now time to shift to PostgreSQL security. Here, we will learn how to secure a server and configure permissions to avoid security breaches. Security is an important aspect and is getting increasingly important as applications are exposed to more and more internal, as well as external, threats.

The following topics will be covered in this chapter:

• Managing network security
• Digging into row-level security
• Inspecting permissions
• Reassigning objects and dropping users

By the end of the chapter, we will be able to professionally configure PostgreSQL security. Let’s now start off with managing network security.

Managing network security

Security is a complex thing and we have to approach this topic systematically to ensure success. One of the key aspects is network security, which is the first topic we want to focus on. However, before we dive head-on into network security, it makes sense to find a mental model to handle PostgreSQL security in general.

Here is my personal mental model, which has served me well over the years:

• 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 more
• Schema-level permissions: Using a schema and creating objects inside a schema
• Table-level permissions: Selecting, inserting, updating, and more
• Column-level permissions: Allowing or restricting access to columns
• RLS: Restricting access to rows

In order to read a value...

Digging into row-level security

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

Consider the following real-world example, where 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 their 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 all the data in their part of the company.

RLS has been designed to do exactly this and enables you to build multi...

Inspecting permissions

When all of the permissions have been 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, let’s assign rights to the role named joe so that we can inspect them in the next step:

test=# GRANT ALL ON t_person TO joe;
GRANT

Information about permissions can be retrieved 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 on various levels, it can happen that users are dropped from the system. Unsurprisingly, the commands to do this are the DROP ROLE and DROP USER commands. Here is the syntax of DROP ROLE:

test=# \h DROP ROLE
Command: DROP ROLE
Description: remove a database role
Syntax:
 DROP ROLE [ IF EXISTS ] name [, ...]
 URL: https://www.postgresql.org/docs/14/sql-droprole.html

Once the syntax of DROP ROLE has been discussed, we can give it a try. The following listing shows how this works:

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...

Summary

Database security is a wide field, and a single chapter can hardly cover all of the aspects of PostgreSQL security. Many things, such as SELinux and SECURITY DEFINER/INVOKER, were left untouched. However, in this chapter, we learned about the most common things that we will face as PostgreSQL developers and database administrators. We also learned how to avoid the basic pitfalls, and how to make our systems more secure. What is important to understand is that security does matter and that PostgreSQL provides you with all the means necessary to secure your database.

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

Questions

Here are a few questions to test your knowledge:

• How can you configure network access to PostgreSQL?
• What is a user and what is a role?
• How can a password be changed?
• What is RLS?

The answers to these questions can be found in the GitHub repository (https://github.com/PacktPublishing/Mastering-PostgreSQL-15-).