7

Suppose user foo owns a database gee, and user foo has granted user bar with all access privileges on database gee

gee=> \l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 gee       | foo      | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/foo              +
           |          |          |             |             | foo=CTc/foo          +
           |          |          |             |             | bar=CTc/foo

We dump this database,

$ pg_dump -U foo -v -f gee.dump gee

In database gee user bar has created a schema. So because all of these users we restore database gee as user postgres (after user foo has created a database goo),

$ sudo -u postgres psql -d goo -f gee.dump

User bar does not have the access privileges on the database goo. But inside database goo every privilege is OK, for all users, on schemas and tables.

goo=> \l
                              List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 goo       | foo      | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

Why are the access privileges on the database itself not restored?

migrated from stackoverflow.com Jun 16 '14 at 7:29

This question came from our site for professional and enthusiast programmers.

  • try use a superuser instead when pg_dump. – francs Jun 16 '14 at 7:07
  • 1
    Does it emit a bunch of errors during pg_dump? Like, say, errors about GRANT and a user not existing? – Craig Ringer Jun 16 '14 at 7:11
  • @francs, it does not help. – ericj Jun 16 '14 at 7:19
  • @CraigRinger No, no errors. – ericj Jun 16 '14 at 7:19
5

Why are the access privileges on the database itself not restored?

It's a bug, or a design oversight. Though the responder to that report doesn't think so.

pg_dumpall --globals-only doesn't dump rights on the database. Neither does pg_dump as part of the database dump.

So grants on databases only get included in a full pg_dumpall.

I'll make some noise about it by poking this thread.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.