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

CodePudding user response：

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.

CodePudding user response：

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. You can find more info here