Extensions, policies, and pg_dump don't work together and result in duplicate policies

If an extension creates a policy, that policy is erroneously included
in the output from pg_dump. This is problematic because the dumped
SQL looks something like:

CREATE EXTENSION IF NOT EXISTS testcase WITH SCHEMA public;
-- The next line should not be here:
CREATE POLICY testpolicy ON public.testtable USING ((username =
CURRENT_USER));

When restoring / running this SQL, it results in an error because the
policy is created twice: once by CREATE EXTENSION, and once by CREATE
POLICY:

CREATE EXTENSION
2021-11-18 01:37:23.672 UTC [63] ERROR: policy "testpolicy" for
table "testtable" already exists
2021-11-18 01:37:23.672 UTC [63] STATEMENT: CREATE POLICY testpolicy
ON public.testtable USING ((username = CURRENT_USER));
psql:/docker-entrypoint-initdb.d/dump.sql:88: ERROR: policy
"testpolicy" for table "testtable" already exists

Technically, this might not a bug because it is a documented
limitation: https://www.postgresql.org/docs/14/extend-extensions.html

"PostgreSQL does not currently support extension scripts issuing
CREATE POLICY or SECURITY LABEL statements. These are expected to be
set after the extension has been created. All RLS policies and
security labels on extension objects will be included in dumps created
by pg_dump."

So alternatively, this e-mail could be considered a feature request
rather than a bug report. At the end of the day, this is problematic
for an end-user like me because there are commonly-used extensions in
the wild, such as pg_cron, which create policies:
https://github.com/citusdata/pg_cron/blob/4a82548e4e5ae1c0682283fee8559d4111e4c60d/pg_cron.sql#L29

The pg_cron extension otherwise seems to work perfectly fine and there
seems no reason (to me, as an end-user) why the extension can't create
a policy (especially as it is apparently core to the security model of
the extension). Nothing in PostgreSQL actually blocks the extension
author from creating policies in their extension: without consulting
the documentation and finding the above obscure limitation, an
extension author could easily believe that it is supported.
Everything then works, until I try to dump the database, which is
something the extension author (quite reasonably) might not have
tested... at which point, I must manually edit the dump file to remove
this duplicate SQL statement for the policy.

Here is a full test case utilizing the PG 14.1 docker image. To try
it, run the "test.sh" script and then examine the resulting "dump.sql"
file written to the local working directory. If you then try to add a
"COPY dump.sql /docker-entrypoint-initdb.d/dump.sql" to the Dockerfile
to prepopulate the docker image with the generated dump file, the
docker image fails to start with the above quoted error unless you
manually delete the "CREATE POLICY" line from the dump file.

Thank you for taking a look! I am unfamiliar with PG's workflows, so
please feel free to let me know if I should add this to a bug tracker
or a feature request tracker someplace (however this can be
categorized).

--James

FILE: Dockerfile
================

FROM postgres:14.1

RUN apt-get update && \
apt-get install -y --no-install-recommends make

COPY ./Makefile /testcase/Makefile
COPY ./testcase.control /testcase/testcase.control
COPY ./testcase--1.0.sql /testcase/testcase--1.0.sql

RUN cd /testcase && \
make install

ENV POSTGRES_USER=postgres
ENV POSTGRES_HOST_AUTH_METHOD=trust

FILE: Makefile
==============

EXTENSION = testcase
DATA = testcase--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

FILE: test.sh
=============

#!/bin/sh

set -ex

docker build --tag pgdump_policy_bug .

docker network create --driver bridge pgtest_net
docker run --detach --net pgtest_net --net-alias pg --rm \
-p 5432:5432/tcp --name pgdump_test pgdump_policy_bug
sleep 10 # wait for PG to start

docker run --rm --net pgtest_net postgres:14.1 psql -U postgres \
-h pg -p 5432 -c "create extension testcase;" postgres
docker run --rm --net pgtest_net -v $(pwd):/schema_dump postgres:14.1 \
pg_dumpall -h pg -U postgres -f /schema_dump/dump.sql

docker stop pgdump_test
docker network rm pgtest_net

FILE: testcase.control
======================

default_version = '1.0'
relocatable = false

FILE: testcase--1.0.sql
=======================

CREATE TABLE testtable(
id bigint primary key,
username text not null default current_user
);
ALTER TABLE testtable ENABLE ROW LEVEL SECURITY;
CREATE POLICY testpolicy ON testtable USING (username = current_user);