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?