On Fri, Aug 28, 2020 at 2:30 AM Stephen Frost <sfrost@snowman.net> wrote:
Greetings,
There's no shortage of requests and responses regarding how to have a 'read all of the data' role in PG, with various hacks involving "GRANT ALL" and "ALTER DEFAULT PRIVILEGES" to "solve" this, neither of which really works long term ("GRANT ALL" is one-time, and "ALTER DEFAULT" only helps for the roles that exist today).
Now that we have the default role system, we can provide a proper solution to this oft-requested capability.
This patch adds a default role to meet specifically that use-case, in the long-term, by explicitly allowing SELECT rights on all relations, and USAGE rights on all schemas, for roles who are members of the new 'pg_read_all_data' role.
No effort is made to prevent a user who has this role from writing data- that's up to the admin, but this will allow someone to use pg_dump or pg_dumpall in a much more reliable manner to make sure that the entire database is able to be exported for the purpose of backups, upgrades, or other common use-cases, without having to have that same user be a PG superuser.
This role is given the Bypass RLS right, though to use it effectively, a user would need to pass '--role=pg_read_all_data' to pg_dump/pg_dumpall, since role attributes are not checked as part of role membership.
Thoughts?
Without having actually looked at the code, definite +1 for this feature. It's much requested...
But, should we also have a pg_write_all_data to go along with it?
