Skip site navigation (1) Skip section navigation (2)

BUG #7557: Transaction sees outdated grants.

From: zimnyx(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7557: Transaction sees outdated grants.
Date: 2012-09-19 11:09:57
Message-ID: E1TEIAH-00052c-RW@wrigleys.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged on the website:

Bug reference:      7557
Logged by:          Piotr Czachur
Email address:      zimnyx(at)gmail(dot)com
PostgreSQL version: 9.2.0
Operating system:   Ubuntu 12.04.1 LTS
Description:        

After upgrading from 9.1 to 9.2 I noticed that behaviour of veryfying table
permissions during transaction has changed, and may be just wrong.
I give you a simple example how this behaviour has changed from 9.1 to 9.2:


-- start admin session
-- sudo -u postgres psql postgres
DROP DATABASE IF EXISTS test;
DROP ROLE IF EXISTS joe;
CREATE USER joe WITH PASSWORD 'joe';
CREATE DATABASE test;
\c test
CREATE TABLE kingdoms
(
  id serial NOT NULL,
  name character varying,
  CONSTRAINT university_pkey PRIMARY KEY (id)
);
CREATE TABLE wizards
(
  id serial NOT NULL,
  name character varying,
  kingdom_id integer,
  CONSTRAINT wizards_pkey PRIMARY KEY (id),
  CONSTRAINT wizards_kingdom_id_fkey FOREIGN KEY (kingdom_id) REFERENCES
kingdoms (id)
);

GRANT ALL ON DATABASE test TO joe;
GRANT ALL ON ALL TABLES IN SCHEMA public TO joe;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO joe;


-- start joe session
-- psql -h 127.0.0.1 -U joe test
BEGIN;
INSERT INTO "kingdoms" ("name") VALUES ('Mordor');

-- jump back to admin session
REVOKE INSERT ON table "kingdoms" FROM joe;

-- jump back to joe session
INSERT INTO "wizards" ("name", "kingdom_id") VALUES ('Gandalf', 1);

-- jump back to admin session
GRANT INSERT ON table "kingdoms" TO joe;

-- jump back to joe session (HERE IS THE ERROR)
INSERT INTO "kingdoms" ("name") VALUES ('Mordor');
-- ERROR:  permission denied for relation kingdoms.
-- Why permission denied? We've granted INSERT in a previous statemtnt.
-- Error appears in 9.2, in 9.1 there is no error.
ROLLBACK;
INSERT INTO "kingdoms" ("name") VALUES ('Mordor'); -- INSERT 0 1
-- After rolling back INSERT succeeded.
-- Looks like transaction doesn't always see "fresh" grants.







Responses

pgsql-bugs by date

Next:From: Andres FreundDate: 2012-09-19 11:56:11
Subject: Re: BUG #7546: Backups on hot standby cancelled despite hot_standby_feedback=on
Previous:From: Stuart BishopDate: 2012-09-19 09:33:29
Subject: Re: BUG #7546: Backups on hot standby cancelled despite hot_standby_feedback=on

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group