New default role- 'pg_read_all_data'

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: New default role- 'pg_read_all_data'
Date: 2020-08-28 00:30:23
Message-ID: 20200828003023.GU29590@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Will add to the September CF.

Thanks,

Stephen

Attachment Content-Type Size
pg_rorole_v1.patch text/x-diff 6.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Steven Pousty 2020-08-28 01:17:58 Re: New default role- 'pg_read_all_data'
Previous Message Peter Geoghegan 2020-08-28 00:28:20 Disk-based hash aggregate's cost model