BUG #14456: pg_dump doesn't restore permissions on tables belonging to an extension

From: daniele(dot)varrazzo(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14456: pg_dump doesn't restore permissions on tables belonging to an extension
Date: 2016-12-08 19:16:42
Message-ID: 20161208191642.1407.30751@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14456
Logged by: Daniele Varrazzo
Email address: daniele(dot)varrazzo(at)gmail(dot)com
PostgreSQL version: 9.5.4
Operating system: Linux
Description:

As per title. To test:

1) create an extension containing a table, e.g.:

$ cat /usr/share/postgresql/9.5/extension/testext.control
default_version = '1.0'
comment = 'test of a pg bug'
superuser = false

$ cat /usr/share/postgresql/9.5/extension/testext--1.0.sql
create table testtbl (id serial primary key, data text);
select pg_catalog.pg_extension_config_dump('testtbl', '');

2) Load the extension in the database

=# create database test;
CREATE DATABASE

=# \c test;
test=# create schema extschema;
CREATE SCHEMA

test=# create extension testext with schema extschema;
CREATE EXTENSION

3) Customize extension table permissions

test=# create user u2;
CREATE ROLE

test=# grant select on extschema.testtbl to u2;
GRANT

test=# \dpp extschema.testtbl
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
-----------+---------+-------+-------------------+-------------------+----------
extschema | testtbl | table | piro=arwdDxt/piro+| |
| | | u2=r/piro | |
(1 row)

test=# insert into extschema.testtbl (data) values ('asdf');
INSERT 0 1

4) Create a new database and dump/restore data there

test=# create database test2;
CREATE DATABASE

$ pg_dump test | psql -1 test2

5) permissions are not restored

test2=# \dpp extschema.testtbl
Access privileges
Schema | Name | Type | Access privileges | Column privileges |
Policies
-----------+---------+-------+-------------------+-------------------+----------
extschema | testtbl | table | | |
(1 row)

Note that using ALTER DEFAULT PRIVILEGES doesn't work either: the default
privs on the schemas are restored after the extension is created so the
tables created by the extensions don't benefit of it.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephen Frost 2016-12-08 19:29:33 Re: BUG #14456: pg_dump doesn't restore permissions on tables belonging to an extension
Previous Message Tom Lane 2016-12-08 04:54:51 Re: BUG #14455: pg_upgrade fails with error