BUG #6421: Revoke column level privilage - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      6421
Logged by:          Bartosz Dmytrak
Email address:      bdmytrak@eranet.pl
PostgreSQL version: 9.1.2
Operating system:   Mandriva 2011 64 bit
Description:=20=20=20=20=20=20=20=20

Cannot revoke column level privilages.

Scenario:
1. as =E2=80=9Epostgres=E2=80=9D 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=3DFALSE
);

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 =E2=80=
=9CtblTest=E2=80=9D
in the database):

SELECT oid, relname, relacl FROM pg_class WHERE relname =3D 'tblTest';

  oid  | relname |                         relacl=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
-------+---------+--------------------------------------------------------
 36385 | tblTest | {postgres=3DarwdDxt/postgres,otherUser=3Dar*wdxt/postgre=
s}
(1 row)

and also for columns:

    SELECT attrelid, attname, attacl FROM pg_attribute a
INNER JOIN pg_class c ON (a.attrelid =3D c.oid)
WHERE c.relname =3D 'tblTest';

 attrelid | attname  | attacl=20
----------+----------+--------
    36385 | tableoid |=20
    36385 | cmax     |=20
    36385 | xmax     |=20
    36385 | cmin     |=20
    36385 | xmin     |=20
    36385 | ctid     |=20
    36385 | RowId    |=20
    36385 | Column1  |=20
    36385 | Column2  |=20
    36385 | Column3  |=20
(10 rows)

Everything looks good.

4. login as =E2=80=9CotherUser=E2=80=9D and add column level privilages
myDatabase=3D> GRANT ALL("Column1") ON public."tblTest" TO public;
GRANT

5. Check privilages again (as postgres)
myDatabase=3D# SELECT oid, relname, relacl FROM pg_class
WHERE relname =3D 'tblTest';
  oid  | relname |                         relacl=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
-------+---------+--------------------------------------------------------
 36385 | tblTest | {postgres=3DarwdDxt/postgres,otherUser=3Dar*wdxt/postgre=
s}
(1 row)

myDatabase=3D# SELECT attrelid, attname, attacl FROM pg_attribute a
INNER JOIN pg_class c ON (a.attrelid =3D c.oid)
WHERE c.relname =3D 'tblTest';
 attrelid | attname  |     attacl=20=20=20=20=20
----------+----------+----------------
    36385 | tableoid |=20
    36385 | cmax     |=20
    36385 | xmax     |=20
    36385 | cmin     |=20
    36385 | xmin     |=20
    36385 | ctid     |=20
    36385 | RowId    |=20
    36385 | Column1  | {=3Dr/otherUser}
    36385 | Column2  |=20
    36385 | Column3  |=20
(10 rows)

Still looks good.

6. REVOKE Column level privilages from public on table using =E2=80=9Cpostg=
res=E2=80=9D
account:

myDatabase=3D# REVOKE ALL("Column1") ON public."tblTest" FROM public;
REVOKE

7. Check column privilages (as postgres):
myDatabase=3D# SELECT attrelid, attname, attacl FROM pg_attribute a
INNER JOIN pg_class c ON (a.attrelid =3D c.oid)
WHERE c.relname =3D 'tblTest';
 attrelid | attname  |     attacl=20=20=20=20=20
----------+----------+----------------
    36385 | tableoid |=20
    36385 | cmax     |=20
    36385 | xmax     |=20
    36385 | cmin     |=20
    36385 | xmin     |=20
    36385 | ctid     |=20
    36385 | RowId    |=20
    36385 | Column1  | {=3Dr/otherUser}
    36385 | Column2  |=20
    36385 | Column3  |=20
(10 rows)

or using \dp command:
myDatabase=3D# \dp public."tblTest"
                                Access privileges
 Schema |  Name   | Type  |     Access privileges      | Column access
privileges=20
--------+---------+-------+----------------------------+-------------------=
-------
 public | tblTest | table | postgres=3DarwdDxt/postgres +| Column1:=20=20=
=20=20=20=20=20=20=20=20=20
    +
        |         |       | otherUser=3Dar*wdxt/postgres |   =3Dr/otherUser
(1 row)

Looks like privilages has not been revoked.

regards,
Bartek