Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: app1; app2

-- 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;

Two questions:
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.?

Database list

* Database1 (Owner: group_beheer)
    + Client1 (scheme)  
    + Client4 (scheme)   
* Database2 (Owner: group_beheer)
    + Client2 (scheme)
    + Client3 (scheme)

Roles

* Client1
* Client2
* Client3
* Client4
* group_app1
    + Client1
    + Client4
* group_app2
    + Client3
    + Client2
* group_beheer
    + dbadmin
share|improve this question
1  
"But that results in an error." - what error? –  Milen A. Radev Oct 28 '13 at 15:22
    
I added the error. it looks like it trips over de TO command. If I loose it, like this: REVOKE ALL ON SCHEMA client1 it produces a 'syntax error' –  stUrb Oct 28 '13 at 17:15
    
You grant TO a group but you revoke FROM a group. That's what the syntax error is about –  Daniel Vérité Oct 28 '13 at 20:00
    
Furthermore, it is very much unclear how your DB and schema setup looks like. Could you please list the schemas in a given DB? –  dezso Oct 29 '13 at 9:18
    
Added the database list and the role-group setup –  stUrb Oct 29 '13 at 9:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.