PostgreSQL user groups and authorization

Question

Postgresql version 9.0

I need an idea for grouping postgresql users.

i have 150+ table in database, and i want to settle groups of people. some of them can reach all tables, some of them not. I know it is possible to make it using grant or creating roles. but i want to figure out which is the best way to do that. how will it effect the performance?

giving all users to access some of the tables and managing them seems hard to me. so i thought what about creating different tablespaces and set permissions by tablespace.

is there any good tutorial , article or approach for creating user groups? what is your advice?

Answer 1

If you set permissions by tablespace, and requirements change such that the people in accounting need access to another table, then you have to move the table from one tablespace to another. But then again you can't move that table into a different tablespace without screwing up the permissions for all the other users.

Don't do that.

Instead, create logical groups for your users, assign users to groups, and assign permissions to groups. The PostgreSQL term is "roles".

It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.