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

BUG #6421: Revoke column level privilage

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 (view raw or flat)
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



Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2012-01-31 02:03:12
Subject: Re: BUG #6421: Revoke column level privilage
Previous:From: tom.mcglynnDate: 2012-01-30 22:30:00
Subject: BUG #6420: Incorrect description of Postgres time system

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