Extensions, policies, and pg_dump don't work together and result in duplicate policies
От | James Johnston |
---|---|
Тема | Extensions, policies, and pg_dump don't work together and result in duplicate policies |
Дата | |
Msg-id | CAANoKdbO=y0FT9fSzP24-kMf_A5y9xyujwJQk3=HgDynZYMAzQ@mail.gmail.com обсуждение исходный текст |
Список | pgsql-bugs |
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);
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: References to parameters by name are lost in INSERT INTO ... SELECT