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.
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 |