[postgis-users] PostgreSQL/ Postgis schema definition for different group roles

[Mateusz Loskot]

joachim.sommer.net at web.de wrote:
> Now I am getting a bit stuck
> with schema definition with PostgreSQL/Postgis cause I would like have
> different schema like different user groups ( i. e. group roles). For
> example one schema calling webuser and another webguardian. Each group
> role consists of a number of single users.

You can create a new role that will be used as a group for users:

http://www.postgresql.org/docs/8.2/interactive/sql-createrole.html

Next, you can create a schema and assign the new role as a schema owner:

http://www.postgresql.org/docs/8.2/interactive/sql-createschema.html

The schema will work as a namespace for database objects and
it will be owned by the group (role you created):

CREATE ROLE mygroup
CREATE SCHEMA myschema AUTHORIZATION mygroup;

> How to tell PostgreSQL to work with group roles instead of single role as
> schema access?

See above.

> Will this follow to further problems with postgis data types such as
> AddGeometryColum, cause it asks for schemaname ?

There shouldn't be any problems but you may want to set SEARCH_PATH properly:

SET search_path TO myschema, public;

So, PostGIS metaschema stored in public schema can be found.

Here you can read about SET command:
http://www.postgresql.org/docs/8.2/interactive/sql-set.html

> SELECT AddGeometryColumn
> ('anwender','bauwerk_poly','geom',31468,'MULTIPOLYGON',3);


According to my example above, it will read:

SELECT AddGeometryColumn
('myschema','bauwerk_poly','geom',31468,'MULTIPOLYGON',3);

> Is there a better way for using groups in this context, as it seems to me
> that schema use refers just to a single user ?

Schema are not related to users statically.
It's just a convention that if you create a schema and you won't provide
new name, name of current user will be used (Check manual about CREATE
SCHEMA command).
Certainly, it's possible to have schema named differently than user,
as presented in examples above.

Cheers
-- 
Mateusz Loskot
http://mateusz.loskot.net