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

Robin Cheng 1 Reputation point

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> -U postgres postgres > a
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?

Azure Database for PostgreSQL
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Nasimjon Tohirov 211 Reputation points

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


    1 person found this answer helpful.
    0 comments No comments

  2. GeethaThatipatri-MSFT 23,847 Reputation points Microsoft Employee

    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.


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