Chapter 11. PostgreSQL Security

Data protection and security are essential for the continuity of business. Data protection is not nice to have, but it is required by the legal system. Sensitive data, such as user information, email addresses, geographical addresses, and payment information, should be protected against any data breach. There are several other topics related to data security, such as data privacy, retention, and loss prevention.

There are several levels of data protection, often defined in the data protection policy and by the country's legal system. Data protection policy often defines data dissemination to other parties, users authorized to access the data, and so on. Data should be protected on different levels, including transferring and encrypting data on storage devices. Data security is a huge topic and often there are data security managers dedicated only to these tasks.

In this chapter we will discuss the following topics:

• Authentication in PostgreSQL including PostgreSQL...

Authentication in PostgreSQL

Authentication answers the question: who is the user? PostgreSQL supports several authentication methods, including

• trust: anyone who can connect to the server is authorized to access the database / databases as specified in the pg_hba.conf configuration file. Often used to allow connection using Unix-domain socket on a single user machine to access the database. One can also use this method with TCP/IP , but it is rare to allow connection from any IP address other than localhost. 
• ident: it works by getting the client's operating system user name from an ident server and then using it in accessing the database server. This method is recommend only for closed networks where client machines has a tight control by system administrators.
• peer: it works similar to ident, but the client's operating system user name is obtained from the kernel.
• GSSAPI: GSSAPI is an industry standard defined in RFC 2743, it provides automatic authentication (single sign-on).
• LDAP: LDAP ...

Authentication best practices

Authentication best practices depend on the whole infrastructure setup, the application's nature, the user's characteristics, data sensitivity, and so on. For example, the following setup is common for start-up companies: the database application, including the database server, is hosted on the same machine and only used from one physical location by intracompany users.

Often, database servers are isolated from the world using firewalls; in this case, one can use the scram-sha-256 authentication method and limit the IP addresses so that the database server accepts connections within a certain range or set. Note that it is important not to use a superuser or database owner account to connect to the database, because if this account were hacked, the whole database cluster would be exposed.

If the application server—business logic—and database server are not on the same machine, one can use a strong authentication method, such as LDAP (Lightweight Directory Access...

PostgreSQL default access privileges

By default, PostgreSQL users—also known as roles with the login option—can access the public schema. Additionally, note that the default PostgreSQL authentication policy allows users to access all databases from the localhost using peer authentication on a Linux system. Users can create database objects -tables, views, functions and so on- in the public schema of any database that they can access by default. Finally, the user can alter some settings regarding sessions such as work_mem.

The user cannot access other user objects in the public schema or create databases and schemas. However, the user can sniff data about the database objects by querying the system catalog. Unprivileged users can get information about other users, table structure, table owner, some table statistics, and so on.

The following example shows how the user test_user is able to get information about a table, which is owned by a postgres user; to simulate this situation, let's create...

Role system and proxy authentication

Often, when designing an application, a user is used to configure database connections and connection tools. Another level of security needs to be implemented to ensure that the user who uses the application is authorized to perform a certain task. This logic is often implemented in application business logic. The database's role system can also be used to partially implement this logic by delegating the authentication to another role after the connection is established or reused, using the SET SESSION AUTHORIZATION statement or SET ROLE command in a transaction block:

postgres=# SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 postgres | postgres
(1 row)

postgres=# SET SESSION AUTHORIZATION test_user;
SET
postgres=> SELECT session_user, current_user;
 session_user | current_user 
--------------+--------------
 test_user | test_user
(1 row)

The SET ROLE requires a role membership, while SET SESSION AUTHORIZATION...

PostgreSQL security levels

PostgreSQL has different security levels defined on PostgreSQL objects, including tablespace, database, schema, table, foreign data wrapper, sequence, domain, language, and large object. One can have a peek into different privileges by running the \h meta command in psql, as follows:

Command: GRANT
Description: define access privileges
Syntax:
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

Database security level

To disallow users from connecting to the database by default, one needs to revoke the default database permissions from public, as follows:

$ psql car_portal -U postgres
psql (10.0)
Type "help" for help.
car_portal=# REVOKE ALL ON DATABASE car_portal FROM public;
REVOKE
car_portal=# \q
$ psql car_portal -U web_app_user
psql: FATAL: permission denied...

Encrypting data

By default, PostgreSQL internally encrypts sensitive data, such as roles' passwords. However, database users can also encrypt and decrypt sensitive data using the pgcrypto extension.

PostgreSQL role password encryption

When creating a role with password and login options, one can see the role's details in the pg_shadow catalog relation. Note that it is not recommended to use the following format to create the password:

CREATE ROLE <role_name> <with options> PASSWORD 'some_password';

The CREATE ROLE statement can appear in pg_stat_activity as well as the server logs, as follows:

postgres=# SELECT query FROM pg_stat_activity;
                query 
--------------------------------------


 SELECT query FROM pg_stat_activity;
 create role c password 'c';

All passwords stored in pg_shadow are encrypted with salt; finally, renaming an account will rest the password as follows:

postgres=# ALTER ROLE a RENAME TO b;
NOTICE: MD5 password cleared because of role rename

When creating...

Summary

In this chapter, PostgreSQL security is tackled from the authorization, authentication, and data encryption aspects; however, one also should protect the code against SQL injection and other known security issues, such as function cost, and the security barrier options. PostgreSQL provides several authentication methods, such as password and trust. Also, it provides security levels on all database objects including the database itself, schemas, tables, views, function, columns, and rows. Finally, one can also store sensitive data in the database in an encrypted format using pgcrypto extension. 

The next chapter will focus on the PostgreSQL system catalog and introduce several recipes to maintain the database. The recipes will be used to extract potential problems in the database, such as missing indexes, and introduce the solutions to tackle these problems.