Security and Operations Basics in PostgreSQL: Roles, GRANT, pg_hba, SCRAM, Backup and Major Upgrades

Version note: The examples in this post were run with PostgreSQL 18.7 (the official postgres:18 Docker image), .NET 10 and Npgsql 10.0.3, in the Bookstore lab we set up in post 02; the pg_upgrade and restore experiments ran in a throwaway container with both PostgreSQL 17.12 and 18.7 installed.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, needed a fix late one night, and Emre ran the migration out of habit as the postgres superuser. The migration added an audit_log table. In post 02 we defined ALTER DEFAULT PRIVILEGES for the application role; can the application read the new table? We also had the application try to create its own scratch table in the public schema:

CREATE TABLE public.audit_log (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, note text);  /* as postgres */
SET ROLE bookstore_app;
SELECT count(*) FROM audit_log;
CREATE TABLE app_scratch (id int);

With default privileges in place the first query should work, and the second might work too if you think of SQL Server's dbo. The result?

ERROR:  permission denied for table audit_log
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE app_scratch (id int);

Both were refused. Default privileges apply only to objects created by the role they name (FOR ROLE bookstore_owner); when postgres created the table, the rule did not kick in. And since PostgreSQL 15, not everyone may create tables in the public schema. In this post we look at the role model, GRANTs, a short introduction to row-level security, connection security with pg_hba.conf, SCRAM and TLS, backups and restore testing, returning to a point in time, and major upgrades.

The Role Model: Login and User Are One Concept

PostgreSQL only has roles. A role with the LOGIN attribute is a user that can connect; a role without it is a group that collects privileges. Privileges are granted to roles defined at the cluster level; there is no need to map a login to a user in every database as in SQL Server.

     rolname     | rolsuper | rolcreaterole | rolcreatedb | rolcanlogin | rolbypassrls
-----------------+----------+---------------+-------------+-------------+--------------
 bookstore_app   | f        | f             | f           | t           | f
 bookstore_owner | f        | f             | f           | t           | f
 postgres        | t        | t             | t           | t           | t

How the concepts from the security model post of the SQL Server Derinlemesine series map:

The split in the lab is deliberate: bookstore_owner owns the schema and runs migrations, bookstore_app only reads and writes data, and the superuser is used only for maintenance.

GRANT, ALTER DEFAULT PRIVILEGES and the public Schema

\dp shows a table's privileges in the form role=privileges/grantor:

 Schema |  Name  | Type  |            Access privileges             | Column privileges | Policies
--------+--------+-------+------------------------------------------+-------------------+----------
 public | orders | table | bookstore_owner=arwdDxtm/bookstore_owner+|                   |
        |        |       | bookstore_app=arwd/bookstore_owner       |                   |

arwd is the application role's INSERT (a), SELECT (r), UPDATE (w) and DELETE (d); D (TRUNCATE), x (REFERENCES), t (TRIGGER) and m (MAINTAIN, added in 17 for maintenance commands such as VACUUM, ANALYZE and REINDEX) are only the owner's. These privileges come from the default privileges in 00-roles.sql, and so does the error in the guess: the rule was written for "tables created by bookstore_owner". The fix is to always run migrations as the owner role; writing a separate ALTER DEFAULT PRIVILEGES FOR ROLE postgres for objects the superuser creates turns the exception into a permanent state.

If you need a read-only role for reporting, the predefined roles help. pg_read_all_data grants SELECT on every table, including tables created later:

CREATE ROLE bookstore_report NOLOGIN;
GRANT pg_read_all_data TO bookstore_report;
SET ROLE bookstore_report;
SELECT count(*) AS orders FROM orders;
UPDATE books SET price = price WHERE book_id = 1;

 orders
---------
 1000000

ERROR:  permission denied for table books

Careful: pg_read_all_data covers every database in the cluster. For a role that only needs to read bookstore, GRANT SELECT ON ALL TABLES IN SCHEMA public plus default privileges for the same schema is the narrower grant. For monitoring tools there are pg_monitor and pg_read_all_stats, which we used in post 07.

A Short Introduction to Row-Level Security

RLS adds a row-level filter to a table. The application writes the customer ID into a session setting on every request, and the policy shows only that customer's orders:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY orders_current_customer ON orders TO bookstore_app
    USING (customer_id = current_setting('app.customer_id', true)::int);

 customer_2
------------
      17359

ERROR:  invalid input syntax for type integer: ""

 as_owner
----------
  1000000

Customer 2 got 17,359 orders. The second result is an instructive error: once the setting has been SET and then RESET in a session, current_setting(..., true) returns an empty string, not NULL, and the cast fails. Use nullif(current_setting('app.customer_id', true), '')::int in the policy; without the setting the query then returns zero rows instead of an error. The third result matters too: the table owner is not subject to RLS; for the owner to obey the rule as well you need ALTER TABLE ... FORCE ROW LEVEL SECURITY. Setting the value per transaction (set_config(..., true)) when you use a connection pool, the multi-tenant design itself and a comparison with SQL Server's security policies are in the multi-tenancy post of the Çözüm Desenleri series.

pg_hba.conf, SCRAM and TLS

pg_hba.conf decides who may connect, from where and with which method; rules are read top to bottom and the first matching line wins. Since 14, passwords are stored with SCRAM-SHA-256 by default; 18 deprecated md5, and assigning an md5 password now raises a warning:

SET password_encryption = 'md5';
CREATE ROLE legacy_md5 LOGIN PASSWORD :'pw';   /* pw: a psql variable, randomly generated */

WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.

Roles carried over from an old server may still have md5 passwords; check whether pg_authid.rolpassword starts with SCRAM-SHA-256$ and reset the ones that are md5. The official Docker image ships with TLS turned off (ssl = off). db/14-tls.ps1 first creates a self-signed certificate for localhost and turns ssl on, then adds a rule that forbids the application role from connecting without encryption:

 line_number |   type    | database |    user_name    | address |  auth_method
-------------+-----------+----------+-----------------+---------+---------------
         128 | hostnossl | {all}    | {bookstore_app} | all     | reject
         129 | hostssl   | {all}    | {all}           | all     | scram-sha-256
         130 | host      | {all}    | {all}           | all     | scram-sha-256

On the .NET side the tls command opens the same connection with every SSL Mode and prints what the server sees in pg_stat_ssl:

NpgsqlConnectionStringBuilder builder = new(connectionString) { Pooling = false };
configure(builder);   /* e.g. b.SslMode = SslMode.VerifyFull; b.RootCertificate = rootCertificate; */
await using NpgsqlConnection connection = new(builder.ConnectionString);
await connection.OpenAsync();

$env:BOOKSTORE_PG_ROOT_CERT = "$PWD\server.crt"
dotnet run --project src/Bookstore.PgLab -- tls

Disable                        PostgresException: 28000: pg_hba.conf rejects connection for host "172.17.0.1", user "bookstore_app", database "bookstore", no encryption
Prefer (default)               ssl=True TLSv1.2 ECDHE-RSA-AES256-GCM-SHA384
Require                        ssl=True TLSv1.2 ECDHE-RSA-AES256-GCM-SHA384
VerifyFull                     NpgsqlException: Exception while performing SSL handshake
                               -> AuthenticationException: The remote certificate was rejected by the provided RemoteCertificateValidationCallback.
VerifyFull + Root Certificate  ssl=True TLSv1.2 ECDHE-RSA-AES256-GCM-SHA384

The default Prefer encrypts when the server supports it but does not validate the certificate; Require does not validate it either. Both accept a fake certificate presented by someone in the middle. The only mode that checks the server's identity is VerifyFull: without a trusted root certificate it refused, and with Root Certificate it connected. Running the same command with Host=127.0.0.1, VerifyFull refused again, because the certificate is for localhost. The TLS version depends on the client's TLS library; psql inside the container talked TLSv1.3 to the same server. In production the connection string should say SSL Mode=VerifyFull; managed services publish their root certificates in their documentation.

Backups: pg_dump and pg_restore

pg_dump takes a logical backup of a single database; the custom format (-Fc) is compressed and can be restored selectively and in parallel with pg_restore:

docker exec bookstore-pg pg_dump -U postgres -d bookstore -Fc -f /tmp/bookstore.dump
docker exec bookstore-pg createdb -U postgres -O bookstore_owner bookstore_restore
docker exec bookstore-pg pg_restore -U postgres -j 4 -d bookstore_restore /tmp/bookstore.dump

pg_dump -Fc: 5.650 s
63M     /tmp/bookstore.dump
pg_restore -j 1: 4.939 s
pg_restore -j 4: 2.267 s

The backup of a database of about 650 MB took 63 MB; indexes are stored in the backup only as CREATE INDEX statements. A parallel restore cut the time in half; table data and indexes are loaded concurrently. pg_dump does not include roles: they live at the cluster level, so take them separately with pg_dumpall --globals-only. Here we also saw what a restore test is worth: restoring the same backup as the owner role instead of the superuser gave:

pg_restore: error: could not execute query: ERROR:  permission denied to create extension "pg_stat_statements"
pg_restore: error: could not execute query: ERROR:  permission denied to create extension "pgstattuple"
pg_restore: warning: errors ignored on restore: 4

The untrusted extensions from post 13 cannot be installed by the owner role. You do not want to learn this for the first time during a real disaster. Only a regular restore test proves that a backup is good: restore it to a separate server, check row counts and that the application starts, and measure the time.

PITR: pg_basebackup and the WAL Archive

pg_dump takes you back to the moment it ran. To return to just before a mistake made during the day you need point-in-time recovery: a physical base backup plus an archive of every WAL file written after it. db/14-ops/pitr.sh turns on archive_mode in a throwaway container, takes a backup with pg_basebackup, then makes a fix and a mistake: a price update with a forgotten WHERE.

UPDATE books SET price = 41.50 WHERE book_id = 2;   /* the intended change */
/* recovery target: 2026-11-14 03:12:39.597928+00 */
UPDATE books SET price = 9.99;                       /* WHERE forgotten: 40,000 rows */

To go back, the server is stopped, the data directory is replaced with the base backup, and restore_command, recovery_target_time and a recovery.signal file are added:

LOG:  starting point-in-time recovery to 2026-11-14 03:12:39.597928+00
LOG:  recovery stopping before commit of transaction 811, time 2026-11-14 03:12:42.064434+00
LOG:  selected new timeline ID: 2
 book_id | price
---------+-------
       1 | 32.77
       2 | 41.50

The fix is still there and the bad update is gone. The server switched to a new timeline; when you restore from the same archive a second time, mind which timeline it follows. Since 17, pg_basebackup --incremental backs up only the changed blocks (it needs summarize_wal = on): after a 682 MB full backup, an update of 1,000 rows produced a 25 MB incremental backup. Before restoring, pg_combinebackup merges the chain into a single directory. In real environments tools such as pgBackRest or Barman do this job; the mechanism is the same.

Major Upgrades: pg_upgrade

Minor releases (18.6 → 18.7) only replace binaries and should be applied without delay: the 18.7 release of 12 November 2026 closed 52 security vulnerabilities, among them code execution through pg_restore (CVE-2026-116314). Between major versions the data file format changes, so you need pg_upgrade, dump/restore or logical replication. db/14-ops/upgrade.sh moves Bookstore from 17.12 to 18.7. The first attempt ran into a new 18 default:

Data page checksums are enabled.
old cluster does not use data checksums but the new one does

18's initdb turns data checksums on by default, 17's did not, and pg_upgrade requires both to match. Creating the new cluster with initdb --no-data-checksums passed the check (after removing the missing pg_cron library we saw in post 13). Turning checksums on later with pg_checksums --enable reads every file while the server is down; plan it as a separate maintenance task. The two upgrade modes:

659M    /var/lib/postgresql/17/data/base
copy: 1.864 s
link: 1.336 s

On the lab's 659 MB the difference is small, but copy mode grows with the data size, while --link connects files with hard links and is almost independent of size. The price: once the new server has started, the old cluster cannot be used, and your way back is the backup. An important improvement in 18 is statistics: right after the upgrade, without any ANALYZE, the planner's estimates are in place:

 column_stats
--------------
           46

 relname | last_analyze | last_autoanalyze
---------+--------------+------------------
 orders  |              |

 Gather  (cost=1000.00..16931.63 rows=4133 width=47)

For Pending orders, of which there are actually 4,800, the estimate is 4,133; up to 17 the table would have had no statistics at this point and the first queries would have run with bad plans. Extended statistics of the kind we created in post 07 are not carried over (pg_stats_ext was empty); the vacuumdb --all --analyze-in-stages --missing-stats-only that pg_upgrade suggests collects only what is missing, after which one row appears in pg_stats_ext. If you need to bring downtime below minutes, you can copy to the new version with logical replication and switch over; that approach is in the replication post of the Veritabanı Performansı ve Scaling series.

Production Notes

Use three separate roles for the application, migrations and reporting; the superuser is for maintenance only and should connect from the server itself, not over the network. Instead of a catch-all rule at the end of pg_hba.conf, spell out which application connects to which database from which network.

Derive the backup strategy from your RPO and RTO targets: pg_dump on its own gives a daily RPO, PITR brings it down to minutes. Put the restore test on the calendar and measure how long it takes.

Apply minor releases within the month they come out; before a major upgrade, run pg_upgrade --check on a copy of production and check extension versions (post 13) and the checksum setting.

Common Pitfalls

Taking backups and never restoring one: the extension error above, missing roles or a missing WAL file in the archive only show up during a restore.

Connecting the application as the postgres superuser: an SQL injection then compromises the whole server, not one table; and RLS does not apply to superusers.

Thinking SSL Mode=Require is enough: it encrypts, but it does not verify who is on the other side.

Running a migration with the wrong role and creating tables outside the default privileges: the permission denied from the guess usually shows up on the first production request.

A Small Experiment

★☆☆ Look at how the lab roles appear in the output of pg_dumpall --globals-only --no-role-passwords. How do you give passwords to roles restored without them?

★★☆ Fix the RLS policy with nullif, add FORCE ROW LEVEL SECURITY and verify that the owner role also sees only one customer's orders. What happens when pg_dump backs up this table?

★★★ Change pitr.sh: after the bad update, also insert a new order, and try the recovery with recovery_target_xid instead of recovery_target_time, skipping only the bad transaction. Could you recover the new order, and why?

In the next post we start Block D on versions: the changes from 13 to 17 you will run into when you inherit an older PostgreSQL project, and why moving off PostgreSQL 14, now out of support, is a priority.