Thread: pg_dump dumps row level policies on extension tables

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

On Tue, 19 May 2020 at 15:31, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:
>
> 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?
>

I think we don't support this case as the comment in
checkExtensionMembership() describes:

    /*
     * In 9.6 and above, mark the member object to have any non-initial ACL,
     * policies, and security labels dumped.
     *
     * Note that any initial ACLs (see pg_init_privs) will be removed when we
     * extract the information about the object.  We don't provide support for
     * initial policies and security labels and it seems unlikely for those to
     * ever exist, but we may have to revisit this later.
     *
     * Prior to 9.6, we do not include any extension member components.
     *
     * In binary upgrades, we still dump all components of the members
     * individually, since the idea is to exactly reproduce the database
     * contents rather than replace the extension contents with something
     * different.
     */

Regards,

-- 
Masahiko Sawada            http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services