On my postgresql server I'm struggeling / finding the best practice for a proper role/group setup.
I have a number of clients on one machine; each client has its own role:
client1; client2;client3 etcetc Depending on what kind of application they have, the role is added to a group, which corresponds with the database:
-- Add clients to the application GRANT group_app1 TO client1; GRANT group_app1 TO client4; -- Grant access to the database app1 to the corresponding group GRANT ALL ON DATABASE app1 TO group_app1;
Each database doesn't have a public schema; only schema's with the same name as the role are made so they are used as the standard search path. But off course i don't want to give access to other schemes but their own one.
-- Grant access to client1 to the schema -- ** how to revoke access to other members of the group? ** -- GRANT ALL ON SCHEMA client1 TO client1; GRANT ALL ON SCHEMA client4 TO client4;
1 - How can I now make sure the different users cannot access the other scheme's in the database? I tried
REVOKE ALL ON SCHEMA scheme_name TO group_name. But that results in the following error:
Fout in query: ERROR: syntax error at or near "TO" LINE 1: REVOKE ALL ON SCHEMA client1 TO group_app1
2 - How can I tweak this setup? Or is this o.k.?
* Database1 (Owner: group_beheer) + Client1 (scheme) + Client4 (scheme) * Database2 (Owner: group_beheer) + Client2 (scheme) + Client3 (scheme)
* Client1 * Client2 * Client3 * Client4 * group_app1 + Client1 + Client4 * group_app2 + Client3 + Client2 * group_beheer + dbadmin