[postgis] ERROR: geometry_columns: Permission denied

[lczub]

Moin Markus,

I played a little bit with GRANT. To create a new table with geometry fields,
the user needs INSERT-Access to the table geomtry_columns.

You find in the attachment a sql-script, which creates three groups with default
user and an insert-rule on geomtry_columns, which automaticly add the new table
to this groups.

Group pgis_admins should be used to create new table with geometry fields 
Group pgis_users could insert / manipulate data in tables with geometry fields
Group pgis_webusers could evaluate such tables like webusers 
        (could be used for connect-specifications in configuration-file of
mapserver (demo.map))

Names of the default user are: pgis_admin, pgis_user, mapserv
After calling 
          ' psql -f postgis_groups.sql markusgeom postgres '
you could install your new table calling
           ' psql -f radweg.sql markusgeom pgis_admin'

See please also the comment lines in the top of postgis_groups.sql and (sorry)
be careful, cause these are my first steps in sql. Hope it functions. My own
tests were successful.

PS: pgis_admin could not use DropGeometryColumn(), case this function works an
pg_attribute, so system rights are needed.

> 
> I get this error every time I try to use SELECT AddGeometryTable.
> 
> - I am logged in to the system as "markus"
> - psql -U postgres
>    CREATE USER markus WITH PASSWORD 'abc' CREATEDB;
> - createdb markusgeom
> - psql -U postgres -d markusgeom -f postgis.sql
> - psql -d markusgeom -f radwege.sql
>    radwege.sql is created by the shapefile-coverter
>   select DropGeometryColumn('markusgeom','testdaten3','the_geom');
>   drop table testdaten3;
>   create table testdaten3 (gid int4 , ID int4, INDEX varchar,
>    RAD_NAM3 varchar, RAD_NAM2 varchar, RAD_NAM1 varchar,
>    KLASSE varchar, VERKEHR float8, STEIGUNG float8, BESCHREI varchar);
>   select AddGeometryColumn(
>    'markusgeom','testdaten3','the_geom','-1','MULTILINESTRING',2);
> 
> Wich permissions do I have to change? GRANT to ???
> 

--

Gruß Luiko Czub

______________________________________________________
Luiko Czub
Stürtzelstraße 1         Tel.:  ++49-761/8098-240
D-79106 Freiburg         Email: lczub at t-online.de
______________________________________________________
------------------------ Yahoo! Groups Sponsor ---------------------~-->
Sponsored by VeriSign - The Value of Trust
Secure all your Web servers now - with a proven 5-part
strategy. The FREE Server Security Guide shows you how.
http://us.click.yahoo.com/uCuuSA/VdiDAA/yigFAA/PhFolB/TM
---------------------------------------------------------------------~->

To unsubscribe from this group, send an email to:
postgis-unsubscribe at yahoogroups.com

 

Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/ 

-------------- next part --------------
-- defines following groups with different access to posgis tables
-- pgis_admins	     people, which creates new tables with geomtry columns
--                   a) right ALL on spatial_ref_sys, geometry_columns
--		     b) rigth ALL on every table with geomtry columns
-- pgis_users        people, which manipulate data in tables with geomtry columns     
--                   a) rigth SELECT on spatial_ref_sys, geometry_columns
--		     b) rigth INSERT,UPDATE,DELETE on every table with geomtry columns
-- pgis_webusers     people, which only looks an gis data like webuser 
--                   a) rigth SELECT on spatial_ref_sys, geometry_columns
--                   b) rigth SELECT on every table with geomtry columns
--                   (if webuser should be able to manipulte data, the admin must 
--                    add this right seratly)
--
-- We get the rigth b) by implemantate
-- 1) a function SetPgisTableRights(a_table_name)
--    it creates three new GRANT Rules for the table A_TABLE_NAME
-- 2) an insert-RULE on geometry_columns, which calls this function everytime, 
--    a new entry is inserted into  geometry_columns. , the trigger creates 
--
-- defines following default users
-- pgis_admin        member of pgis_admins
-- pgis_user         member of pgis_users
-- mapserv           member of webuser, could be used for the connect-specification 
--                   in the configuration-file of mapserver (z.B. demo.map) 
--
-- open problem:  DropGeometryColumn() needs system rights, cause it changed something 
-- in the table pg_attribute  
-- the script shp2pgsql creates lines like 
--    select DropGeometryColumn('geomtest','radweg','the_geom');
--    drop table radweg ;
-- maybe, if the hole table will be droped, it would be better to say
--    DELETE FROM geometry_columns WHERE f_table_name = 'radweg';
--    DROP TABLE radweg ;
-- we only need DropGeometryColumn(), if the table should be alive, after removing
-- the geometry field, but the shp2psql wants to throw away the old structure
--
-- by lczub at t-online.de (Luiko Czub, Freiburg) 27.012002, postgis-0.6.2
-- (sorry for my bad english, hope you understand, what I'am doing)


-- default users for postgis
CREATE USER pgis_admin;
CREATE USER pgis_user;
CREATE USER mapserv;

-- Group pgis_webusers, pgis_users, pgis_webusers
CREATE GROUP pgis_admins WITH USER pgis_admin;
CREATE GROUP pgis_users WITH USER pgis_user;
CREATE GROUP pgis_webusers WITH USER mapserv;

-- rights for group pgis_webusers
GRANT SELECT ON spatial_ref_sys, geometry_columns TO GROUP pgis_webusers;
GRANT SELECT ON spatial_ref_sys, geometry_columns TO GROUP pgis_users;
GRANT ALL ON spatial_ref_sys, geometry_columns TO GROUP pgis_admins;

-- Function, which adds the specific PostGIs Rigths for a new table
CREATE FUNCTION SetPgisTableRights(varchar) RETURNS text AS '
       DECLARE
		table_name alias for $1;
       BEGIN
		EXECUTE ''GRANT ALL ON '' || table_name || '' TO GROUP pgis_admins '';
		EXECUTE ''GRANT SELECT, INSERT, UPDATE, DELETE ON '' || table_name || '' TO GROUP pgis_users '';
		EXECUTE ''GRANT SELECT ON '' || table_name || '' TO GROUP pgis_webusers '';
		return ''PostGis-Rights set for table '' || table_name;
       END;
       ' LANGUAGE 'plpgsql';

-- RULE on geometry_columns, which set the GRAND Rights, we need
CREATE RULE SetPgisTableRights AS ON INSERT
       TO geometry_columns
       DO SELECT SetPgisTableRights(new.f_table_name);



-------------- next part --------------
-- drops special postgis groups, default users, 
-- the function SetPgisTableRights() and the rule SetPgisTableRights
-- by lczub at t-online.de (Luiko Czub, Freiburg) 27.01.2002, postgis-0.6.2

-- the default users
DROP USER mapserv;
DROP USER pgis_user;
DROP USER pgis_admin;

-- the postgis groups
DROP GROUP pgis_webusers;
DROP GROUP pgis_users;
DROP GROUP pgis_admins;

-- the function 
DROP FUNCTION SetPgisTableRights(varchar);

-- the rule
DROP RULE SetPgisTableRights;