Re: database specific pg_read_all_data / pg_write_all_data - Mailing list pgsql-admin

You are *almost* there I think.  By my understanding, which admittedly might be flawed, "pg_read_all_data" once given allows the role with that privlidge to litterally "read all data" across all databases in that cluster.  So while one can revoke public connect privs to a database and keep a role with pg_read_all_data privs from connecting to it, you're otherwise pretty much out of luck.  That option is unavailable in the situation where users have differing privs on the same cluster.  For example, if user0 needs to have "read_all" privs in database0, "read_all" and "write_all" privs in database1, and various privs in database2 on the same cluster, you can't use the CONNECT nor pg_hba.conf workarounds.  As soon as a role who's a member of 
"pg_read_all_data" can connect to a database in that cluster, it's game over.  Doubly so for roles with the "pg_write_all_data" priv.

These built-in roles are a much welcomed addition in PostgreSQL.  Unfortunately in PostgreSQL, unlike other RDBMSs, roles are cluster wide not database specific.  This leads to some interesting things in multi-database tools such as DBeaver which includes a seperate Roles folder in each PostgreSQL database connection containing copies of the exact same roles.  Use the GUI to alter a role in the Roles folder for database0, potentially be amazed that database1, and every other database in that cluster, magically reflects the change.  I'm not saying that is is nessicarrilly a bad thing, just different.  What it does mean though is that cluster wide roles and privs can and do much more than one might suspect.  This discussion of pg_read_all_data being a prime example.  Basically I think that because of the reliance on cluster wide roles in PostgreSQL, it's potentially dangerous to introduce built-in roles with far ranging privs without having a machinaism to limit them to specific databases in that cluster.  The only realistic way to take advantage of the extrodinarilly useful abilites they enable is to limit them to the relatively rare instances where there is only a single database on a cluster, or when the users can have the same access to all of the databases on that cluster.

Hopefully I've made my self clear enough in this matter and have demonstrated how being able to limit built-in cluster specific privs in a per database way would be very useful.

Thanks for taking the time, everyone, to read my missives and contribute your thoughts in this.

rik.