The following bug has been logged on the website:
Bug reference: 7557
Logged by: Piotr Czachur
Email address: zim...@gmail.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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
