"Access privileges" is missing after pg_dumpall

Started by Prabhat Sahuover 7 years ago3 messages
#1Prabhat Sahu
prabhat.sahu@enterprisedb.com

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

#2Jeevan Ladhe
jeevan.ladhe@enterprisedb.com
In reply to: Prabhat Sahu (#1)
Re: "Access privileges" is missing after pg_dumpall

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]/messages/by-id/5280E2AE.8070106@usit.uio.no
in past and I also see there is a wiki[2]https://wiki.postgresql.org/wiki/Pg_dump_improvements page called "Pg dump
improvements".

[1]: /messages/by-id/5280E2AE.8070106@usit.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.sahu@enterprisedb.com> wrote:

Show quoted text

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Prabhat Sahu (#1)
Re: "Access privileges" is missing after pg_dumpall

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