Assuming the only reason you need these views is for controlling the login
column value and who can update what rows, then you don't actually need a view here.
Start by creating an INSERT
trigger to always set the login
column:
CREATE OR REPLACE FUNCTION set_login_to_current_user()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
NEW.login = current_user;
RETURN NEW;
END
$$
;
CREATE TRIGGER insert_on_lines
BEFORE INSERT ON lines
FOR EACH ROW
EXECUTE PROCEDURE set_login_to_current_user()
;
(Note that this is a little different than a DEFAULT
value. A DEFAULT
would only kick in if no value was provided. This trigger overrides the value even if the user tried to specify one.)
Now add an update trigger that prevents modification of rows not belonging to the current user:
CREATE OR REPLACE FUNCTION prevent_update_for_other_user()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF OLD.login != current_user THEN
RAISE EXCEPTION 'Attempted to edit row belonging to another user';
ELSE
NEW.login = current_user;
RETURN NEW;
END IF;
END
$$
;
CREATE TRIGGER update_on_lines
BEFORE UPDATE ON lines
FOR EACH ROW
EXECUTE PROCEDURE prevent_update_for_other_user()
;
And last, add a trigger that prevents delete:
CREATE OR REPLACE FUNCTION prevent_delete_for_other_users()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF OLD.login != current_user THEN
RAISE EXCEPTION 'Attempted to delete row belonging to another user';
ELSE
RETURN OLD;
END IF;
END
$$
;
CREATE TRIGGER delete_on_lines
BEFORE DELETE ON lines
FOR EACH ROW
EXECUTE PROCEDURE prevent_delete_for_other_users()
;
I've tested that this works correctly with PostgreSQL 9.3/PostGIS 2.1.4 and QGIS 2.8.1, but since it's only using basic PostgreSQL functionality, I expect this to work in higher versions of everything.
Note that in the update and delete cases, I raise an error if a user tries to modify a row belonging to another user. This is slightly different than what you posted, where you just silently do nothing. I don't advise this. When silently rejecting the command, the user may think their changes have been applied even though they haven't, and the user may be very confused when they later find their changes are "missing." By raising an error, the user knows immediately that a change they attempted has been rejected and can undo to edits accordingly. If you really need to silently continue, you can replace the RAISE
commands with
RETURN NULL;
to silently prevent the edit or delete.
I also want to note that this doesn't exactly represent "security" all by itself, but it can be part of the solution in combination with other things. You need to combine this with appropriate permissions to prevent users from modifying the triggers or trigger functions. Importantly, you can't filter what rows a particular user is able to view (but your question doesn't do that anyway). Also worth noting is that PG 9.5 is going to have "row level security" out of the box, which would be an even better way of solving this.