3

I have a PostgreSQL 8.4.4 database with PostGIS 1.4.2 and I am experiencing a problem where an external GIS system (using embedded CadCorp SIS 7.0) is connecting to the database and taking Access Share Locks on a large number of tables which, as far as I can understand, it should not have privileges to access. The affected tables are all sources for views that are registered in geometry_columns, across a number of schemas. All other tables, whether registered in geometry_columns or not, with or without a geometry column, are unaffected (except those explicitly required by the map project the external system is using). This is frustrating because the connections stay open for a large period of time and prevent a lot of maintenance work that I might like to do.

For example I have a schema: 

CREATE SCHEMA my_schema AUTHORIZATION user_one;  
GRANT ALL ON SCHEMA my_schema TO user_one;

And within that a table: 

CREATE TABLE my_schema.my_table...;  
ALTER TABLE my_schema.my_table OWNER TO user_one;  
GRANT ALL ON TABLE my_schema.my_table TO user_one;

And a view: 

CREATE OR REPLACE VIEW my_schema.vw_my_view AS SELECT... FROM my_schema.my_table;  
ALTER TABLE my_schema.vw_my_view OWNER TO superuser;  
GRANT ALL ON TABLE my_schema.vw_my_view TO superuser;  
GRANT SELECT ON TABLE my_schema.vw_my_view TO user_one;

The roles user_one and user_two are independent but I find that user_two is able to put an Access Share Lock on my_schema.my_table. If I connect to the database as user_two I am denied access to my_schema.my_table as I would expect.

Is there something I'm not doing with the privileges or roles that would prevent this? Why is it only those tables that are the source for views that are registered in geometry_columns that are affected?

Improve this question
10
  • This question appears to be off-topic because it is about Database Administration, which is a better fit for dba.stackexchange.com
    – Devdatta Tengshe
    Commented Feb 14, 2014 at 13:01
  • It is do with administration but as it seems to specifically involve interactions with geometry_columns and only affects tables referenced by spatial views I thought it had a better fit with the GIS community.
    – UrsineWelles
    Commented Feb 14, 2014 at 13:14
  • You can restrict by IP on the POSTGRES side - postgresql.org/docs/8.4/static/auth-pg-hba-conf.html or make the the tables readyonly (or using a view mode). postgresql.org/docs/8.4/static/sql-createview.html
    – Mapperz ♦
    Commented Feb 14, 2014 at 14:34
  • @DevdattaTengshe I had a job that was specify called 'GIS Administrator' this included all Spatial Database setup, Maintenance, Backup, Licensing, Training knowledge etc)
    – Mapperz ♦
    Commented Feb 14, 2014 at 14:38
  • @Mapperz - I do need user_two to access the database so their IP must be allowed to connect; I would like to deny user_two access to particular tables to stop it acquiring locks on them, but, as I understand the above privileges, user_two has not been given access in the first place, either to see the schema, the table, or the view.
    – UrsineWelles
    Commented Feb 14, 2014 at 15:17

1 Answer 1

Reset to default
1

That was fixed in SIS 7.1 two years ago, but apparently not merged into 7.0. If you can email more details to me (martin dot daly at cadcorp dot com) then I'm sure that we can sort something out.

Please include the details of the application that embeds SIS, in case the supplier needs to be involved too.

Improve this answer
5
  • I will contact you directly and include our contacts for the two systems' suppliers. For the benefit of the community could you post more information on how SIS 7.0 is managing to get locks on tables it has no access to please.
    – UrsineWelles
    Commented Mar 3, 2014 at 11:26
  • 1
    SIS uses cursors via libpq, and these are extremely slow unless a transaction is used. Unfortunately the metadata queries that we have to make touch lots of tables, eg those underlying a view, and, because the queries are inside a transaction, that causes the resource to be locked.
    – mpdaly
    Commented Mar 3, 2014 at 15:05
  • Is there something I can do with permissions that would prevent the locking of the tables - the user accounts don't appear to have access to the views and tables, but have I missed something obvious? Or is any user able to issue similar metadata queries on tables to which it does not have access?
    – UrsineWelles
    Commented Mar 3, 2014 at 18:29
  • 1
    For the benefit of GIS SE, the reason that the metadata queries used by SIS 'touch' even tables to which the logged in user does not have rights is that SIS does not know this with an initial "list the geometry columns" query. SIS uses the results of that query to fetch additional table/view metadata (permissions, pk column, etc). It is these secondary queries that (because they are inside a transaction) inadvertently lock the tables. It is simpler (and safer) for us to restart the transaction (which made no changes anyway) than to fiddle with the order or complexity of the metadata queries.
    – mpdaly
    Commented Mar 31, 2014 at 15:07
  • has supplied a replacement file Cadcorp.PostGIS.Dataset.dll which has alleviated the locking issue (except for the tables genuinely in use) for both our systems using embedded CadCorp SIS 7.0. Problem solved!
    – UrsineWelles
    Commented May 13, 2014 at 9:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.