How do I restrict PostGIS database with user specific access?

Question

I am using open source tools and working on QGIS 2.8 and Postgres/PostGIS 9.3 .

Working on urban application so we have single database and many users .I want to create department wise user group so that that group can not access other tables in database.

I want to update data to want to share data to them.

e.g. electric department should use only view/edit their data and no other department person should edit their data.

Tried following :-

1.Searched on google and PostgreSQL help as well created few users and their roles but when I log in using postgis by 'user1' credentials in QGIS I can see all my data.

2.Going through MANAGING USERS AND PERMISSIONS IN POSTGRESQL AND POSTGIS

thanks..

Answer 1

I would use schemas to manage the individual department objects. For example:

    CREATE SCHEMA elect_dept;
    CREATE SCHEMA water_dept;

Store each department's objects in the appropriate schema and use roles to manage access, for example:

   CREATE ROLE elect_dept;
   ALTER ROLE elect_dept SET SEARCH_PATH TO elect_dept,water_dept,public;
   GRANT USAGE ON SCHEMA elect_dept,water_dept TO elect_dept;
   GRANT SELECT ON ALL TABLES IN SCHEMA water_dept TO elect_dept;
   GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA elect_dept to elect_dept;

In the above example the role elect_dept can view water_dept schema objects and edit elect_dept schema objects.

It may make sense to create the roles as group roles and grant them to individual named accounts.

    GRANT elect_dept to smithj;

A little rough and ready, but with some tidying, should give you what you need.