PostgreSQL Prevent updating columns EVERYONE

Question

I want to prevent users (everyone) updating a particular column of a topic to prevent circular dependencies.

CREATE TABLE Topic(
    id          serial    NOT NULL PRIMARY KEY,
    contenxt    text      DEFAULT NULL, -- can be freely updated
    Dependency1 serial    REFERENCES Topic(id) ON DELETE RESTRICT, -- CAN'T be updated
    Dependency2 serial    REFERENCES Topic(id) ON DELETE RESTRICT, -- CAN'T be updated
);

DENY UPDATE ON Topic(Dependency1) TO *; -- Here
DENY UPDATE ON Topic(Dependency2) TO *;

But after trying few variants it seems to report always some syntax error. It starts to be boring to fix that. Alternative solutions are welcome, but I think this solution is reasonably simple (given you know exact syntax for that u.u).

In the comments a trigger is suggested, but I have no idea how to achieve that with a trigger.

Answer 1

PostgreSQL supports column-level privileges. You probably need something along these lines.

grant select(id, dependency1, dependency2), update(id) on topic to public;