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:
DROP
all tables orDELETE
schemasCREATE
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:
- Data is never actually deleted, so the issues outlined above are not of concern.
- 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:
- Ensuring administrator users are object owners
- Application users only have privileges for add/update operations
- 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.