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; CREATE ROLE # \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; GRANT
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; GRANT
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; GRANT > UPDATE test SET b = 'x', c = now() WHERE id = 1; UPDATE 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.
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.
@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.
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.
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 🙂
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.
@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.