Thread: "Access privileges" is missing after pg_dumpall
Hi,
I have taken pg_dumpall in pg-master and after restoring the dump I am not able to see the "Access privileges" as below:
Same is reproducible in back branches as well, is this fine ?
CREATE ROLE user1 PASSWORD 'user1' SUPERUSER LOGIN;
CREATE DATABASE db1 OWNER=user1;
GRANT ALL ON DATABASE db1 TO user1;
postgres=# \l+ db1
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------+-------+----------+------------+------------+-------------------+---------+------------+-------------
db1 | user1 | UTF8 | en_US.utf8 | en_US.utf8 | =Tc/user1 +| 7621 kB | pg_default |
| | | | | user1=CTc/user1 | | |
(1 row)
postgres=# SELECT datname as "Relation", datacl as "Access permissions" FROM pg_database WHERE datname = 'db1';
Relation | Access permissions
----------+-----------------------------
db1 | {=Tc/user1,user1=CTc/user1}
(1 row)
-- pg_dumpall
./pg_dumpall > /tmp/dumpall.sql
-- Restore
./psql -a -f /tmp/dumpall.sql
postgres=# \l+ db1
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
------+-------+----------+------------+------------+-------------------+---------+------------+-------------
db1 | user1 | UTF8 | en_US.utf8 | en_US.utf8 | | 7699 kB | pg_default |
(1 row)
postgres=# SELECT datname as "Relation", datacl as "Access permissions" FROM pg_database WHERE datname = 'db1';
Relation | Access permissions
----------+--------------------
db1 |
(1 row)
--
With Regards,
Prabhat Kumar Sahu
Skype ID: prabhat.sahu1984
EnterpriseDB Corporation
The Postgres Database Company
Seems this is a known issue and the reason I understand is that the users/roles
may already exist but may have a different meaning, I see it has been discussed[1]
in past and I also see there is a wiki[2] page called "Pg dump improvements".
Regards,
Jeevan Ladhe
On Tue, Jun 26, 2018 at 12:12 PM, Prabhat Sahu <prabhat.sahu@enterprisedb.com> wrote:
Hi,I have taken pg_dumpall in pg-master and after restoring the dump I am not able to see the "Access privileges" as below:Same is reproducible in back branches as well, is this fine ?CREATE ROLE user1 PASSWORD 'user1' SUPERUSER LOGIN;CREATE DATABASE db1 OWNER=user1;GRANT ALL ON DATABASE db1 TO user1;postgres=# \l+ db1List of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description------+-------+----------+------------+------------+-------- -----------+---------+-------- ----+------------- db1 | user1 | UTF8 | en_US.utf8 | en_US.utf8 | =Tc/user1 +| 7621 kB | pg_default || | | | | user1=CTc/user1 | | |(1 row)postgres=# SELECT datname as "Relation", datacl as "Access permissions" FROM pg_database WHERE datname = 'db1';Relation | Access permissions----------+----------------------------- db1 | {=Tc/user1,user1=CTc/user1}(1 row)-- pg_dumpall./pg_dumpall > /tmp/dumpall.sql-- Restore./psql -a -f /tmp/dumpall.sqlpostgres=# \l+ db1List of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description------+-------+----------+------------+------------+-------- -----------+---------+-------- ----+------------- db1 | user1 | UTF8 | en_US.utf8 | en_US.utf8 | | 7699 kB | pg_default |(1 row)postgres=# SELECT datname as "Relation", datacl as "Access permissions" FROM pg_database WHERE datname = 'db1';Relation | Access permissions----------+-------------------- db1 |(1 row)--
With Regards,
Prabhat Kumar Sahu
Skype ID: prabhat.sahu1984
EnterpriseDB Corporation
The Postgres Database Company
Prabhat Sahu <prabhat.sahu@enterprisedb.com> writes: > I have taken pg_dumpall in pg-master and after restoring the dump I am not > able to see the "Access privileges" as below: > Same is reproducible in back branches as well, is this fine ? Yes, it is, because the privileges are the same in both states. In one case you have an explicit representation of the default privileges, in the other it's just default. regards, tom lane