PostgreSQL Administration: Users, Roles, Backup, and Restore

PostgreSQL Administration: Users, Roles, Backup, and Restore

A production database without access controls and a tested backup strategy is a liability. This lesson covers two of the most important administration tasks you will perform as a developer or junior database administrator: controlling who can do what through PostgreSQL's role system, and protecting data through reliable backup and restore procedures.

Roles and Privileges in PostgreSQL

PostgreSQL uses roles as the single concept for both users and groups. A role can log in (making it behave like a user), own database objects, and be a member of other roles (making it behave like a group). This flexibility lets you model real-world permission structures cleanly.

Create a role with the `CREATE ROLE` statement. By default, a new role cannot log in. Add `LOGIN` to allow it, and `PASSWORD` to set a credential:

```sql

CREATE ROLE read_only LOGIN PASSWORD 'changeme';

CREATE ROLE read_write LOGIN PASSWORD 'changeme';

CREATE ROLE admin LOGIN PASSWORD 'changeme' CREATEDB;

```

Roles alone do nothing until you grant privileges. Use `GRANT` to give a role permission on specific database objects:

```sql

-- read_only: SELECT on all tables in public schema

GRANT CONNECT ON DATABASE library TO read_only;

GRANT USAGE ON SCHEMA public TO read_only;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;

-- read_write: SELECT, INSERT, UPDATE, DELETE

GRANT CONNECT ON DATABASE library TO read_write;

GRANT USAGE ON SCHEMA public TO read_write;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO read_write;

-- admin: all privileges

GRANT ALL PRIVILEGES ON DATABASE library TO admin;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;

```

Verify each role by connecting with `psql -U read_only -d library` and attempting both permitted and restricted operations. Record every result in a security matrix — a table listing each role, each operation tested, and whether it succeeded or was correctly denied. This document becomes evidence that your access controls work as designed.

Remove a privilege with `REVOKE`. The principle of least privilege applies: grant only what the role needs to do its job, nothing more.

Backup with pg_dump

`pg_dump` is PostgreSQL's standard logical backup utility. It exports a consistent snapshot of one database without locking out other users, making it safe to run against a live system.

Two output formats matter most:

Plain-text SQL produces a `.sql` file you can read in any text editor and restore with `psql`:

```bash

pg_dump -U postgres -d library -f library_backup.sql

```

Custom format (flag `-Fc`) produces a compressed binary file restorable only with `pg_restore`. It supports parallel restores and selective object restoration, making it the preferred format for real backups:

```bash

pg_dump -U postgres -Fc -d library -f library_backup.dump

```

After the dump completes, always verify it. For a plain-text dump, check that the file begins with `-- PostgreSQL database dump` and that the file size is larger than zero. For a custom-format dump, run:

```bash

pg_restore --list library_backup.dump | head -20

```

This lists the objects stored in the archive. If the command returns output, the file is structurally valid.

Restore with pg_restore

Restoring the custom-format dump to a new database demonstrates that your backup is actually usable — the most critical test in any backup strategy.

```bash

createdb -U postgres library_restored

pg_restore -U postgres -d library_restored library_backup.dump

```

After restore, connect and run row-count queries to confirm data integrity:

```sql

SELECT relname, n_live_tup

FROM pg_stat_user_tables

ORDER BY relname;

```

Compare counts against the source database. Mismatches indicate a problem with the backup or restore process.

Writing an Administration Runbook

A runbook documents operational procedures so any qualified developer can perform them without guessing. A database runbook for the library system should cover:

• Backup schedule: when backups run, which format, where files are stored, and how long they are retained.
• Restore procedure: step-by-step commands to restore from the most recent dump, including how to verify success.
• Role definitions: a table of each role, its login name, its privileges, and which application components use it.
• Contact information: who owns the database and who to call if something breaks.

Peer-review each other's runbooks against a checklist. A runbook that is ambiguous or incomplete will fail at the worst possible moment.

Safety and Professional Practice

Database administration carries real risk. Always test restores in a non-production environment before relying on a backup. Never store database passwords in plain-text scripts checked into version control; use environment variables or a secrets manager. Grant the `SUPERUSER` attribute only when absolutely necessary and audit its use. Keep backup files in a location separate from the server they back up — a backup stored only on the server it protects is not a backup.