When using pg_dump with a database with non-default schema foo like so:

pg_dump --schema-only --schema='foo' -U myuser -d mydb -h myhost -W > mydb.sql

The SQL dump file is full of these seemingly redundant commands like this:

REVOKE ALL ON schema foo FROM PUBLIC;  -- Doesn't PUBLIC have no privileges by
                                       -- default on a schema created by myuser?
REVOKE ALL ON schema foo FROM myuser;
GRANT ALL ON schema foo TO myuser;  -- Why revoke at all if GRANT ALL is done after?

-- Again, why each of these revocation statements when myuser already owns
-- this schema? And why all of these duplicate revocation statements for PUBLIC?

REVOKE ALL ON {FUNCTION|TABLE|SEQUENCE} foo.some_thing(...) FROM myuser;
GRANT ALL ON {FUNCTION|TABLE|SEQUENCE} foo.some_thing(...) TO myuser;

There are numerous functions, sequences, and tables within the schema. So these commands take up a lot of lines and appear almost completely redundant.

I am not asking to remove all ACL commands with the --no-acl flag. I am asking why the ACL commands are so noisy and appear to be mostly redundant. I'd like to simplify them in the database or in pgAdmin3 so that a dump does not have all these lines. In other words, why can't it just be something like:

REVOKE ALL ON SCHEMA foo FROM PUBLIC;  -- If there are any privileges by
                                       -- default for PUBLIC, remove them.

I do not want to manually write this each time, I am asking if pg_dump can give the desired behaviour. Clearly I can write it myself, but that is not very helpful.

Note that this is for a database where I am not the superuser, myuser does not own mydb or the public schema of mydb.

  • "is full of these seemingly redundant " the restore may happen in a DB where some schemas/permissions already exist. In which case only REVOKE + GRANT can guarantee to have the final state to be exactly as described in the database being dumped. – Patrick Mevzek Jul 26 at 18:06
  • In the case of GRANT ALL, that is still redundant. But point taken on other permissions already existing. And do you know anything about simplifying them? – Daniel Soutar Jul 29 at 10:01

Your Answer

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

Browse other questions tagged or ask your own question.