Sample Header Ad - 728x90

Can I get pg_dump to simplify the ACL commands?

0 votes
0 answers
821 views
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.
Asked by Daniel Soutar (101 rep)
Jul 26, 2019, 03:44 PM
Last activity: Jul 26, 2019, 03:57 PM