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
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 |