Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is a restatement of a question asked on the GIS Stack Exchange here. Although it has GIS-specific elements it may be be a more general PostgreSQL effect.

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.

Here are the privileges for the schema, table, view and users:

mydb=# \dn+ my_schema
                                      List of schemas
   Name    |  Owner   |  Access privileges   |              Description
-----------+----------+----------------------+---------------------------------------
 my_schema | user_one | user_one=UC/user_one |

mydb-# \dp+ my_schema.my_table
                                          Access privileges
  Schema   |   Name   | Type  |     Access privileges     | Column access privileges
-----------+----------+-------+---------------------------+--------------------------
 my_schema | my_table | table | user_one=arwdDxt/user_one |

mydb-# \dp+ my_schema.vw_my_view
                                       Access privileges
  Schema   |    Name    | Type |      Access privileges      | Column access privileges
-----------+------------+------+-----------------------------+--------------------------
 my_schema | vw_my_view | view | superuser=arwdDxt/superuser |
                               : user_one=r/superuser

mydb-# \du+
                          List of roles
 Role name |  Attributes  | Member of | Description
-----------+--------------+-----------+-------------
 superuser | Superuser    | {}        |
           : Create role
           : Create DB
 user_one  |              | {}        |
 user_two  |              | {}        |

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?

share|improve this question

closed as off-topic by JNK May 13 at 12:22

If this question can be reworded to fit the rules in the help center, please edit the question.

    
Show the actual privileges on the tables please - \dp+ tablename for affected table(s). Also show \du+ output, or at least the two roles of interest and any roles mentioned in their Member of column recursively. Please edit your post with the extra detail and comment here when done. –  Craig Ringer Feb 14 at 22:35
    
@CraigRinger: I've added the \dp+ and \du results to the question. I hope I understood you correctly and got everything down that is required. –  UrsineWelles Feb 17 at 11:36
    
Are these the only schemas, tables, views, users you have? In particular, what can user_two do? –  Colin 't Hart Feb 17 at 13:11
    
@Colin'tHart: Other than in the information schema and pg_catalog, I have more than 1300 tables and around 130 views across 28 schemas. Most, but not all, of these are spatial and registered in the geometry_columns table in the public schema. –  UrsineWelles Feb 17 at 13:57
    
@Colin'tHart: The public schema is the only one which user_two has any privileges on (it has ALL), and user_two has ownership of 60 tables in it and SELECT, INSERT, DELETE, UPDATE privileges on a handful of others (including geometry_columns). There are a small number more active users, two of which have been granted user_one. –  UrsineWelles Feb 17 at 14:19