Waiting for 8.4 – column level privileges.

On 22nd of January, Tom Lane committed patch by Stephen Frost, which adds column level privileges:

Log Message:
Support column-level privileges, as required by SQL standard.
Stephen Frost, with help from KaiGai Kohei and others

In case you don't understand – it means that you can now grant/revoke privileges to specific columns of tables, not whole tables (this is still of course possible).

Let's try this on some simple example:

    id serial PRIMARY KEY,
    a text,
    b text,
    c timestamptz NOT NULL DEFAULT now()

I created the table using user “depesz", which is also superuser.

Afterwards, let's insert some dummy data:

# INSERT INTO test (a, b)
    SELECT 'a : ' || i::text, 'b : ' || i::text FROM generate_series(1,10) i;

Now, let's create “test" account, and do sanity check:

# \c - test
You are now connected TO DATABASE "depesz" AS USER "test".
> SELECT * FROM test;
ERROR:  permission denied FOR relation test
> \z test
                          Access privileges
 Schema | Name | TYPE  | Access privileges | COLUMN access privileges
 public | test | TABLE |                   |
(1 ROW)

Now, let's grant test user select privilege on id, a and c columns:

# GRANT SELECT ( id, a, c ) ON test TO test;

and, now from user test:

> SELECT id, a, c FROM test LIMIT 2;
 id |   a   |               c
  1 | a : 1 | 2009-01-31 15:15:19.609873+01
  2 | a : 2 | 2009-01-31 15:15:19.609873+01
(2 ROWS)

Which nicely works. But what would happen if I'd select * ?

> SELECT * FROM test;
ERROR:  permission denied FOR relation test

Ok. Let's grant the missing column select privilege:

# GRANT SELECT ( b ) ON test TO test;

And, now we can select *:

 id |   a   |   b   |               c
  1 | a : 1 | b : 1 | 2009-01-31 15:15:19.609873+01
  2 | a : 2 | b : 2 | 2009-01-31 15:15:19.609873+01
(2 ROWS)

\z output looks interestingly:

> \z
                                 Access privileges
 Schema |    Name     |   Type   |   Access privileges   | Column access privileges
 public | test        | table    | depesz=arwdDxt/depesz | id:
                                                         :   test=r/depesz
                                                         : a:
                                                         :   test=r/depesz
                                                         : b:
                                                         :   test=r/depesz
                                                         : c:
                                                         :   test=r/depesz
 public | test_id_seq | sequence |                       |
(2 rows)

Updates are pretty obvious, if you don't have update right to given column, you cannot update it:

# GRANT UPDATE (b,c) ON test TO test;
> UPDATE test SET b = 'x', c = now() WHERE id = 1;
> UPDATE test SET b = 'x', c = now(), a = 'q' WHERE id = 1;
ERROR:  permission denied FOR relation test

Well – that's about it. I would prefer the code to work around “select *" in case not all columns are selectable, but I think the current situation is better in this way, that it is not ambiguous.

6 thoughts on “Waiting for 8.4 – column level privileges.”

  1. Very nice feature! 🙂

    This is probably a stupid question, but does this also apply to INSERT privileges? If so, I’m assuming that if, using your scenario above, you granted the user similar insert privileges (grant select ( id, a, c ) on test to test;), but column “b” was NOT NULL and didn’t have a default value, they couldn’t actually insert data at all.

    And obviously this doesn’t apply to DELETE.

  2. @Thom Brown:
    Ah, sorry, I forgot to add it.

    Basically you can grant INSERT on separate columns, and it works like this:

    if you’ll try to insert you have to specify columns you want to insert to. If you have rights to all of them, and there is no other column with “not null” and no default – insert will work.

    This is actually great thing for all kind of “id” or “entered_on” columns – you just don’t grant insert/update privilege on them, and just provide sane defaults.

  3. Wow. I was unaware of that SQL standard requirement. This is nice. If nothing else, it will help prevent the use of ‘select * from’, which constantly causes integration problems. I suppose a lot of legacy code could have problems if this feature is actually used, though.

  4. I’d assume legacy code is running on a matching legacy version of postgres without that feature.

    Good timing getting this feature, as it was mentioned in the “pet peeves” thread on the mailing list not long ago 🙂

  5. 2017-08-15 14:59:07 IST ERROR: unrecognized privilege type: “Select,Insert,Update,Delete,Trigger,References”
    2017-08-15 14:59:07 IST STATEMENT: select * from (select tablename,schemaname,(‘”‘||schemaname ||'”‘||’.’||'”‘||tablename ||'”‘)as tablefilter from pg_tables where schemaname in (‘cosec1’)) data1 where has_table_privilege(‘cosec1′,tablefilter,’Select,Insert,Update,Delete,Trigger,References’)

    kindly any body solve this issue ..i will be very thankful for this.

  6. @premchand:

    1. please note that my blog is not support channel for postgresql (consider mailing lists and/or irc)
    2. your query is broken anyway – generating identifiers using string concatenation is dangerous and inherently wrong.

Comments are closed.