Prevent Explicit Insert into Serial Columns in PostgreSQL

database-designpermissionspostgresqlsequencetrigger

I want to prevent explicit inserts into serial columns. I have come up to the following trigger:

drop table test_table;

create table test_table(
id bigserial primary key,
foobar text
);

create or replace function serial_id_check() returns trigger as
$$
begin
    if new.id != currval(TG_TABLE_NAME||'_id_seq') then
        raise exception 'Explicit insert into serial id, currval = %, tried to insert = %', currval(TG_TABLE_NAME||'_id_seq'), new.id;
    end if;
    return new;
end;
$$ language plpgsql;

create trigger test_table_serial_id_check
before insert on test_table
for each row
execute procedure serial_id_check();

Maybe there is a better approach? Maybe this approach is broken and this cannot be achieved at all?

P.S. I also think about not granting rights for the insert and update, but only for pgplsql procedure for inserting/updating – but this approach is not possible for me right now.

Best Answer

In Postgres 10 or later, consider an IDENTITY column instead. Like:

id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

See:


Original answer for older versions:

Updateable View

In Postgres 9.4, views are automatically updatable per column. I.e., as long as basic conditions are met, columns are automatically updatable for plain references to underlying columns. Expressions are not. We can exploit this feature:

CREATE TABLE test_table(
  id serial PRIMARY KEY
, foobar text
);

CREATE VIEW test_view AS
SELECT id * 1 AS id  -- id unchanged but not updatable!
     , foobar
FROM   test_table;

By multiplying id with 1, the value is unchanged but the column is not automatically updatable any more.

  • REVOKE INSERT / UPDATE privileges on test_table from your users.
  • Make another role holding those privileges own the view.
  • GRANT INSERT / UPDATE on test_view to your users.

Now they can do everything, but they cannot manually set or alter the value in id. It's your choice how to handle DELETE.

This simple and quick solution works out of the box in Postgres 9.4. Automatically updatable views were introduced with Postgres 9.3, but all columns need to be updatable in that version for the feature to work.

In Postgres 9.3 or older, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.

While writing rules / triggers manually, you don't need the id * 1 trick. You might want to use this even in Postgres 9.4+ to fine-tune functionality. Example:

CREATE VIEW test_view1 AS TABLE test_table;

CREATE OR REPLACE RULE ins_up AS
 ON INSERT TO test_view1 DO INSTEAD
INSERT INTO test_table (foobar) VALUES (NEW.foobar);

Now, INSERT on the view is possible, but not yet UPDATE or DELETE. Write more rules if you want that.

SQL Fiddle (Postgres 9.6)

Important difference: while the automatically updatable view rejects attempts to INSERT / UPDATE values in id with an exception, the demonstrated RULE simply ignores values for id and proceeds without exception.

Simple trigger

Alternatively, you could simply overwrite the serial column with the next value from its sequence unconditionally:

CREATE OR REPLACE FUNCTION force_serial_id()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   NEW.id := nextval(pg_get_serial_sequence(quote_ident(TG_TABLE_NAME), 'id'));
   RETURN NEW;
END
$func$;

That's simpler and cheaper and less error-prone than trying to be smart about it. quote_ident() to safely escape otherwise illegal names (also defends against SQL injection).

Like @dezso commented, this burns two numbers per row in normal operation with a serial column because a default is fetched before the trigger function kicks in. Typically, gaps in a sequence should not be a problem (to be expected anyway), but you can avoid the side effect by removing the DEFAULT from the column. Then you rely on the trigger exclusively.

You could fine-tune the UPDATE case with a separate trigger and a condition on the trigger itself WHEN (OLD.id <> NEW.id). Syntax example:

Note the use of pg_get_serial_sequence(), which won't break like your original for non-basic identifiers. Think of "MyTable" or a non-default sequence name. Still assuming the column name id which I personally never use since it's not descriptive.

Related Question