Safer Application Users in Postgres

We deleted our database.

Two years ago on a Friday afternoon around 4pm I had a customer open a support ticket. The customer thought they were running their test suite against a dev environment. In reality they were running on production. One of the early steps in many test suites is to ensure a clean state:

1. DROP all tables or DELETE schemas
2. CREATE from scratch

With disaster recovery and point-in-time recovery in place, we could roll the database back to any exact moment in the past. So we got the timestamp, and they ran the command and recovered their several TB database to exactly the moment before. A stressful Friday afternoon, but no data loss.

You might be thinking of the various ways you can prevent this. Set your shell color to red when connected to production. Don't allow public internet access to production. Only allow CI-driven deployment. Here is one more option for you that is great for production risk mitigation: don't allow your production application users to delete data in prod.

Prevent Application User from Deleting Data in Production

To prevent an application from deleting data in production, we need to mitigate this risk and restrict the application user from the following operations:

• DROP TABLE
• TRUNCATE TABLE

The approach requires a mixture of best practices and proper configuration. To start, let's define the actors!

Administrator User

Administrator users are responsible for the creation of database schemas and relations (Data Definition Language, or DDL).

Let's create an administrator user for the sake of this example:

CREATE USER admin with PASSWORD 'correcthorsebatterystaple' SUPERUSER;
CREATE ROLE

\du admin
           List of roles
 Role name | Attributes | Member of
-----------+------------+-----------
 admin     | Superuser  | {}

Application User

Application users are generally restricted to performing operations on predefined database relations and schemas (Data Manipulation Language, or DML).

DROP and TRUNCATE privileges would not be granted to an application user.

Production applications should only need privileges to add and update data. A typical production application grows by:

• Adding new columns to tables
• Adding new rows
• Updating records

If your application follows the design pattern above, you might not want to give app users the ability to DROP, TRUNCATE, or DELETE from tables.

In the following example, we will use the application user named 'myappuser', so let's create them:

CREATE USER myappuser WITH PASSWORD 'verygoodpasswordstring';
CREATE ROLE

Create Tables as Admin

Now that we have our actors defined, let's set the stage.

We should only create production tables as the administrator user. By default, relation creators are relation owners. Only owners and superusers can perform actions such as DROP TABLE. This protects against accidental deletion of data in production tables by application users. Application users cannot drop tables they do not own.

Let's make sure we're the appropriate admin before making our production sandbox:

SELECT current_user;
 current_user
--------------
 admin
(1 row)

Go ahead and create a production SCHEMA and GRANT the appropriate permissions:

CREATE SCHEMA prod;
CREATE SCHEMA

GRANT USAGE ON SCHEMA prod TO myappuser;
GRANT

Now we can create a table for our production data and start testing out some concepts:

CREATE TABLE prod.userdata (col1 integer, col2 text, col3 text);
CREATE TABLE

If we log back in as myappuser, we shouldn't be able to drop the table:

\c postgres myappuser
Password for user myappuser:
You are now connected to database "postgres" as user "myappuser".
postgres=> DROP TABLE prod.userdata;
ERROR:  must be owner of table userdata

Least Privilege

We've shown how to block DROP TABLE for application users. To prevent deletion of tuples inside a relation, we need to do a bit more work. The application user should only have access to exactly what it needs.

To do this, we GRANT only the privileges that the application user needs, as outlined above:

postgres=> \c postgres admin
Password for user admin:
You are now connected to database "postgres" as user "admin".

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA prod TO myappuser;
GRANT

Or if you already have some application user created you can REVOKE the unwanted production privileges:

REVOKE DELETE, TRUNCATE ON ALL TABLES IN SCHEMA prod FROM myappuser;
REVOKE

Now our application user cannot delete data:

\c postgres myappuser
Password for user myappuser:
You are now connected to database "postgres" as user "myappuser".
postgres=> DELETE FROM prod.userdata *;
ERROR:  permission denied for table userdata
postgres=> TRUNCATE TABLE prod.userdata;
ERROR:  permission denied for table userdata

Great! We've narrowed down our privileges, but how do we know whether or not we're missing something?

Check Access

When working with roles and permissions, it is always good to do an access check. We have a nice extension I recommend crunchy_check_access for walking the full tree of access and permissions.

Log in as the admin user and take a look at the privileges we've granted to the application user:

SELECT base_role,objtype,schemaname,objname,privname FROM all_access() WHERE base_role = 'myappuser' AND schemaname = 'prod';
 base_role | objtype | schemaname | objname  | privname
-----------+---------+------------+----------+----------
 myappuser | schema  | prod       | prod     | USAGE
 myappuser | table   | prod       | userdata | SELECT
 myappuser | table   | prod       | userdata | INSERT
 myappuser | table   | prod       | userdata | UPDATE
(4 rows)

It's as simple as that!

Let Your Application User Delete Records

So we've revoked privileges and protected against "accidental" deletion errors in the database, but it is very likely that your application still needs to delete records. Let's look at safer alternative designs for deleting application data.

A common pattern in applications is to mark tuples as deleted, rather than deleting them.

We can alter the table above to add a timestamp column, named deleted, which has two benefits:

1. Data is never actually deleted, so the issues outlined above are not of concern.
2. We now have a snapshot of records at each moment in time for quick and painless application-level rollback of state.

Adding a deleted Column

Assuming we have the production table created already, we can add a deleted column like so:

ALTER TABLE prod.userdata ADD COLUMN deleted timestamp;
ALTER TABLE

NOTE: The ADD COLUMN syntax noted above is an expensive operation, as it holds an Exclusive Lock on the table.

Normal table inserts and update operations can still take the same form:

INSERT INTO prod.userdata VALUES (generate_series(1,10), md5(random()::text), md5(random()::text)) ;
INSERT 0 10

We now have the option of updating a row to mark it deleted. Let's say our app wants to delete all records where col1 < 3:

postgres=> UPDATE prod.userdata SET deleted = now() WHERE col1 < 3;
UPDATE 2

If we want to see all remaining records:

SELECT * from prod.userdata WHERE deleted IS NULL;
 col1 |               col2               |               col3               | deleted
------+----------------------------------+----------------------------------+---------
    3 | 828748efff06ce5b6f0f8e8931429bd3 | e50fe6654ee497de8ad75746849fba0f |
    4 | 4241511ee0a8f7f76976f0bab43b47f0 | d08e31ba79f972a2983301832ec67b94 |
    5 | 93de032bc9157362593a0259a8558514 | 6cd1639323a0c1a96fb3e781283e19d3 |
    6 | af1e1d81ef68dbd5ac14a0ae55195e2a | a4e500cf2c3ecd24c0a745c42b5af939 |
    7 | bcd0c74ca0d416b3f1b3e7ffda375615 | 361ed5d6bff759df7c138daf4b4b0e1b |
    8 | 35856a2d5b0e5b3e1d3ea4e09f0f88fe | a6d0977908e08626bad8278e965e9315 |
    9 | 43de7e949e9777969248b9b1d751d44e | 196390d618931a8dd3d5473cc23869fa |
   10 | 3fc5661e900a25b96b708f3c22cf1d59 | 2f29a28b25e1a1e25fc10b45fc22bc91 |
(8 rows)

We can also filter by timestamp. Say we delete more records, say any of the non-deleted columns, WHERE col1 < 6:

UPDATE prod.userdata SET deleted = now() WHERE deleted IS NULL AND col1 < 6;
UPDATE 3

SELECT * from prod.userdata;
 col1 |               col2               |               col3               |          deleted
------+----------------------------------+----------------------------------+----------------------------
    6 | af1e1d81ef68dbd5ac14a0ae55195e2a | a4e500cf2c3ecd24c0a745c42b5af939 |
    7 | bcd0c74ca0d416b3f1b3e7ffda375615 | 361ed5d6bff759df7c138daf4b4b0e1b |
    8 | 35856a2d5b0e5b3e1d3ea4e09f0f88fe | a6d0977908e08626bad8278e965e9315 |
    9 | 43de7e949e9777969248b9b1d751d44e | 196390d618931a8dd3d5473cc23869fa |
   10 | 3fc5661e900a25b96b708f3c22cf1d59 | 2f29a28b25e1a1e25fc10b45fc22bc91 |
    1 | b4fb51aff93bf865c6bc8c5f32b306cf | 49d37b3934e2c44f20ddd87019bc525e | 2022-02-03 16:30:49.445571
    2 | e53507d91f39905f6bcd193636b13c3d | 66066e4c78a3eb701086391052c19b56 | 2022-02-03 16:30:49.445571
    3 | 828748efff06ce5b6f0f8e8931429bd3 | e50fe6654ee497de8ad75746849fba0f | 2022-02-03 16:34:19.953742
    4 | 4241511ee0a8f7f76976f0bab43b47f0 | d08e31ba79f972a2983301832ec67b94 | 2022-02-03 16:34:19.953742
    5 | 93de032bc9157362593a0259a8558514 | 6cd1639323a0c1a96fb3e781283e19d3 | 2022-02-03 16:34:19.953742
(10 rows)

We can now restore state using the timestamp from the last delete:

SELECT * from prod.userdata WHERE deleted IS NULL OR deleted >= timestamp '2022-02-03 16:34:19.953742';
 col1 |               col2               |               col3               |          deleted
------+----------------------------------+----------------------------------+----------------------------
    6 | af1e1d81ef68dbd5ac14a0ae55195e2a | a4e500cf2c3ecd24c0a745c42b5af939 |
    7 | bcd0c74ca0d416b3f1b3e7ffda375615 | 361ed5d6bff759df7c138daf4b4b0e1b |
    8 | 35856a2d5b0e5b3e1d3ea4e09f0f88fe | a6d0977908e08626bad8278e965e9315 |
    9 | 43de7e949e9777969248b9b1d751d44e | 196390d618931a8dd3d5473cc23869fa |
   10 | 3fc5661e900a25b96b708f3c22cf1d59 | 2f29a28b25e1a1e25fc10b45fc22bc91 |
    3 | 828748efff06ce5b6f0f8e8931429bd3 | e50fe6654ee497de8ad75746849fba0f | 2022-02-03 16:34:19.953742
    4 | 4241511ee0a8f7f76976f0bab43b47f0 | d08e31ba79f972a2983301832ec67b94 | 2022-02-03 16:34:19.953742
    5 | 93de032bc9157362593a0259a8558514 | 6cd1639323a0c1a96fb3e781283e19d3 | 2022-02-03 16:34:19.953742
(8 rows)

Safer Application Users Summary

We've shown how to mitigate the risk of accidental deletion of production data, by:

1. Ensuring administrator users are object owners
2. Application users only have privileges for add/update operations
3. Safer deletion of data is possible by using a deleted timestamp column

Now we can rest easy knowing our production data is safe from those pesky test scripts!

• For more information on limiting database user privileges, check out the blog post on Creating a Read-Only Postgres User.
• PostgreSQL's privilege landscape is complicated. There is often more to Least Privilege than meets the eye. For a deeper dive on the complexities, check out the PostgreSQL Defaults and Impact on Security blog series.
• If you're interested in protecting user data, take a look at the Enhanced RBAC and Superuser Lockdown features of Crunchy Hardened PostgreSQL.