17 min read

When dealing with security, it makes sense to keep those levels in mind in order to approach security-related issues in an organized way.

  • 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 schema 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 has to ensure that we have sufficient permissions on every level. The entire chain of permissions has to be correct.

This article is an excerpt taken from the book Mastering PostgreSQL 11 – Second Edition by Hans-Jürgen Schönig. In this book, you will learn the approach to get to grips with advanced PostgreSQL 11 features and SQL functions, master replication and failover techniques, configure database security and more. In this article, you will learn the process of handling SSL, column-level security and configuring default privileges and much more.

Understanding bind addresses and connections

When configuring a PostgreSQL server, one of the first things that needs to be done is define remote access. By default, PostgreSQL does not accept remote connections. The important thing here is that PostgreSQL does not even reject the connection because it simply does not listen on the port. If we try to connect, the error message will actually come from the operating system because PostgreSQL does not care at all.

Assuming that there is a database server using the default configuration on 192.168.0.123, the following will happen:

iMac:~ hs$ telnet 192.168.0.123 5432 
Trying 192.168.0.123... 
telnet: connect to address 192.168.0.123: Connection refused 
telnet: Unable to connect to remote host

Telnet tries to create a connection on port 5432 and is instantly rejected by the remote box. From the outside, it looks as if PostgreSQL is not running at all.

The key to success can be found in the postgresql.conf file:

# - Connection Settings - 

# listen_addresses = ‘localhost’
# what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to ‘localhost’; use ‘*’ for all
# (change requires restart)

The listen_addresses setting will tell PostgreSQL which addresses to listen on. Technically speaking, those addresses are bind addresses. What does that actually mean? Suppose we have four network cards in our machine. We can listen on, say, three of those IP addresses. PostgreSQL takes requests to those three cards into account and does not listen on the fourth one. The port is simply closed.

If we put an * in, PostgreSQL will listen to every IP assigned to your machine.

However, there are more settings related to connection management that are highly important to understand. They are as follows:

#port = 5432 
              # (change requires restart) 
max_connections = 100 
              # (change requires restart) 
# Note: Increasing max_connections costs ~400 bytes of 
# shared memory per 
# connection slot, plus lock space 
# (see max_locks_per_transaction). 
#superuser_reserved_connections = 3 
              # (change requires restart) 
#unix_socket_directories = '/tmp' 
              # comma-separated list of directories 
              # (change requires restart) 
#unix_socket_group = '' 
              # (change requires restart) 
#unix_socket_permissions = 0777 
              # begin with 0 to use octal notation 
              # (change requires restart)

First of all, PostgreSQL listens to a single TCP port, the default value of which is is 5432. Keep in mind that PostgreSQL will listen on a single port only. Whenever a request comes in, the postmaster will fork and create a new process to handle the connection. By default, up to 100 normal connections are allowed. On top of that, three additional connections are reserved for superusers. This means that we can either have 97 connections plus three superusers or 100 superuser connections.

Handling SSL

PostgreSQL allows us to encrypt the transfer between the server and the client. Encryption is highly beneficial, especially if we are communicating over long distances. SSL offers a simple and secure way to ensure that nobody is able to listen to your communication.

In this section, we will learn how to set up SSL.

The first thing to do is to set the ssl parameter to on in the postgresql.conf file when the server starts. In the next step, we can put SSL certificates into the $PGDATA directory. If we don’t want the certificates to be in some other directory, change the following parameters:

#ssl_cert_file = 'server.crt'    # (change requires restart) 
#ssl_key_file = 'server.key'     # (change requires restart) 
#ssl_ca_file = ''                # (change requires restart) 
#ssl_crl_file = ''               # (change requires restart)

If we want to use self-signed certificates, perform the following step:

openssl req -new -text -out server.req

Answer the questions asked by OpenSSL. Make sure that we enter the local hostname as common name. We can leave the password empty. This call will generate a key that is passphrase protected; it will not accept a passphrase that is less than four characters long.

To remove the passphrase (as you must if you want automatic startup of the server), run the following commands:

openssl rsa -in privkey.pem -out server.key
rm privkey.pem

Enter the old passphrase to unlock the existing key. Now, do this to turn the certificate into a self-signed certificate and to copy the key and certificate to where the server will look for them:

openssl req -x509 -in server.req -text
  -key server.key -out server.crt

After doing this, make sure that the files have the right set of permissions:

chmod og-rwx server.key

Once the proper rules have been put into the pg_hba.conf file, we can use SSL to connect to your server. To verify that we are indeed using SSL, consider checking out the pg_stat_ssl function. It will tell us every connection and whether it uses SSL or not, and it will provide some important information about encryption:

test=# \d pg_stat_sslView  "pg_catalog.pg_stat_ssl"
Column | Type | Modifiers -------------+----------+----------- pid | integer | ssl | boolean | version | text | cipher | text | bits | integer | compression | boolean |

clientdn     | text     |

If the ssl field for a process contains true; PostgreSQL does what we would expect it to do:

postgres=# select * from pg_stat_ssl; 
-[ RECORD 1 ] 
---------------------------- 
pid         | 20075
ssl         | t 
version     | TLSv1.2
cipher      | ECDHE-RSA-AES256-GCM-SHA384 
bits        | 256
compression | f 
clientdn    |

Handling instance-level security

So far, we have configured bind addresses and we have told PostgreSQL which means of authentication to use for which IP ranges. Up to now, the configuration was purely network-related.

In the next step, we can shift our attention to permissions at the instance level. The most important thing to know is that users in PostgreSQL exist at the instance level. If we create a user, it is not just visible inside one database; it can be seen by all the databases. A user might have permissions to access just a single database, but basically users are created at the instance level.

To those of you who are new to PostgreSQL, there is one more thing you should keep in mind: users and roles are the same thing. CREATE ROLE and CREATE USER clauses have different default values (literally, the only difference is that roles do not get the LOGIN attribute by default), but at the end of the day, users and roles are the same. Therefore, CREATE ROLE and CREATE USER clauses support the very same syntax:

test=# \h CREATE USER
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:

SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid

Let’s discuss those syntax elements one by one. The first thing we see is that a user can be a superuser or a normal user. If somebody is marked as a SUPERUSER , there are no longer any restrictions that a normal user has to face. A SUPERUSER can drop objects (databases and so on) as they wish.

The next important thing is that it takes permissions on the instance level to create a new database.

The rule is this: the creator is always automatically the owner of an object (unless specified otherwise, as can be done with the CREATE DATABASE clause). The beauty is that object owners can also drop an object again.

The next important thing is the INHERIT/NOINHERIT clause. If the INHERIT clause is set (which is the default value), a user can inherit permissions from some other user. Using inherited permissions allows us to use roles, which is as a good way to abstract permissions. For example, we can create the role of bookkeeper and make many other roles inherit from bookkeeper. The idea is that we only have to tell PostgreSQL once what a bookkeeper is allowed to do, even if we have many people working in accounting.

The LOGIN/NOLOGIN clause defines whether a role is allowed to log in to the instance.

After this theoretical introduction, it is time to actually create users and see how things can be used in a practical example:

test=# CREATE ROLE  bookkeeper NOLOGIN;  
CREATE ROLE 
test=# CREATE ROLE  joe LOGIN;  
CREATE ROLE 
test=# GRANT  bookkeeper TO joe;  
GRANT ROLE

The first thing done here is that a role called bookkeeper is created.

Note that we don’t want people to log in as bookkeeper, so the role is marked as NOLOGIN.

Also note that NOLOGIN is the default value if you use the CREATE ROLE clause. If you prefer the CREATE USER clause, the default setting is LOGIN.

Then, the joe role is created and marked as LOGIN. Finally, the bookkeeper role is assigned to the joe role so that he can do everything a bookkeeper is actually allowed to do.

Once the users are in place, we can test what we have so far:

[hs@zenbook ~]$ psql test -U bookkeeper 
psql: FATAL:  role "bookkeeper" is not permitted to log in

As expected, the bookkeeper role is not allowed to log in to the system. What happens if the joe role tries to log in?

    [hs@zenbook ~]$ psql test -U joe
    ... 
    test=>

This will actually work as expected. However, note that Command Prompt has changed. This is just a way for PostgreSQL to show you that you are not logged in as a superuser.

Once a user has been created, it might be necessary to modify it. One thing we might want to change is the password. In PostgreSQL, users are allowed to change their own passwords. Here is how it works:

test=> ALTER  ROLE  joe PASSWORD 'abc';  
ALTER  ROLE 
test=> SELECT current_user; 
 current_user 
--------------  
 joe 
(1 row)

The ALTER ROLE clause (or ALTER USER) will allow us to change most settings which can be set during user creation. However, there is even more to managing users. In many cases, we want to assign special parameters to a user. The ALTER USER clause gives us the means to do that:

ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] 
              SET configuration_parameter { TO | = } { value | DEFAULT } 
ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] 
              SET configuration_parameter FROM CURRENT 
ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] RESET configuration_parameter 
ALTER ROLE { role_specification | ALL } 
     [ IN DATABASE database_name ] RESET ALL

The syntax is fairly simple and pretty straightforward. To depict why this is really useful, I have added a real-world example. Let’s suppose that Joe happens to live on the island of Mauritius. When he logs in, he wants to be in his own time zone, even if his database server is located in Europe:

test=> ALTER  ROLE  joe SET TimeZone = 'UTC-4';  
ALTER  ROLE 
test=> SELECT now(); 
             now 
------------------------------- 
2017-01-09 20:36:48.571584+01 
(1 row) 
 
test=> q 
[hs@zenbook ~]$ psql  test  -U joe 
... 
test=> SELECT now(); 
           now 
------------------------------- 
2017-01-09 23:36:53.357845+04 
(1 row)

The ALTER ROLE clause will modify the user. As soon as joe reconnects, the time zone will already be set for him.

The time zone is not changed immediately. You should either reconnect or use a SET ... TO DEFAULT clause.

The important thing here is that this is also possible for some memory parameters, such as work_mem and so on.

Security at the database level

After configuring users at the instance level, it is possible to dig deeper and see what can be done at the database level. The first major question that arises is: we explicitly allowed Joe to log in to the database instance, but who or what allowed Joe to actually connect to one of the databases? Maybe we don’t want Joe to access all the databases in your system. Restricting access to certain databases is exactly what we can achieve on this level.

For databases, the following permissions can be set using a GRANT clause:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] 
     | ALL [ PRIVILEGES ] } 
    ON DATABASE database_name [, ...] 
    TO role_specification [, ...] [ WITH GRANT OPTION ]  

There are two major permissions on the database level that deserve close attention:

  • CREATE: This allows somebody to create a schema inside the database. Note that a CREATE clause does not allow for the creation of tables; it is about schemas. In PostgreSQL, a table resides inside a schema, so you have to get to the schema level first to be able to create a table.
  • CONNECT: This allows somebody to connect to a database.

The question now is: nobody has explicitly assigned CONNECT permissions to the joe role, so where do those permissions actually come from? The answer is this: there is a thing called public, which is similar to the Unix world. If the world is allowed to do something, so is joe, who is part of the general public.

The main thing is that public is not a role in the sense that it can be dropped and renamed. We can simply see it as the equivalent for everybody on the system.

So, to ensure that not everybody can connect to any database at any time, CONNECT may have to be revoked from the general public. To do so, we can connect as superuser and fix the problem:

[hs@zenbook ~]$ psql  test  -U postgres 
... 
test=# REVOKE ALL ON DATABASE test FROM public;  
REVOKE 
test=# \q 
[hs@zenbook ~]$ psql test -U joe 
psql:  FATAL:  permission denied for database "test"  
DETAIL:  User does not have CONNECT privilege.

As we can see, the joe role is not allowed to connect anymore. At this point, only superusers have access to test.

In general, it is a good idea to revoke permissions from the postgres database even before other databases are created. The idea behind this concept is that those permissions won’t be in all those newly created databases anymore. If somebody needs access to a certain database, rights have to be explicitly granted. Rights are not automatically there anymore.

If we want to allow the joe role to connect to the test database, try the following line as superuser:

[hs@zenbook ~]$ psql test -U postgres 
... 
test=# GRANT CONNECT ON DATABASE test TO bookkeeper;  
GRANT 
test=# \q 
[hs@zenbook ~]$ psql test -U joe 
...  
test=>

Basically, there are two choices here:

  • We can allow the joe role directly so that only the joe role will be able to connect.
  • Alternatively, we can grant permissions to the bookkeeper role. Remember, the joe role will inherit all the permissions from the bookkeeper role, so if we want all accountants to be able to connect to the database, assigning permissions to the bookkeeper role seems such as an attractive idea.

If we grant permissions to the bookkeeper role, it is not risky because the role is not allowed to log in to the instance in the first place, so it purely serves as a source of permissions.

Handling column-level security

In some cases, not everybody is allowed to see all the data. Just imagine a bank. Some people might see the entire information about a bank account, while others might be limited to only a subset of the data. In a real-world situation, somebody might not be allowed to read the balance column or somebody might not see the interest rates of people’s loans.

Another example would be that people are allowed to see people’s profiles but not their pictures or some other private information. The question now is: how can column-level security be used?

To demonstrate that, we will add a column to the existing table belonging to the joe role:

test=> ALTER TABLE t_useful ADD COLUMN name text;  
ALTER TABLE

The table now consists of two columns. The goal of the example is to ensure that a user can see only one of those columns:

test=> \d t_useful 
    Table  "public.t_useful"  
 Column |   Type   | Modifiers 
--------+---------+-----------  
 id     | integer  | 
 name   | text     |

As a superuser, let’s create a user and give it access to the schema containing our table:

test=# CREATE ROLE paul LOGIN;  
CREATE ROLE 
test=# GRANT CONNECT ON DATABASE test TO paul;  
GRANT 
test=# GRANT USAGE ON SCHEMA public TO paul;  
GRANT

CONNECT was revoked from public. Explicit granting is therefore absolutely necessary to ensure that we can even get to the table.

The SELECT permissions can be given to the paul role:

test=# GRANT  SELECT (id)  ON t_useful TO paul;  
GRANT 

Basically, this is already enough. It is already possible to connect to the database as user paul and read the column:

[hs@zenbook ~]$ psql test -U paul 
... 
test=> SELECT id FROM t_useful; 
id 
---- 
(0 rows)

If we are using column-level permissions, there is an important thing to keep in mind; we should stop using SELECT *, as it does not work anymore:

test=> SELECT * FROM t_useful; 
ERROR:  permission denied for relation t_useful

* still means all columns, but as there is no way to access all columns, things will error out instantly.

Configuring default privileges

So far, a lot of stuff has already been configured. What happens if new tables are added to the system? It can be quite painful and risky to process these tables one by one and to set proper permissions. Wouldn’t it be nice if those things would just happen automatically? This is exactly what the ALTER DEFAULT PRIVILEGES clause does. The idea is to give users an option to make PostgreSQL automatically set the desired permissions as soon as an object comes into existence. It cannot happen anymore that somebody simply forgets to set those rights.

The following listing shows the first part of the syntax specification:

postgres=# \h ALTER DEFAULT PRIVILEGES
Command: ALTER DEFAULT PRIVILEGES
Description: define default access privileges
Syntax:
ALTER DEFAULT PRIVILEGES
    [ FOR { ROLE | USER } target_role [, ...] ]
    [ IN SCHEMA schema_name [, ...] ]
    abbreviated_grant_or_revoke
where abbreviated_grant_or_revoke is one of:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
...

Basically, the syntax works similar to the GRANT clause and is therefore easy and intuitive to use. To show us how it works, I compiled a simple example. The idea is that if the joe role creates a table, the paul role will automatically be able to use it:

test=# ALTER DEFAULT PRIVILEGES FOR ROLE joe 
      IN SCHEMA public GRANT ALL ON TABLES TO paul;  
ALTER DEFAULT PRIVILEGES

Let’s connect as the joe role now and create a table:

[hs@zenbook ~]$ psql  test  -U joe 
... 
test=> CREATE TABLE  t_user (id serial, name  text,  passwd text);  
CREATE TABLE

Connecting as the paul role will prove that the table has been assigned to the proper set of permissions:

[hs@zenbook ~]$ psql test -U paul 
... 
test=> SELECT * FROM  t_user; 
 id | name | passwd 
----+------+--------  
(0 row

In the article, we covered the process of handling SSL, column-level security and configuring default privileges and much more. To know about row-level security and get detailed information about advanced PostgreSQL 11 features, check out the book Mastering PostgreSQL 11 – Second Edition.

Read Next

PostgreSQL wins ‘DBMS of the year’ 2018 beating MongoDB and Redis in DB-Engines Ranking

Devart releases standard edition of dbForge Studio for PostgreSQL

PipelineDB 1.0.0, the high performance time-series aggregation for PostgreSQL, released!