10

I have a PostgreSQL (9.4) database that limits access to records depending upon the current user, and tracks changes made by the user. This is achieved through views and triggers, and for the most part this works well, but I'm having problems with views that require INSTEAD OF triggers. I have tried to reduce the problem down, but I apologise in advance that this is still quite long.

The Situation

All connections to the database are made from a web front-end via a single account dbweb. Once connected, the role is changed via SET ROLE to correspond to the person using the web interface, and all such roles belong to the group role dbuser. (See this answer for details). Let's assume the user is alice.

Most of my tables are placed in a schema that here I'll call private and belong to dbowner. These tables are not directly accessible to dbuser, but are to another role dbview. E.g:

SET SESSION AUTHORIZATION dbowner;
CREATE TABLE private.incident
(
  incident_id serial PRIMARY KEY,
  incident_name character varying NOT NULL,
  incident_owner character varying NOT NULL
);
GRANT ALL ON TABLE private.incident TO dbview;

Availability of specific rows to the current user alice is determined by other views. A simplified example (which could be reduced, but needs to be done this way to support more general cases) would be:

-- Simplified case, but in principle could join multiple tables to determine allowed ids
CREATE OR REPLACE VIEW usr_incident AS 
 SELECT incident_id
   FROM private.incident
  WHERE incident_owner  = current_user;
ALTER TABLE usr_incident
  OWNER TO dbview;

Access to the rows is then provided through a view that is accessible to dbuser roles such as alice:

CREATE OR REPLACE VIEW public.incident AS 
 SELECT incident.*
   FROM private.incident
  WHERE (incident_id IN ( SELECT incident_id
           FROM usr_incident));
ALTER TABLE public.incident
  OWNER TO dbview;
GRANT ALL ON TABLE public.incident TO dbuser;

Note that because only the one relation appears in the FROM clause, this sort of view is updatable without any additional triggers.

For logging, another table exists to record which table is changed and who changed it. A reduced version is:

CREATE TABLE private.audit
(
  audit_id serial PRIMATE KEY,
  table_name text NOT NULL,
  user_name text NOT NULL
);
GRANT INSERT ON TABLE private.audit TO dbuser;

This is populated via triggers placed on each of the relations that I wish to track. For instance, an example for private.incident limited to just inserts is:

CREATE OR REPLACE FUNCTION private.if_modified_func()
  RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO private.audit (table_name, user_name)
        VALUES (tg_table_name::text, current_user::text);
        RETURN NEW;
    END IF;
END;
$BODY$
  LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION private.if_modified_func() TO dbuser;

CREATE TRIGGER log_incident
AFTER INSERT ON private.incident
FOR EACH ROW
EXECUTE PROCEDURE private.if_modified_func();

So now if alice inserts into public.incident, a record ('incident','alice') appears in audit.

The Problem

This approach hits problems when the views become more complicated and need INSTEAD OF triggers to support inserts.

Let's say I have two relations, for instance representing entities involved in some many-to-one relationship:

CREATE TABLE private.driver
(
  driver_id serial PRIMARY KEY,
  driver_name text NOT NULL
);
GRANT ALL ON TABLE private.driver TO dbview;

CREATE TABLE private.vehicle
(
  vehicle_id serial PRIMARY KEY,
  incident_id integer REFERENCES private.incident,
  make text NOT NULL,
  model text NOT NULL,
  driver_id integer NOT NULL REFERENCES private.driver
);
GRANT ALL ON TABLE private.vehicle TO dbview;

Assume that I don't want to expose the details other than the name of private.driver, and so have a view that joins the tables and projects the bits I want to expose:

CREATE OR REPLACE VIEW public.vehicle AS 
 SELECT vehicle_id, make, model, driver_name
   FROM private.driver
   JOIN private.vehicle USING (driver_id)
  WHERE (incident_id IN ( SELECT incident_id
               FROM usr_incident));
ALTER TABLE public.vehicle OWNER TO dbview;
GRANT ALL ON TABLE public.vehicle TO dbuser;

In order for alice to be able to insert into this view a trigger has to be provided, e.g.:

CREATE OR REPLACE FUNCTION vehicle_vw_insert()
  RETURNS trigger AS
$BODY$
DECLARE did INTEGER;
   BEGIN
     INSERT INTO private.driver(driver_name) VALUES(NEW.driver_name) RETURNING driver_id INTO did;
     INSERT INTO private.vehicle(make, model, driver_id) VALUES(NEW.make_id,NEW.model, did) RETURNING vehicle_id INTO NEW.vehicle_id;
     RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql SECURITY DEFINER;
ALTER FUNCTION vehicle_vw_insert()
  OWNER TO dbowner;
GRANT EXECUTE ON FUNCTION vehicle_vw_insert() TO dbuser;

CREATE TRIGGER vehicle_vw_insert_trig
INSTEAD OF INSERT ON public.vehicle
FOR EACH ROW
EXECUTE PROCEDURE vehicle_vw_insert();

The problem with this is that the SECURITY DEFINER option in the trigger function causes it to be run with current_user set to dbowner, so if alice inserts a new record into the view the corresponding entry in private.audit records the author to be dbowner.

So, is there a way to preserve current_user, without giving the dbuser group role direct access to the relations in schema private?

Partial Solution

As suggested by Craig, using rules rather than triggers avoids changing the current_user. Using the above example, the following can be used in place of the update trigger:

CREATE OR REPLACE RULE update_vehicle_view AS
  ON UPDATE TO vehicle
  DO INSTEAD
     ( 
      UPDATE private.vehicle
        SET make = NEW.make,
            model = NEW.model
      WHERE vehicle_id = OLD.vehicle_id
       AND (NEW.incident_id IN ( SELECT incident_id
                   FROM usr_incident));
     UPDATE private.driver
        SET driver_name = NEW.driver_name
       FROM private.vehicle v
      WHERE driver_id = v.driver_id
      AND vehicle_id = OLD.vehicle_id
      AND (NEW.incident_id IN ( SELECT incident_id
                   FROM usr_incident));               
   )

This preserves current_user. Supporting RETURNING clauses can be a bit hairy, though. Furthermore, I could find no safe way to use rules to simultaneously insert into both tables in order to handle the use of a sequence for driver_id. The easiest way would have been to use a WITH clause in an INSERT (CTE), but these aren't allowed in conjunction with NEW (error: rules cannot refer to NEW within WITH query), leaving one to resort to lastval() which is strongly discouraged.

4

So, is there a way to preserve current_user, without giving the dbuser group role direct access to the relations in schema private?

You may be able to use a rule, rather than an INSTEAD OF trigger, to provide write access through the view. Views always act with the security rights of the view creator rather than the querying user, but I don't think current_user changes.

If your application connects directly as the user, you can check session_user instead of current_user. This also works if you connect with a generic user then SET SESSION AUTHORIZATION. It won't work if you connect as a generic user then SET ROLE to the desired user, though.

There is no way to obtain the immediately prior user from within a SECURITY DEFINER function. You can only get the current_user and session_user. A way to get the last_user or a stack of user identities would be nice, but is not currently supported.

  • Aha, hadn't dealt with rules before, thanks. SET SESSION could be even better but I think the initial login user would need to have superuser privileges, which smells dangerous. – beldaz Aug 12 '15 at 3:36
  • @beldaz Yeah. It's the big problem with SET SESSION AUTHORIZATION. I really want something between it and SET ROLE, but at the moment there's no such thing. – Craig Ringer Aug 12 '15 at 3:36
1

Not a complete answer, but it would not fit into a comment.

lastval() & currval()

What makes you think lastval() is discouraged? Seems like a misunderstanding.

In the referenced answer, Craig strongly recommends to use a trigger instead of the rule in a comment. And I agree - except for your special case, obviously.

The answer strongly discourages the use of currval() - but that seems to be a misundertstanding. There is nothing wrong with lastval() or rather currval(). I left a comment with the referenced answer.

Quoting the manual:

currval

Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

So this is safe with concurrent transactions. The only possible complication could arise from other triggers or rules that might call the same trigger inadvertently - which would be a very unlikely scenario and you have complete control over which triggers / rules you install.

However, I not sure the sequence of commands is preserved within rules (even though currval() is as volatile function). Also, a multi-row INSERT might get you out of sync. You could split up your RULE into two rules, only the second being INSTEAD. Remember, per documentation:

Multiple rules on the same table and same event type are applied in alphabetical name order.

I did not investigate further, out of time.

DEFAULT PRIVILEGES

As for:

SET SESSION AUTHORIZATION dbowner;
...
GRANT ALL ON TABLE private.incident TO dbview;

You may be interested instead:

ALTER DEFAULT PRIVILEGES FOR ROLE dbowner IN SCHEMA private
   GRANT ALL ON TABLES TO dbview;

Related:

  • Thanks, I was indeed wrong in my understanding of lastval and currval, as I didn't realise they were local to a session. I do in fact use default privileges in my real schema, but the per-table ones were from copying and pasting from the dumped DB. I've concluded that restructuring the relations is easier than messing about with rules, neat though they are, since I can see them being a headache later. – beldaz Aug 14 '15 at 3:00
  • @beldaz: I think that's a good decision. Your design was getting too complicated. – Erwin Brandstetter Aug 14 '15 at 8:25

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.