pg_dump: error: query failed: ERROR: query would be affected by row-level security policy for table "job"

Question

I'm doing an backup and restore exercise against Postgres DB but immediately ran into error while performing the action against Azure Postgres DB.

Right after I initialized a Postgres DB on the Flexible Server, I ran pg_dump from my laptop

pg_dump -Fc -h <dbhost>.postgres.database.azure.com -U postgres postgres > a
Password:
pg_dump: error: query failed: ERROR: query would be affected by row-level security policy for table "job"
pg_dump: detail: Query was: COPY cron.job (jobid, schedule, command, nodename, nodeport, database, username, active, jobname) TO stdout

How can I bypass those tables to accomplish my pg_dump backup?

Answer 1

Hello,
I think you can try to add this -T "cron.*".

Regards,
Nasimjon.

Answer 2

Hi, @Robin Cheng Welcome to the Microsoft Q&A forum, Thanks for posting your question.
If my understanding is correct you are taking the dump of the Postgres database. with pg_dump and you are facing row-level security policy for table "job" error.
You exclude the tables from your dump....add the parameter -T tablename in pg_dump command.

Also, why do you want to dump Postgres DB? Postgres DB is the default database and only superusers can dump objects in Postgres DB unless the object is owned by your user.
As per the design of the PaaS environment, you cannot take a dump of the Postgres database as it is a default database. It is not recommended to use Postgres database as every user has access to this database"

recommend way is to create separate user databases to store the data. You take backups of each table in the Postgres database as a workaround.
More information on Backup and restore
Please let me know if you are looking for additional information.

Regards
Geetha

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer.