Hi, I noticed that if a row level policy is defined on an extension object, even in the extension creation script, pg_dump dumps a separate CREATE POLICY statement for such policies. That makes the dump unrestorable because the CREATE EXTENSION and CREATE POLICY then conflicts.
Here is a simple example. I just abused the pageinspect contrib module to demonstrate the problem. ``` diff --git a/contrib/pageinspect/pageinspect--1.5.sql b/contrib/pageinspect/pageinspect--1.5.sql index 1e40c3c97e..f04d70d1c1 100644 --- a/contrib/pageinspect/pageinspect--1.5.sql +++ b/contrib/pageinspect/pageinspect--1.5.sql @@ -277,3 +277,9 @@ CREATE FUNCTION gin_leafpage_items(IN page bytea, RETURNS SETOF record AS 'MODULE_PATHNAME', 'gin_leafpage_items' LANGUAGE C STRICT PARALLEL SAFE; + +-- sample table +CREATE TABLE pf_testtab (a int, b int); +-- sample policy +CREATE POLICY p1 ON pf_testtab +FOR SELECT USING (true); ``` If I now take a dump of a database with pageinspect extension created, the dump has the following. ``` -- -- Name: pageinspect; Type: EXTENSION; Schema: -; Owner: -- CREATE EXTENSION IF NOT EXISTS pageinspect WITH SCHEMA public; -- -- Name: pf_testtab p1; Type: POLICY; Schema: public; Owner: pavan -- CREATE POLICY p1 ON public.pf_testtab FOR SELECT USING (true); ``` That's a problem. The CREATE POLICY statement fails during restore because CREATE EXTENSION already creates the policy. Are we missing recording dependency on extension for row level policies? Or somehow pg_dump should skip dumping those policies? Thanks, Pavan -- Pavan Deolasee http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
