[postgis-users] Securing postgis

[Maria Arias de Reyna]

El Tuesday 01 March 2011, Karl O. Pinc escribió:
> Hi,
> 
> I have a number of users each of which has their
> own schema.  I don't want the users to be able
> to enable/disable the geospatialness of anyone
> else's columns.  What's the right way to
> secure postgis so as to prevent this?  It seems
> that a single, global, geometry_columns is the
> problem.
> 
> I see a number of possibliities.
> 
> If geometry_coulumns is all that needs to be secured
> I could create the table in each user's schema.
> If there's a lot of other infrastructure that needs
> to be duplicated this would not work as well --
> the user's schemas would be all cluttered up.
> But I can see where having multiple geometry_columns
> tables could complicate an upgrade....
> 
> I could create a separate postgis schema for
> each user, but that seems overkill and I'm not
> at all clear on how $user is expanded in
> the search_path and whether or not it'd be possible
> to automatically have such schemas in the search
> path.
> 
> then again I could just forget about it and
> hope the users don't kill each other.
> 
> What's the best approach here?

What if you write a trigger on every delete/update on the geometry_columns 
table? This trigger can cancel the delete/update if the user has no 
"permission" for that row. 

-- 
María Arias de Reyna Domínguez
Área de Operaciones

Emergya Consultoría 
Tfno: +34 954 51 75 77 / +34 607 43 74 27
Fax: +34 954 51 64 73 
www.emergya.es