Menu

PostgreSQL

Database Permissions – PostgreSQL

Full backup

To perform PostgreSQL full backups, the SqlBak database user needs the following privileges. Run the commands below (replace your_database with the actual database name).

CREATE USER sqlbak WITH PASSWORD 'your_password';

GRANT CONNECT ON DATABASE your_database TO sqlbak;

\c your_database
GRANT USAGE ON SCHEMA public TO sqlbak;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sqlbak;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO sqlbak;

What each privilege is used for (full backup)

  • CONNECT — Required to connect to the database for backup.
  • USAGE on schema public — Allows the user to access objects in the public schema.
  • SELECT on all tables — Read table data and acquire ACCESS SHARE lock for pg_dump (e.g. LOCK TABLE ... IN ACCESS SHARE MODE).
  • SELECT on all sequences — Required for pg_dump to read sequence values (e.g. last_value, is_called).

Restore

To restore PostgreSQL databases, the SqlBak user needs the following.

Restore to a new database

The user must be allowed to create databases:

ALTER USER sqlbak CREATEDB;

Restore to an existing database

If you restore into a database that already exists, the SqlBak user must be the owner of that database. Otherwise you get: ERROR: must be owner of database <name>.

Make the user the owner of the target database (run as superuser):

ALTER DATABASE your_database OWNER TO sqlbak;

Replace your_database with the actual database name (e.g. test_db).