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.

What about a trigger? With it you can prevent updates also from super user. — user_0
– user_0, Commented Feb 9, 2015 at 17:05
The Topic table has other fields that can be updated ("Content" as text) so the trigger should keep in count only changes to "Dependencies" fields. If not possible just tell I'll move "Content" to another table. It's just I never used a trigger that way so I don't know where to start — CoffeDeveloper
– CoffeDeveloper, Commented Feb 9, 2015 at 17:21
See the answer. In postgresql you can use trigger on columns. You can also work on it to raise message only when value are really different. If needed.. — user_0
– user_0, Commented Feb 9, 2015 at 17:24
DENY isn't used by PostgreSQL, it can't work. Just read the manual about REVOKE (and GRANT): postgresql.org/docs/current/interactive/sql-revoke.html Using a TRIGGER is a very strange work around for something that should be done using the correct permissions. — Frank Heikens
– Frank Heikens, Commented Feb 10, 2015 at 6:47
"But after trying few variants it seems to report always some syntax error. It starts to be boring to fix that." Syntax is available online. It doesn't look like you tried to read it. (PostgreSQL uses revoke, not deny.) — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Feb 10, 2015 at 6:47

Mike Sherrill 'Cat Recall' · Accepted Answer · 2015-02-09 17:25:10Z

13

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

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

answered Feb 9, 2015 at 17:25

Mike Sherrill 'Cat Recall'

96.6k20 gold badges134 silver badges195 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user_0 · Accepted Answer · 2015-02-09 17:23:22Z

12

Try this:

CREATE OR REPLACE FUNCTION fnprevent_update()
  RETURNS trigger AS
$BODY$
    BEGIN
        RAISE EXCEPTION 'no way';
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



CREATE TRIGGER trg_prevent_update
  BEFORE UPDATE OF dependency1, dependency2
  ON topic
  FOR EACH ROW
  EXECUTE PROCEDURE fnprevent_update();

Just customize the "no way" message.

answered Feb 9, 2015 at 17:23

user_0

3,38323 silver badges38 bronze badges

6 Comments

CoffeDeveloper Over a year ago

Non constructive critic, if you know an answer exists, show it.

user_0 Over a year ago

@FrankHeikens, actually the trigger will prevent data modifications also from superuser. This will prevent mistakes and future misconfigurations in permissions. It is not so bad :-)

Frank Heikens Over a year ago

@user_0: No it doesn't, a superuser can always update a record. He might have to change his permissions or disable a trigger, but it's always possible. A trigger is a bad idea for this issue when you have permissions to solve it.

user_0 Over a year ago

@FrankHeikens Yes, he can. But he NEEDS to disable trigger. This means a conscious decision. With permission he just can. Am I wrong? I don't pretend this to be the solution, but it seems to me the best solution that fit the case. The problem with your proposal is: How can you prevent SUPERUSER doing accidental updates?

danius Over a year ago

Assuming super users are a solely 1 as it happens in small/medium projects and that he or she has all the know-how of the project is not always desired. Not assuming he or she will be top-class dba either. The permissions solution assumes that, is the orthodox solution and it works for the 99%. But given solution is the heterodox one for heterodox projects, normally huge ones. Not bad per se. It's like disabling read or write for root or sudo group users in evironments. Yeah they can change this and do it anyway, but when they see they were disabled they are warned that it is for a reason.

|

Collectives™ on Stack Overflow

PostgreSQL Prevent updating columns EVERYONE

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related