Re: Enforcing serial uniqueness?

Tom Lane wrote:
[...]
> I think the solution for you is to use BEFORE triggers as suggested
> upthread.  The BEFORE INSERT trigger function should be SECURITY DEFINER
> and owned by a user who has permission to NEXTVAL the sequence.  The id
> column should probably be declared plain integer (or bigint), not
> SERIAL, because there's no percentage in setting a default that's just
> going to be overwritten by the trigger.

Wrote this up today and it works great - currval() is still
session-local which I didn't expect would work with the sequence behind
a security definer and updated by trigger.  The only thing missing,
which is quite minor, is I can't detect on INSERT if the caller
attempted to override the default with the default itself (e.g, INSERT
INTO foo(id) VALUES(id's default)), so they won't properly get an
exception in that case.  Not sure if there's a way to catch that.

In case it helps anyone else running into this thread, here's the solution:


-- Create a sequence that your normal users can read but not update.
CREATE SEQUENCE foo_id_seq;
GRANT SELECT ON foo_id_seq TO GROUP (normal user group);

-- Create a table where 'id' will be treated as serial.
CREATE TABLE foo(id integer NOT NULL DEFAULT 0 PRIMARY KEY, something TEXT);
GRANT SELECT, INSERT, UPDATE, DELETE ON foo TO GROUP (normal user group);

-- On INSERT, fill id from the sequence - creator has UPDATE permission.
-- Block attempts to force the id.
CREATE OR REPLACE FUNCTION foo_id_insert_procedure() RETURNS trigger
SECURITY DEFINER AS '
   BEGIN
     IF NEW.id != 0 THEN
       RAISE EXCEPTION ''Setting id to a non-default is not allowed'';
     ELSE
       NEW.id := nextval(''foo_id_seq'');
     END IF;
     RETURN NEW;
   END;
' LANGUAGE plpgsql;

-- Block all UPDATEs to the id.
CREATE OR REPLACE FUNCTION foo_id_update_procedure() RETURNS trigger
SECURITY DEFINER AS '
   BEGIN
     IF NEW.id != OLD.id THEN
       RAISE EXCEPTION ''Setting id to a non-default is not allowed'';
     ELSE
       RETURN NEW;
     END IF;
   END;
' LANGUAGE plpgsql;

CREATE TRIGGER "foo_id_insert_trigger" BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_id_insert_procedure();

CREATE TRIGGER "foo_id_update_trigger" BEFORE UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE foo_id_update_procedure();