How do I prevent the user from inserting or updating values for certain columns?

Using SQLAlchemy as the ORM for my PostgreSQL database, I would like to add a constraint (or something similar) in my model definition that prevents a user from:

1) Inserting a value into a column they're not supposed to insert into (e.g. my auto-incrementing primary key or a date_modified that gets automatically set onupdate.)

2) Updating values in the same columns.

Basically - I would like inserts and/or updates to fail if they are attempting to touch specific columns.


For clarification: I would like to declare this through my model (which inherit's from SQLAlchemy's Base).

2 answers

  • answered 2018-01-11 20:55 Neil McGuigan

    PostgreSQL supports declarative column security (as well as row security)

    Use this template:

    GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
        [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
        ON [ TABLE ] table_name [, ...]
        TO role_specification [, ...] [ WITH GRANT OPTION ]


    GRANT INSERT (colA, colB) ON TABLE foo TO michi;

  • answered 2018-01-11 20:55 LoganTippy

    The easiest way I could think of doing this would be to use:

    final int (name) = x;

    This way the value can't be changed while running.