CODEWITHSUNDEEP
postgresqldump
Nitesh morajkar
Nitesh morajkar

Reputation: 431

Postgres: Can only superuser and owner take backup/dump?

So, I have created an user exclusively for taking backup. When I executed the dump command, below error occurred:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for table {{tableName}}
pg_dump: [archiver (db)] query was: LOCK TABLE public.{{tableName}} IN ACCESS SHARE MODE

Currently, Owner is postgres user for the respective table.

I wish to use an exclusive backup user which has only SELECT access. Below are the privileges to the role assigned to the user

CREATE ROLE {{roleName}};
\c {{databaseName}};
GRANT USAGE ON SCHEMA public TO {{roleName}};
GRANT SELECT ON ALL TABLES IN SCHEMA public TO {{roleName}};
GRANT SELECT ON ALL SEQUENCES IN SCHEMA mySchema TO {{roleName}};
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO {{roleName}};

Upvotes: 1

Views: 4415

Answers (1)

pifor
pifor

Reputation: 7882

I have only found in the documentation https://www.postgresql.org/docs/12/backup-dump.html:

But remember that pg_dump does not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in order to back up the entire database you almost always have to run it as a database superuser. (If you do not have sufficient privileges to back up the entire database, you can still back up portions of the database to which you do have access using options such as -n schema or -t table.)

And a very old message in the postgresql mailing list: https://www.postgresql.org/message-id/18014.1075821674%40sss.pgh.pa.us:

pg_dump should generally be run by the database superuser. Anything less creates problems with being unable to dump stuff that doesn't belong to it.

You can try to create a user with less privileges like you did: if pg_dump and pg_restore succeed then it should be OK.

Upvotes: 1

Related Questions