From: | Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> |
---|---|
To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | pg_dump dumps row level policies on extension tables |
Date: | 2020-05-19 06:31:35 |
Message-ID: | CABOikdOJaV1wuFaaRaZCTCcpcWvxGbaEyF4i8Qk=-utanJ5xJg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2020-05-19 06:43:27 | some grammar refactoring |
Previous Message | Michael Paquier | 2020-05-19 06:20:29 | Re: Missing grammar production for WITH TIES |