[postgis-users] Securing postgis

Andrea Peri aperi2007 at gmail.com
Wed Mar 2 01:00:40 PST 2011

>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

I guess a solution could be this:

you set the geometry_column to read-only for all user except "postgres" user.

After you can create a schema for every user must add table using only
its schema (its username).

After you create two security definer function with owner "postgres"
that add and remove a row (a table) from geometry_column.
and this two function has all the parameters needed for add a table to

The function before add to geometry_columns retrieve the
"session-user" and check if that is the same of the
If equal -> ok add
if not-equel-> "error: you are not allowable to add a table to schema"

Pay attention you must use "session-user" not "current-user" to check.


Andrea Peri
. . . . . . . . .
qwerty àèìòù
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110302/f6718ebc/attachment.html>

More information about the postgis-users mailing list