Postgres Pro
Downloads
Home   >   mailing lists

Thread: Clarity on how LOCK interacts with INHERIT

Clarity on how LOCK interacts with INHERIT

From
Robins Tharakan
Date:
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

Re: Clarity on how LOCK interacts with INHERIT

From
Simon Riggs
Date:
On 12 April 2013 21:59, Robins Tharakan <tharakan@gmail.com> wrote:

> postgres=# CREATE TABLE lock_tbl6 (b BIGINT) INHERITS (lock_tbl5);
> CREATE TABLE

> postgres=> LOCK TABLE lock_tbl6 * IN access EXCLUSIVE MODE;
> LOCK TABLE

> 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

No problem.

LOCK * locks only the descendants, so tbl5 is not locked since it is
not a decendant of tbl6.

--Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services