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 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.
