On Thu, Aug 27, 2020 at 5:30 PM 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.
This will be much appreciated from an app developers perspective. It makes life so much easier to "do the right thing" in terms of giving read only webapps the right permissions.
