A few tips about database owners and application users

Safely controlling access to databases and their content is critical nowadays. Let's go through some simple tips that can help you secure your database in most common scenarios.

PostgreSQL manages database access permissions using the concept of roles.

A role may refer to an individual user (a role with LOGIN privilege) or to a group of users.

In PostgreSQL, roles are created at cluster level, meaning that roles are applied to every databases in the PostgreSQL instance. For this reason it is very important to limit role permissions appropriately.

This article suggests a general purpose practice to create and manage database roles and ownership. Even though it is not applicable in 100% of the cases, we do hope that it fosters the recognition of proper data access and control policy needs in your PostgreSQL instance, and helps you develop your own method.

The examples below refer to a generic database called foo, and are executed with the psql command line interface.

Typically, the minimal production database setup should contain at least two types of users:

• database owners (or administrators - though this term which might lead to misunderstandings)
• application users

As a basic rule of thumb, the database owner controls the database schema, data definitions, permissions, and so on.

Application users on the other hand, are only able to access and modify data where allowed.

The first step is about creating roles:

a. Create a user called foo (as postgres, or any user with role creation privileges):

# CREATE USER foo; 

b. Set a password for user foo, so that the password doesn't get written to the logs:

# \password foo 

c. Create an application role for read-only operations (as postgres, or any user with role creation privileges):

-- We use the "_ro" suffix to denote read-only 
# CREATE ROLE foo_ro; 

d. Create an application role for read and write operations (as postgres, or any user with role creation privileges):

-- We use the "_rw" suffix to denote read and write operations 
# CREATE ROLE foo_rw; 
-- Let read/write user inherit from read-only 
# GRANT foo_ro TO foo_rw; 

It is important to note that the _ro and _rw suffixes are just conventions. You are free to choose a different naming convention.

It is a good security practice to limit superuser access to management of global objects as much as possible (a superuser bypasses a lot of checks). For this reason we suggest to let standard users own a database. Continuing with our example, create a database called foo that is owned by foo (as postgres, or any user with database creation privileges):

# CREATE DATABASE foo WITH OWNER foo; 

From now on, the foo user is the owner of the foo database and can be used to create database schema and, in general, database objects (tables, functions, and so on) - as well as grant permissions on them.

Unless it is required by the application (e.g. the application manages the schema and related migrations), it is advised to have separate users than owners for data manipulation. An application user normally requires table access in order to perform any action, including reads (SELECT) on them.

Another good practice is to not grant privileges directly to users, but as shown in this example, to use intermediate roles to represent a set of privileges.

To start with, as the foo user, grant access to existing tables (supposing that you are using public schema):

> \c foo 
> GRANT USAGE ON SCHEMA public TO foo_ro; 
> GRANT SELECT ON ALL TABLES IN SCHEMA public TO foo_ro; 

Subsequently, grant privileges for data manipulation to the read-write role (as foo user):

> GRANT SELECT, UPDATE, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO foo_rw; 

This sequence of commands first grants a role full access to all objects in that schema, then gives viewing (SELECT) and modifying (INSERT,UPDATE and DELETE) rights on all tables to the respective roles. The last operation is effective only for the tables that already exist in the database. If you create new tables, you need to repeat the same operation, or individually assign permissions.

Alternatively, you can grant access to future tables for the read-only role by running the alter default privileges statement, as user foo:

# SET ROLE foo; -- become user foo 
> ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO foo_ro; 

Now that you have defined access policies at role level, you can associate them to real users.

In case users do not yet exist in the instance, you can create them afterwards. For example you could create a user for business intelligence purposes (with read only permissions) and another for web applications (with read-write permissions) in the following way :

# CREATE USER bi;
-- [snip] you need to set the password for the user
# GRANT foo_ro TO bi; 

# CREATE USER webapp; 
-- [snip] you need to set the password for the user
# GRANT foo_rw TO webapp; 

Once these steps are complete, the bi and webapp application users can log into the foo database.

Do not forget to assign them a safe password as discussed above and configure the pg_hba.conf file properly if necessary.

Obviously any assigned privilege can be removed using the REVOKE statement.

The described approach only covers two roles (global read-only and global read-write). There are obviously infinite possibilities to define access policies, which involve:

• subset of tables
• views
• stored procedures and functions
• access to particular columns
• row level security
• ...

In any case, they may all inherit from the suggested method. To sum up, here are some important and simple rules to keep in mind when defining data access policies:

1. Use superusers only for global objects management (i.e.: databases, tablespaces, roles, ...)
2. Let standard users own a database and manage the contained objects (in some cases, for example some extensions, superuser access might be still required)
3. Use roles to define data access policies
4. Let users (roles with login privileges) inherit roles