Hi, The following when run in PostgreSQL 9.2.x seems to tell that if I have two tables A and B, such that if B inherits A, then, even if I don't have any rights on A, I can get an ACCESS EXCLUSIVE LOCK on Table A via Table B.
This isn't necessarily wrong, I just want be sure that this is what PostgreSQL allows us to do. Sample SQL: postgres=# CREATE SCHEMA lock_schema1; CREATE SCHEMA postgres=# SET search_path = lock_schema1; SET postgres=# CREATE ROLE lock_rol5; CREATE ROLE postgres=# CREATE TABLE lock_tbl5 (a BIGINT); CREATE TABLE postgres=# CREATE TABLE lock_tbl6 (b BIGINT) INHERITS (lock_tbl5); CREATE TABLE postgres=# GRANT USAGE ON SCHEMA lock_schema1 TO lock_rol5; GRANT postgres=# GRANT ALL ON TABLE lock_tbl6 TO lock_rol5; GRANT postgres=# REVOKE ALL ON TABLE lock_tbl5 FROM lock_rol5; REVOKE postgres=# SET ROLE lock_rol5; SET postgres=> SET search_path=lock_schema1; SET postgres=> BEGIN TRANSACTION; BEGIN postgres=> LOCK TABLE ONLY lock_tbl6 IN access EXCLUSIVE MODE; LOCK TABLE postgres=> ROLLBACK; ROLLBACK postgres=> BEGIN TRANSACTION; BEGIN postgres=> LOCK TABLE lock_tbl6 * IN access EXCLUSIVE MODE; LOCK TABLE postgres=> ROLLBACK; ROLLBACK postgres=> BEGIN TRANSACTION; BEGIN postgres=> LOCK TABLE lock_tbl5 IN access EXCLUSIVE MODE; ERROR: permission denied for relation lock_tbl5 STATEMENT: LOCK TABLE lock_tbl5 IN access EXCLUSIVE MODE; ERROR: permission denied for relation lock_tbl5 postgres=> ROLLBACK; ROLLBACK postgres=> RESET ROLE; RESET postgres=# DROP TABLE lock_tbl6; DROP TABLE postgres=# DROP TABLE lock_tbl5; DROP TABLE postgres=# REVOKE ALL ON SCHEMA lock_schema1 FROM lock_rol5; REVOKE postgres=# DROP ROLE lock_rol5 ; DROP ROLE postgres=# Thanks -- Robins Tharakan
