Creating admin roles with limited access to PostGIS

Question

I'm trying to cram three different organisations into one server. Today they each have their own geoserver instance connected to separate PostGIS databases, which works fine. But now they also need to be able to access and manipulate their own data and I can't figure out how to set this up.

So in short: How do I set up three different users in PostGIS and grant them access to one specific database each?

Any suggestions welcome

/O

Answer 1

The easiest would be to setup PostgreSQL user group roles and assign permissions based on those roles. It's a lot easier in PostgreSQL 9.0+ since you can use DEFAULT PRIVILEGES.

--this will take care of future tables in a database

 ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO gisadmins;

-- this will take care of existing tables in public schema. As I recall for existing I think you have to do it schema by schema

GRANT ALL ON ALL TABLES IN SCHEMA public TO gisadmins;