pg_dump failed when trying the backup from Azure Postgres Database ERROR: query would be affected by row-level security policy for table "job"

Question

I am a postgres newbie and now trying to setup an external database for the application that we're developing.

Attempt 1:

I used our application to initialize a local postgres db and then immediately ran a pg_dump

pg_dump -Fc -h localhost -U postgres postgres

there was no error but things were dumped to stdin

Attempt 2:

I created a database with free Azure account, used our application to initialize the azure postgres db and then immediately ran the same pg_dump command then an error appeared

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

Need some input from you how can I solve the row-level security issue? Thanks

Answer 1

Welcome to the joys of hosted databases. Your databases are not identical, because on the hosted database postgres is not a superuser: you will never get a superuser account on a hosted PostgreSQL databases.

Now, since you are developing that application, you should know that it uses row level security. pg_dump will refuse to operate if the database user is not exempt from row level security, because that would lead to an incomplete and potentially inconsistent dump. Superusers can always be used, because they are automatically exempt from row level security.

You will have to use a user that has the BYPASSRLS property set. Unless row level security is ENFORCEd, you can also use the user that owns the tables, since the table owner is by default exempt from row level security.

Answer 2

Pg_dump exeuction with exclusing CRON related tables -T "cron.*".