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".
[1]https://www.postgresql.org/message-id/flat/5280E2AE.8070106%40usit.uio.no [2]https://wiki.postgresql.org/wiki/Pg_dump_improvements Regards, Jeevan Ladhe On Tue, Jun 26, 2018 at 12:12 PM, Prabhat Sahu < prabhat.s...@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+ 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 >
