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.
