BUG #18107: "policy ... for table ... already exists" errors when restore postgres database from dump
The following bug has been logged on the website:
Bug reference: 18107
Logged by: AntonioK
Email address: antoniok.spb@gmail.com
PostgreSQL version: 15.4
Operating system: Debian GNU/Linux 12 (bookworm)
Description:
I'm restoring postgresql database from dump on a fresh new server. Source
database uses pgcron extension, target server have pgcron extension
installed.
This is how I create dumps on "source" server:
$ pg_dumpall -h hostname -p 5435 -U myuser --roles-only | bzip2 -c -z >
dump-role.sql.bz2
$ pg_dump -C -h hostname -p 5435 -U myuser mydatabase | bzip2 -c -z >
dump-data.sql.bz2
This is how I restore dumps on empty "target" server:
$ grep -Eiv '(CREATE ROLE postgres|ALTER ROLE postgres .*PASSWORD)'
dump-role.sql | psql -Upostgres > /dev/null
$ psql -Upostgres -f dump-data.sql > /dev/null
(`grep` on the first command is used to left "postgres" superuser untouched
on a target server.)
When restoring from dump, I got two errors:
psql:/path/to/dump-data.sql:18831791: ERROR: policy "cron_job_policy" for
table "job" already exists
psql:/path/to/dump-data.sql:18831798: ERROR: policy
"cron_job_run_details_policy" for table "job_run_details" already exists
They refers to the following lines of dump:
-- Name: job cron_job_policy; Type: POLICY; Schema: cron; Owner: some_user
CREATE POLICY cron_job_policy ON cron.job USING ((username =
CURRENT_USER));
-- Name: job_run_details cron_job_run_details_policy; Type: POLICY; Schema:
cron; Owner: some_user
CREATE POLICY cron_job_run_details_policy ON cron.job_run_details USING
((username = CURRENT_USER));
I never created those POLICY by hands on "source" database. The guess is
(@see https://dba.stackexchange.com/a/331198/82463) that 'CREATE EXTENSION
... pg_cron ...' statement in the dump file is implicitly executes POLICY
creation, so 'CREATE POLICY ... ON cron...' statements in the dump file are
redundant and unnecessary.
Should we consider this as pg_dump bug?
Source server:
# grep PRETTY_NAME /etc/*rel*
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
# psql -V
psql (PostgreSQL) 15.3 (Debian 15.3-1.pgdg120+1)
# dpkg -l | grep postgresql
ii postgresql-15 15.3-1.pgdg120+1 amd64
ii postgresql-15-cron 1.5.2-1.pgdg120+1 amd64
ii postgresql-client-10 10.23-1.pgdg120+1+b2 amd64
ii postgresql-client-11 11.20-1.pgdg120+1 amd64
ii postgresql-client-12 12.15-1.pgdg120+1 amd64
ii postgresql-client-13 13.11-1.pgdg120+1 amd64
ii postgresql-client-14 14.8-1.pgdg120+1 amd64
ii postgresql-client-15 15.3-1.pgdg120+1 amd64
ii postgresql-client-common 250.pgdg120+1 all
ii postgresql-common 250.pgdg120+1 all
ii postgresql-plpython3-15 15.3-1.pgdg120+1 amd64
ii postgresql-server-dev-10 10.23-1.pgdg120+1+b2 amd64
ii postgresql-server-dev-11 11.20-1.pgdg120+1 amd64
ii postgresql-server-dev-12 12.15-1.pgdg120+1 amd64
ii postgresql-server-dev-13 13.11-1.pgdg120+1 amd64
ii postgresql-server-dev-14 14.8-1.pgdg120+1 amd64
ii postgresql-server-dev-15 15.3-1.pgdg120+1 amd64
ii postgresql-server-dev-all:amd64 250.pgdg120+1 amd64
Target server:
# grep PRETTY_NAME /etc/*rel*
PRETTY_NAME="Debian GNU/Linux 12 (bookworm)"
# psql -V
psql (PostgreSQL) 15.4 (Debian 15.4-1.pgdg120+1)
# dpkg -l | grep postgresql
ii postgresql-15 15.4-1.pgdg120+1 amd64
ii postgresql-15-cron 1.5.2-2.pgdg120+2 amd64
ii postgresql-client-15 15.4-1.pgdg120+1 amd64
ii postgresql-client-common 253.pgdg120+1 all
ii postgresql-common 253.pgdg120+1 all
ii postgresql-plpython3-15 15.4-1.pgdg120+1 amd64
ii postgresql-server-dev-15 15.4-1.pgdg120+1 amd64