PostgreSQL Basics #9 Roles, Permissions, Backup Fundamentals: The Minimum Discipline of Operations

Infrastructure PostgreSQL Database
4 min read

The last chapter of the basics is unglamorous but accident-preventing: permissions and backups. “We gave the app a superuser account for convenience” and “we thought we had backups, but they wouldn’t restore” are the two great staples of database postmortems. Both are avoidable with a minimum of discipline.

Roles: users and groups are both just roles #

PostgreSQL unifies its permission subjects into a single concept: the role. A role that can log in is conventionally called a “user,” and a role used as a bundle of permissions is called a “group” — but they’re the same thing. The postgres role we’ve used since chapter 1 is a superuser role with everything permitted; convenient for learning, but handing it to an application is off-limits. Whether the breach is SQL injection or a code bug, the damage is bounded by “whatever the permissions allow.”

The practical sequence: a least-privilege account #

The proper structure separates a permission-bundle role from a login role.

Create least-privilege roles 
-- 1) permission-bundle (group) role: cannot log in
CREATE ROLE app_readwrite NOLOGIN;
GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_readwrite;

-- also apply automatically to tables created in the future
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_readwrite;

-- 2) login (user) role: inherits permissions from the group
CREATE ROLE app_server LOGIN PASSWORD 'strong-password' IN ROLE app_readwrite;

Three points worth absorbing. First, GRANT ... ON ALL TABLES applies only to tables that exist now; future tables are handled by ALTER DEFAULT PRIVILEGES (skip this and you’ll meet permission errors on every deploy). Second, IDENTITY columns draw numbers from sequences internally, so the USAGE grant on ALL SEQUENCES is required. Third, adding a human-facing read-only role (app_readonly, SELECT only) alongside the application role structurally prevents “logged in to look something up, accidentally ran an UPDATE.” Note that from PostgreSQL 15 the defaults were tightened so that not just anyone can create tables in the public schema — but the habit of granting explicitly, as above, keeps you safe regardless of version.

Check permissions with the psql meta-commands \du (role list) and \dp (table privileges). Add both to chapter 1’s observation toolkit.

Backup fundamentals: pg_dump and restore rehearsals #

The foundation of backups is the logical backup: extracting an SQL script or archive from which the database can be rebuilt.

Dump and restore rehearsal 
# dump in custom format (compressed + selective restore possible)
pg_dump -U postgres -Fc -f lab.dump lab

# restore: rehearse into a fresh database
createdb -U postgres lab_restore
pg_restore -U postgres -d lab_restore lab.dump

The reason -Fc (custom format) is the default habit: it compresses, and pg_restore can selectively restore individual tables (a plain SQL dump is all or nothing). And the most important sentence in all of backup practice is this: a backup you have never restored is not a backup. Only by periodically rehearsing a restore into a separate database, as above, do you avoid the accident of “the backup files piled up but won’t open.”

pg_dump takes “a consistent snapshot as of when the dump started” (chapter 6’s MVCC at work again). But logical backups have clear limits: changes after the dump are gone (with last night’s backup, today’s data doesn’t exist), and dump/restore times stretch with database size. “Rewinding to just before the incident” (PITR) and physical backups are the final chapter of the practice series. As covered in RDS vs self-managed, a managed service can take much of this layer off your hands.

The nine basics in retrospect, and the practice series #

That’s the basics. Installation and psql (1), types and constraints (2), joins and aggregation (3), indexes (4), EXPLAIN (5), transactions and MVCC (6), JSONB (7), views, CTEs and window functions (8), and permissions and backups (this chapter) — starting from “writes queries but never learned databases,” you now have the fundamentals to observe a database and reason about it. The practice series that follows moves the stage to a growing service: zero-downtime schema changes, connection pooling, performance diagnostics routines, VACUUM, locking, partitioning, replication, and PITR — in the order you actually hit them in operations.

Summary #

  • The permission subject is the role, singular. Users and groups are combinations of login capability and permission inheritance.
  • Never give the application a superuser. Build least privilege with a permission-bundle role plus a login role.
  • ON ALL TABLES covers the present; the future belongs to ALTER DEFAULT PRIVILEGES. The sequence USAGE grant is the easy-to-forget essential.
  • Backup fundamentals are pg_dump custom format — and a backup without restore rehearsals is not a backup. PITR comes in the practice series.
  • The nine basics are done. The practice series takes on a growing service’s operational problems in order.
X