3

I have a PostgreSQL table and view:

     CREATE TABLE lines (
      geom geometry(MultiLineString,4326),
      type character varying(254),
      login text,
      gid serial NOT NULL,
      CONSTRAINT lines_pkey PRIMARY KEY (gid))

    CREATE OR REPLACE VIEW lines_view AS 
     SELECT lines.geom,
        lines.type,
        lines.gid
       FROM lines;

    CREATE OR REPLACE RULE add AS
        ON INSERT TO lines_view DO INSTEAD  INSERT INTO lines (geom, type, login)
      VALUES (new.geom, new.type, "current_user"());

    CREATE OR REPLACE RULE del AS
        ON DELETE TO lines_view DO INSTEAD  DELETE FROM lines
        WHERE lines.login = "current_user"()::text AND lines.gid = old.gid;

     CREATE OR REPLACE RULE upd AS
      ON UPDATE TO lines_view DO INSTEAD  UPDATE lines SET geom = new.geom, type = new.type, login = "current_user"()
      WHERE lines.login = "current_user"()::text AND lines.gid = new.gid;

So, problem is when I load this lines_view in QGIS as layer and add lines, after saving I cannot use "node tool" to new lines ("Node tool: could not snap to a segment on the current layer"). But when I use move tool on them (just select "move feature(s)" button and click on new line) "node tool" start working as usual. Can some one help me with this?

  • are you trying to edit a view? – DPSSpatial Aug 18 '15 at 15:18
  • Yes, I add view as a layer to QGIS and edit that layer. – Meklen Aug 19 '15 at 5:31
1

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.

  • Thanks a lot, it really help me. Never use triggers, but will from now ) – Meklen Aug 20 '15 at 7:23
  • @Meklen Triggers are very useful when you need them, but they do come with some overhead. So use them sparingly. When possible, it's better to avoid them. In this case, I don't see a way around them until 9.5 comes out with row level permissions. – jpmc26 Aug 20 '15 at 7:25
4

You didn't mention qgis nor postgis version, qgis 2.10 doesn't let you edit table without a primary key - which would be the case of your view.

  • I'm using QGIS v. 2.8.2, I'll try 2.10 today, but my view have primary key (gid from lines table), though QGIS does let me add my view table only from DB Manager, not from Browser, it gives "Layer is not valid" mistake. PostgreSQL v. 1.20.0, PostGIS v. 2.1.7 – Meklen Aug 19 '15 at 5:37
  • Views in Postgresql can't have primary keys. If you have a look into messages PostGIS sends when you try to add the layer, you probably see something like "No primary key defined for table_name". – Michal Zimmermann Aug 19 '15 at 5:39
  • Thanks for help, but I've read [link]gis.stackexchange.com/questions/25699/…, and it is not my case. I can add my view layer from DB Manager without primary key specify. And it works just fine, until I press the save button. After save QGIS "Node tool" cant find my new lines, but "Move feature" tool can. – Meklen Aug 19 '15 at 5:54
  • Could you share message log from your postgresql database? The one from QGIS View -> Panels -> Log Messages would suffice. – Michal Zimmermann Aug 19 '15 at 6:16
  • I'll be glad to, but then I load my view layer with DB Manager I got nothing in logs, I just have General, Plugins and Python warnings sections in Log Messages window. – Meklen Aug 19 '15 at 6:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.