Re: Read only column ?

On 17/01/2010 10:18 AM, John R Pierce wrote:
> Tom Lane wrote:
>> Well, you could have the triggers throw errors instead of being silent
>> about it.
>>
>
> otoh, do you really want trigger code checking for permissions? ugh.

Before column permissions, it was all you could do :-(

I'm LOVING col permissions, especially now that I've got Hibernate
playing properly with them. So much ugly, error-prone trigger code is
gone. Having to test IS DISTINCT FROM or IS NOT NULL and RAISE NOTICE
handling all the different cases of insert/update/delete wasn't pretty.

One thing, though: Tom said that you can just revoke col permissions
from the cols of interest. I suspect that how it was worded could be
confusing to a reader not familiar with how the permissions work. You can't:

GRANT SELECT, INSERT, UPDATE, DELETE ON sometable TO user;
REVOKE SELECT, INSERT, UPDATE (protectedcol) ON sometable FROM user;

since there's no column-level GRANT to REVOKE. Column-level REVOKE can't
convert a table-level GRANT to a selective column-level GRANT. Instead,
you must GRANT access to the list of columns the user *should* be
allowed to modify.

GRANT DELETE ON sometable TO user;
GRANT SELECT, INSERT, UPDATE (col1, col2, col3) ON sometable TO user;

The manual goes into this a bit.

--
Craig Ringer