Chapter 6: Security

This chapter will present a few common recipes for securing your database server. Taken together, these will cover the main areas around security in PostgreSQL that you should be concerned with. The last recipe will cover some cloud-specific topics.

This chapter covers the following recipes:

• Overview of PostgreSQL security
• The PostgreSQL superuser
• Revoking user access to a table
• Granting user access to a table
• Granting user access to specific columns
• Granting user access to specific rows
• Creating a new user
• Temporarily preventing a user from connecting
• Removing a user without dropping their data
• Checking whether all users have a secure password
• Giving limited superuser powers to specific users
• Auditing database access
• Always knowing which user is logged in
• Integrating with Lightweight Directory Access Protocol (LDAP)
• Connecting using encryption SSL GSS API)
• Using SSL certificates to authenticate...

Overview of PostgreSQL security

Security is a huge area of related methods and technologies, so we will take a practical approach, covering the most common issues related to database security.

First, we set up access rules in the database server. PostgreSQL allows you to control access based upon the host that is trying to connect, using the pg_hba.conf file. You can specify SSL/GSSAPI connections if needed or skip that if the network is secure. Passwords are encrypted using SCRAM-SHA-256, but many other authentication methods are available.

Next, set up the role and privileges for accessing your data. Modern databases should be configured using the principle of least privilege (POLP). Data access is managed by a privilege system, where users are granted different privileges for different tables or other database objects, such as schemas or functions. Thus, some records or tables can only be seen by certain users, and even those tables that are visible to everyone can have restrictions...

The PostgreSQL superuser

A PostgreSQL superuser is a user that bypasses all permission checks, except the right to log in. Superuser is a dangerous privilege and should not be used carelessly, and many cloud databases do not allow this level of privilege to be granted at all. It is normal to place strict controls on users of this type. If you are using PostgreSQL in a cloud service, then please read the Setting up cloud security using predefined roles recipe instead.

In this recipe, you will learn how to grant the right to a user to become a superuser.

How to do it…

Follow the next steps to add or remove superuser privileges for any user.

• A user becomes a superuser when they are created with the SUPERUSER attribute set:

  CREATE USER username SUPERUSER;

• A user can be deprived of their superuser status by removing the SUPERUSER attribute using this command:

  ALTER USER username NOSUPERUSER;

• A user can be restored to superuser status later using the following...

Revoking user access to a table

This recipe answers the question How do I make sure that user X cannot access table Y?

Getting ready

The current user must either be a superuser, the owner of the table, or a user with a GRANT option for the table.

Also, bear in mind that you can't revoke rights from a user who is a superuser.

How to do it…

To revoke all rights on the table1 table from the user2 user, you must run the following SQL command:

REVOKE ALL ON table1 FROM user2;

However, if user2 has been granted another role that gives them some rights on table1—say, role3—this command is not enough; you must also choose one of the following options:

• Fix the user—that is, revoke role3 from user2
• Fix the role—that is, revoke privileges on table1 from role3

Both choices are imperfect because of their side effects. The former will revoke all of the privileges associated with role3, not just the privileges concerning...

Granting user access to a table

A user needs to have access to a table in order to perform any actions on it.

Getting ready

Make sure that you have the appropriate roles defined and that privileges are revoked from the PUBLIC role:

CREATE GROUP webreaders;
CREATE USER tim;
CREATE USER bob;
REVOKE ALL ON SCHEMA someschema FROM PUBLIC;

How to do it…

We had to grant access to the schema in order to allow access to the table. This suggests that access to a given schema can be used as a fast and extreme way of preventing any access to any object in that schema. Otherwise, if you want to allow some access, you must use specific GRANT and REVOKE statements, as needed:

GRANT USAGE ON SCHEMA someschema TO webreaders;

It is often desirable to give a group of users similar permissions to a group of database objects. To do this, you first assign all the permissions to a proxy role (also known as a permission group), and then assign the group to selected users, as follows...

Granting user access to specific columns

A user can be given access to only some table columns.

Getting ready

We will continue the example from the previous recipe, so we assume that there is already a schema called someschema and a role called somerole with USAGE privileges on it. We create a new table on which we will grant column-level privileges:

CREATE TABLE someschema.sometable2(col1 int, col2 text);

How to do it…

We want to grant somerole the ability to view existing data and insert new data; we also want to provide the ability to amend existing data, limited to the col2 column only. We use the following self-evident statements:

GRANT SELECT, INSERT ON someschema.sometable2
TO somerole; 
GRANT UPDATE (col2) ON someschema.sometable2
TO somerole;

We can then test whether this has worked successfully, as follows:

1. Let's assume the identity of the somerole role and test these privileges with the following commands:

   SET ROLE TO somerole; 
   INSERT...

Granting user access to specific rows

PostgreSQL supports granting privileges on a subset of rows in a table using RLS.

Getting ready

Just as we did for the previous recipe, we assume that there is already a schema called someschema and a role called somerole with USAGE privileges on it. We create a new table to experiment with row-level privileges:

CREATE TABLE someschema.sometable3(col1 int, col2 text);

RLS must also be enabled on that table:

ALTER TABLE someschema.sometable3 ENABLE ROW LEVEL SECURITY;

How to do it…

First, we grant somerole the privilege to view the contents of the table, as we did in the previous recipe:

GRANT SELECT ON someschema.sometable3 TO somerole;

Let's assume that the contents of the table are as shown by the following command:

SELECT * FROM someschema.sometable3;
 col1 |   col2   
------+-----------
    1 | One
   -1 | Minus one
(2 rows)

In order...

Creating a new user

In this recipe, we will show you two ways of creating a new database user—one with a dedicated command-line utility and another using SQL commands.

Getting ready

To create new users, you must either be a superuser or have the CREATEROLE or CREATEROLE privilege.

How to do it...

From the command line, you can run the createuser command:

pguser@hvost:~$ createuser bob

If you add the --interactive command-line option, you activate the interactive mode, which means you will be asked some questions, as follows:

pguser@hvost:~$ createuser --interactive alice
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n

Without --interactive, the preceding questions get no as the default answer; you can change that with the -s, -d, and -r command-line options.

In interactive mode, questions are asked only if they make sense. One example...

Temporarily preventing a user from connecting

Sometimes, you need to temporarily revoke a user's connection rights without actually deleting the user or changing the user's password. This recipe presents ways of doing this.

Getting ready

To modify other users, you must either be a superuser or have the CREATEROLE privilege (in the latter case, only non-superuser roles can be altered).

How to do it…

Follow these steps to temporarily prevent and reissue the logging-in capability to a user:

1. To temporarily prevent the user from logging in, run this command:

   pguser=# alter user bob nologin;
   ALTER ROLE

2. To let the user connect again, run the following command:

   pguser=# alter user bob login;
   ALTER ROLE

How it works...

This sets a flag in the system catalog, telling PostgreSQL not to let the user log in. It does not kick out already connected users.

There's more…

Here are some additional remarks.

Limiting the number of concurrent...

Removing a user without dropping their data

When trying to drop a user who owns some tables or other database objects, you get the following error, and the user is not dropped:

testdb=# drop user bob;
ERROR:  role "bob" cannot be dropped because some objects depend on it
DETAIL:  owner of table bobstable
owner of sequence bobstable_id_seq

This recipe presents two solutions to this problem.

Getting ready

To modify users, you must either be a superuser or have the CREATEROLE privilege.

How to do it…

The easiest solution to this problem is to refrain from dropping the user and use the trick from the Temporarily preventing a user from connecting recipe to prevent the user from connecting:

pguser=# alter user bob nologin;
ALTER ROLE

This has the added benefit of the original owner of the table being available later, if needed, for auditing or debugging purposes (Why is this table here? Who created it?).

Then, you can assign...

Checking whether all users have a secure password

By default, as of PostgreSQL 14, passwords are encrypted using the SCRAM-SHA-256 login method for users, which was added in PostgreSQL 10. Any servers upgrading from earlier versions should upgrade from MD5 to SCRAM-SHA-256 password encryption since the MD5 authentication method is considered insecure for many applications.

For client applications connecting from trusted private networks, either real or a virtual private network (VPN), you may use host-based access, provided you know that the machine on which the application is running is not used by some non-trusted individuals. For remote access over public networks, it may be a better idea to use SSL client certificates. See the later recipe, Using SSL certificates to authenticate, for more on this.

How to do it…

To see which users don't yet have SCRAM-encrypted passwords, use this query:

test2=# select usename,passwd from pg_shadow where passwd
not like ...

Giving limited superuser powers to specific users

The superuser role has some privileges that can also be granted to non-superuser roles separately.

To give the bob role the ability to create new databases, run this:

ALTER ROLE BOB WITH CREATEDB;

To give the bob role the ability to create new users, run the following command:

ALTER ROLE BOB WITH CREATEROLE;

Note that the PostgreSQL documentation warns against doing the preceding action:

"Be careful with the CREATEROLE privilege. There is no concept of inheritance for the privileges of a CREATEROLE-role. That means that even if a role does not have a certain privilege but is allowed to create other roles, it can easily create another role with different privileges than its own (except for creating roles with superuser privileges). For example, if the role "user" has the CREATEROLE privilege but not the CREATEDB privilege, nonetheless it can create a new role with the CREATEDB privilege. Therefore, regard...

Auditing database access

Auditing database access is a much bigger topic than you might expect because it can cover a whole range of requirements.

Getting ready

First, decide which of these you want and look at the appropriate subsection:

• Which privileges can be executed? (Auditing access)
• Which SQL statements were executed? (Auditing SQL)
• Which tables were accessed? (Auditing table access)
• Which data rows were changed? (Auditing data changes)
• Which data rows were viewed? (Not described here—usually too much data)

Auditing just SQL produces the lowest volume of audit log information, especially if you choose to log only data definition language (DDL). Higher levels accumulate more information very rapidly, so you may quickly decide not to do this in practice. Read each section to understand the benefits and trade-offs.

Auditing access

Reviewing which users have access to which information is important. There are a few ways of doing...

Always knowing which user is logged in

In the preceding recipes, we just logged the value of the user variable in the current PostgreSQL session to log the current user role.

This does not always mean that this particular user was the user that was actually authenticated at the start of the session. For example, a superuser can execute the SET ROLE TO ... command to set its current role to any other user or role in the system. As you might expect, non-superusers can only assume roles that they own.

It is possible to differentiate between the logged-in role and the assumed role using the current_user and session_user session variables:

postgres=# select current_user, session_user;
current_user | session_user
-------------+--------------
postgres     | postgres 
postgres=# set role to bob;
SET
postgres=> select current_user, session_user;
current_user | session_user
-------------+--------------
bob        ...

Integrating with LDAP

This recipe shows you how to set up your PostgreSQL system so that it uses the LDAP for authentication.

Getting ready

Ensure that the usernames in the database and your LDAP server match, as this method works for user authentication checks of users who are already defined in the database.

How to do it…

In the pg_hba.conf PostgreSQL authentication file, we define some address ranges to use LDAP as an authentication method, and we configure the LDAP server for this address range:

host    all         all         10.10.0.1/16          ldap \
ldapserver=ldap.our.net ldapprefix="cn=" ldapsuffix=", 
   dc=our,dc=net"

How it works…

This setup makes the PostgreSQL server check passwords from the configured LDAP server.

User rights are not queried...

Connecting using encryption (SSL/GSSAPI)

Here, we will demonstrate how to enable PostgreSQL to use SSL for the protection of database connections by encrypting all of the data passed over that connection. Using SSL makes it much harder to sniff the database traffic, including usernames, passwords, and other sensitive data. Otherwise, everything that is passed unencrypted between a client and the database can be observed by someone listening to a network somewhere between them. An alternative to using SSL is running the connection over a VPN.

Using SSL makes the data transfer on the encrypted connection a little slower, so you may not want to use it if you are sure that your network is safe. The performance impact can be quite large if you are creating lots of short connections, as setting up an SSL connection is quite central-processing unit (CPU)-heavy. In this case, you may want to run a local connection-pooling solution, such as PgBouncer, to which the client connects without...

Using SSL certificates to authenticate

This recipe shows you how to set up your PostgreSQL system so that it requires clients to present a valid X.509 certificate before allowing them to connect.

This can be used as an additional security layer, using double authentication, where the client must both have a valid certificate to set up the SSL connection and know the database user's password. It can also be used as the sole authentication method, where the PostgreSQL server will first verify the client connection using the certificate presented by the client, and then retrieve the username from the same certificate.

Getting ready

Get, or generate, a root certificate and a client certificate to be used by the connecting client.

How to do it…

For testing purposes, or for setting up a single trusted user, you can use a self-signed certificate:

openssl genrsa  2048  >  client.key
openssl req  -new -x509 -key ...

Mapping external usernames to database roles

In some cases, the authentication username is different from the PostgreSQL username. For instance, this can happen when using an external system for authentication, such as certificate authentication (as described in the previous recipe), or any other external or single sign-on (SSO) system authentication method from http://www.postgresql.org/docs/current/static/auth-methods.html (GSSAPI, Security Support Provider Interface (SSPI), Kerberos, Radius, or Privileged Access Management (PAM)). You may just need to enable an externally authenticated user to connect as multiple database users. In such cases, you can specify rules to map the external username to the appropriate database role.

Getting ready

Prepare a list of usernames from the external authentication system and decide which database users they are allowed to connect as—that is, which external users map to which database users.

How to do it…

Create a pg_ident...

Using column-level encryption

The user can encrypt data in the database so that it is not visible to the hosting provider. In general, this means that the data cannot then be used for searching or indexing, unless you use homomorphic encryption.

The strictest form of encryption would be client-side encryption so that all the database knows about is a blob of data, which would then normally be stored in a bytea database column, but could be others.

Data can also be encrypted server-side before it is returned to the user using the pgcrypto contrib package provided as an extension with PostgreSQL.

Getting ready

Make sure you (and/or your database server) are in a country where encryption is legal—in some countries, it is either banned completely or a license is required.

In order to create and manage Pretty Good Privacy (PGP) keys, you also need the well-known GnuPG command-line utility, which is available on practically all distributions.

pgcrypto is part of...

Setting up cloud security using predefined roles

Many databases as a service (DBaaS)/database clouds restrict the use of superusers, with good reason. Administrators in a database cloud need to use an intermediate level of authority.

For example, in the EDB BigAnimal cloud service, a user called edb_admin holds most privileges, including CREATEROLE and CREATEDB. BigAnimal runs within your own account on cloud platforms, so the service provides data isolation, which in turn makes it easier and safer to administer than other clouds.

In prior releases of PostgreSQL, many functions were superuser-only, but these functions and views are now just superuser-by-default.

Rather than have administrators work out for themselves how to set up admin privileges, PostgreSQL now provides predefined roles, previously known as default roles, that can be thought of as useful groupings of privileges to grant to different types of administrators.

Getting ready

Set up a cloud account (for...