January 31st, 2009 by depesz | Tags: , , , , , | 6 comments »
Did it help? If yes - maybe you can help me?

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:

create table test (
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:

# create user test;
# \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 *:

> select * from test limit 2;
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.

  1. 6 comments

  2. # Thom Brown
    Jan 31, 2009

    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.

  3. Feb 1, 2009

    @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.

  4. # chris
    Feb 3, 2009

    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.

  5. # moltonel
    Feb 4, 2009

    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 🙂

  6. Aug 15, 2017

    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.

  7. Aug 16, 2017


    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.

Leave a comment