From: | bdmytrak(at)eranet(dot)pl |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #6421: Revoke column level privilage |
Date: | 2012-01-30 22:35:05 |
Message-ID: | E1RrzoX-0005KH-U2@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 6421
Logged by: Bartosz Dmytrak
Email address: bdmytrak(at)eranet(dot)pl
PostgreSQL version: 9.1.2
Operating system: Mandriva 2011 64 bit
Description:
Cannot revoke column level privilages.
Scenario:
1. as „postgres” user create a test table:
CREATE TABLE public."tblTest"
(
"RowId" serial NOT NULL,
"Column1" text,
"Column2" integer,
"Column3" integer,
CONSTRAINT "tblTest_pkey" PRIMARY KEY ("RowId")
)
WITH (
OIDS=FALSE
);
ALTER TABLE public."tblTest"
OWNER TO postgres;
GRANT ALL ON TABLE public."tblTest" TO postgres;
2. Grant privilages to different user, still using postgres acount:
GRANT UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON TABLE public."tblTest"
TO "otherUser";
GRANT SELECT ON TABLE public."tblTest" TO "otherUser" WITH GRANT OPTION;
3. Check privilages (assumption: there is the only one table named “tblTest”
in the database):
SELECT oid, relname, relacl FROM pg_class WHERE relname = 'tblTest';
oid | relname | relacl
-------+---------+--------------------------------------------------------
36385 | tblTest | {postgres=arwdDxt/postgres,otherUser=ar*wdxt/postgres}
(1 row)
and also for columns:
SELECT attrelid, attname, attacl FROM pg_attribute a
INNER JOIN pg_class c ON (a.attrelid = c.oid)
WHERE c.relname = 'tblTest';
attrelid | attname | attacl
----------+----------+--------
36385 | tableoid |
36385 | cmax |
36385 | xmax |
36385 | cmin |
36385 | xmin |
36385 | ctid |
36385 | RowId |
36385 | Column1 |
36385 | Column2 |
36385 | Column3 |
(10 rows)
Everything looks good.
4. login as “otherUser” and add column level privilages
myDatabase=> GRANT ALL("Column1") ON public."tblTest" TO public;
GRANT
5. Check privilages again (as postgres)
myDatabase=# SELECT oid, relname, relacl FROM pg_class
WHERE relname = 'tblTest';
oid | relname | relacl
-------+---------+--------------------------------------------------------
36385 | tblTest | {postgres=arwdDxt/postgres,otherUser=ar*wdxt/postgres}
(1 row)
myDatabase=# SELECT attrelid, attname, attacl FROM pg_attribute a
INNER JOIN pg_class c ON (a.attrelid = c.oid)
WHERE c.relname = 'tblTest';
attrelid | attname | attacl
----------+----------+----------------
36385 | tableoid |
36385 | cmax |
36385 | xmax |
36385 | cmin |
36385 | xmin |
36385 | ctid |
36385 | RowId |
36385 | Column1 | {=r/otherUser}
36385 | Column2 |
36385 | Column3 |
(10 rows)
Still looks good.
6. REVOKE Column level privilages from public on table using “postgres”
account:
myDatabase=# REVOKE ALL("Column1") ON public."tblTest" FROM public;
REVOKE
7. Check column privilages (as postgres):
myDatabase=# SELECT attrelid, attname, attacl FROM pg_attribute a
INNER JOIN pg_class c ON (a.attrelid = c.oid)
WHERE c.relname = 'tblTest';
attrelid | attname | attacl
----------+----------+----------------
36385 | tableoid |
36385 | cmax |
36385 | xmax |
36385 | cmin |
36385 | xmin |
36385 | ctid |
36385 | RowId |
36385 | Column1 | {=r/otherUser}
36385 | Column2 |
36385 | Column3 |
(10 rows)
or using \dp command:
myDatabase=# \dp public."tblTest"
Access privileges
Schema | Name | Type | Access privileges | Column access
privileges
--------+---------+-------+----------------------------+--------------------------
public | tblTest | table | postgres=arwdDxt/postgres +| Column1:
+
| | | otherUser=ar*wdxt/postgres | =r/otherUser
(1 row)
Looks like privilages has not been revoked.
regards,
Bartek
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-01-31 02:03:12 | Re: BUG #6421: Revoke column level privilage |
Previous Message | tom.mcglynn | 2012-01-30 22:30:00 | BUG #6420: Incorrect description of Postgres time system |