Обсуждение: New column modifier?

On 04/29/2015 10:03 AM, John McKown wrote:
> I am wondering about useful something might be. So I hope ya'll don't
> mind me throwing out for feedback. I am fairly good with standard SQL,
> but not the more advanced DBA things such as TRIGGERs. I am reading good
> book, "PostgreSQL Server Programming" to increase my knowledge. The
> section that I'm on now is about triggers. And how to use them to
> enforce a "read only" column. So I'm wondering if such is very common? I
> can see some uses for it with things which "should not change", such as
> a system generated date/time in an audit log. Given how much PostgreSQL
> extends beyond the ANSI standard, I'm wondering if a new column
> modifier, perhaps "WORM" (Write Once, Read Many) might be of any real
> use. Or is this "need" just not prevalent enough to require such a thing?
>
> just thinking. But maybe not productively.

See here

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
     [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
     ON [ TABLE ] table_name [, ...]
     TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]


http://www.postgresql.org/docs/9.4/static/sql-grant.html

and

http://www.postgresql.org/docs/9.4/static/sql-revoke.html

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

On 4/29/15 1:05 PM, Joshua D. Drake wrote:
>>
>> [ discussion about read-only columns ]
>
> See here
>
> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )

What I don't like about relying on GRANT is that the table owner gets to
bypass all that, as does a superuser. So when I'm serious about an
operation (insert, update or delete) not happening on something, I put a
trigger in place. Obviously a table owner or SU can always disable that,
but they can't do it accidentally. I would love the ability to restrict
operations both at a table and a column level.

BTW, John, you mentioned RULEs elsewhere... be very careful about using
those. They're incredibly easy to get wrong and generally not worth the
trouble.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com