PostgreSQL Setup and Security

Anyone ever told you to have several layers of security to protect your application? Damn right. As an application developer or a DB admin, how can you ensure that you’ve setup the right set of security principles for your database? This article will help you answer some of those questions and hopefully make you feel secure about your database and maybe help you have a sound sleep at night.

Segregate privileges

The general principle of segregating privileges to the database is that “If you shouldn’t be using it, you shouldn’t have access to it”. The types of interactions a user or an application can have with a database can be categorized into 3 basic categories:

1. Read Data (SELECT)
2. Change Data / DML (INSERT, UPDATE, DELETE)
3. Change Structure or Schema / DDL (CREATE, ALTER, DROP, etc.)

It’s recommended to have 3 separate user accounts for your application to interact with the database to maintain the desired level of security. A read-only user, a second user that can read or write, and then a third user that can read, write and perform DDL operations on the database (but again, this user should not be the superuser).

Here are some useful commands that you can use to setup separate user accounts for your setup.

-- Create users
CREATE USER read_user WITH PASSWORD 'yED23@*d#5ctud';
CREATE USER dml_user WITH PASSWORD 'eYM45@XZ9Fq5e!';
CREATE USER masterdb WITH PASSWORD '5cE6*HVsaJSUb!';
-- Create the database
CREATE DATABASE masterdb WITH OWNER masterdb;

After creating the database and setting the ownership, the first thing that you should probably do is change the default behavior of the postgres database that allows any user create objects under the public schema. You should revoke access for any user to do anything in the public schema and then grant them specific access so they can view the catalog in the schema by the GRANT USAGE command.

-- Remove ability for all users to do everything in public schema
REVOKE ALL ON SCHEMA public FROM public;
-- Ensure users can list down objects in public schema
GRANT USAGE ON SCHEMA public TO public;

Next step would be to ensure that the read_user has access to read everything as the standard use case (of course, your use case might be different). And then also ensure that they have read access to any new tables created going forward and the admin doesn’t have to explicitly GRANT SELECT on ever new table that is created for the read_user.

-- Allow read_user to read from all objects
GRANT SELECT
    ON ALL TABLES IN SCHEMA public
    TO read_user;
-- Ensure read_user can read from all objects created in future
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES
    TO read_user;

Similar to read_user, the DML privileges should be grated to the dml_user on the public schema and for all the tables created going forward.

-- Allow dml_user to read and write from/to all objects
GRANT SELECT, INSERT, UPDATE, DELETE
    ON ALL TABLES IN SCHEMA public
    TO app_dml;
-- Ensure dml_user can read/write from/to all objects in future
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES
    TO dml_user;

Since we are not making any changes to the masterdb user who is also the owner of the database, they would have all the privileges by default for read, DML and DDL operations. But recommend to not make this user a super user so they cannot access other databases or have the ability to GRANT/REVOKE privileges from other users.