Clarity on how LOCK interacts with INHERIT

From: Robins Tharakan <tharakan(at)gmail(dot)com>
To: pgsql-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Clarity on how LOCK interacts with INHERIT
Date: 2013-04-12 20:59:27
Message-ID: CAEP4nAyx_cmyF8Wzka0D3TkV2nDw9DsLLG9rsTaBCo1gT=ZWcg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Simon Riggs 2013-04-12 21:09:07 Re: Clarity on how LOCK interacts with INHERIT
Previous Message Richard Broersma 2013-04-12 19:52:16 Re: Restrict FOREIGN KEY to a part of the referenced table