Re: [postgis-users] Securing postgis
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 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 geometry_column. The function before add to geometry_columns retrieve the session-user and check if that is the same of the schema-parameter. 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. regards, -- - Andrea Peri . . . . . . . . . qwerty àèìòù - ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
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 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 03/02/2011 03:11:20 AM, Maria Arias de Reyna wrote: 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. 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. Yes, I've been thinking more about this and have come to the same conclusion you have. The right way to go is to put triggers on geometry_columns that check permissions against what's granted on the column holding the geometry data. Any clue if this would be something I could send in as a patch to the postgis project? Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 02/03/11 15:17, Karl O. Pinc wrote: Yes, I've been thinking more about this and have come to the same conclusion you have. The right way to go is to put triggers on geometry_columns that check permissions against what's granted on the column holding the geometry data. Any clue if this would be something I could send in as a patch to the postgis project? Probably not, as I believe Paul is planning to switch the binary representation of the geometry type in 2.0 which would then enable us to make geometry_columns a view just like geography_columns. However, it would probably be of interest to enough people to add it somewhere to the user part of the wiki. ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 03/02/2011 09:55:57 AM, Mark Cave-Ayland wrote: On 02/03/11 15:17, Karl O. Pinc wrote: Yes, I've been thinking more about this and have come to the same conclusion you have. The right way to go is to put triggers on geometry_columns that check permissions against what's granted on the column holding the geometry data. Any clue if this would be something I could send in as a patch to the postgis project? Probably not, as I believe Paul is planning to switch the binary representation of the geometry type in 2.0 which would then enable us to make geometry_columns a view just like geography_columns. However, it would probably be of interest to enough people to add it somewhere to the user part of the wiki. Humm. Ok. I'm unfamiliar with geography_columns. What is the security model? Can anyone do anything like with geography_columns? If it's a view then would it be possible to put triggers on the underlying tables? Where would I look in the code? Thanks. Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 02/03/11 16:30, Karl O. Pinc wrote: Humm. Ok. I'm unfamiliar with geography_columns. What is the security model? Can anyone do anything like with geography_columns? If it's a view then would it be possible to put triggers on the underlying tables? Where would I look in the code? Thanks. It's still under discussion, but the view is generated automatically by querying the system catalogues to pull out the spatial columns (i.e. it can't be altered, but users will be able to see other users' spatial columns in there). This means that AddGeographyColumn() and DropGeometryColumn() can just add/remove the column from the table directly, so this can only happen on tables for which the current role has SQL permissions. HTH, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Securing postgis
On 03/02/2011 10:42:17 AM, Mark Cave-Ayland wrote: On 02/03/11 16:30, Karl O. Pinc wrote: I'm unfamiliar with geography_columns. What is the security model? Can anyone do anything like with geography_columns? If it's a view then would it be possible to put triggers on the underlying tables? Where would I look in the code? It's still under discussion, but the view is generated automatically by querying the system catalogues to pull out the spatial columns (i.e. it can't be altered, but users will be able to see other users' spatial columns in there). If you wanted I suppose you could have such a view check permissions and thereby avoid revealing such information to unauthorized users. Just a thought. This means that AddGeographyColumn() and DropGeometryColumn() can just add/remove the column from the table directly, so this can only happen on tables for which the current role has SQL permissions. I get it now. They're real data types. Great. That's really what I want. Thanks for all the help. If I decide to go ahead and write some triggers I'll post the results to the wiki. Seeing as how there's plans in the works to address the problem I probably won't do anything at all unless a problem arises at my end. Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Securing postgis
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? Thanks. Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
