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
Last activity: Jul 26, 2019, 03:57 PM