can only be updated by trigger in postgresql



  • create table users(
        userid bigint primary key,
        email text not null,
        created_by text not null default  current_user,
        created_on timestamptz not null default  now(),
        updated_on timestamptz,
        updated_by text
    );
    

    I can make trigger to update the last 4 columns. I can also create view.

    create view users_template AS select userid, email from users;
    GRANT ALL ON users_template TO public;
    

    Since column level security is a bad idea. https://dba.stackexchange.com/questions/239577/postgres-column-level-security
    in view there is also an security_barrier but I don't know how to use. https://stackoverflow.com/questions/24604447/create-hidden-column-in-postgresql#:%7E:text=No%2C%20there%20is%20no%20supported,defined%20columns%20from%20the%20*%20wildcard .

    So is possible to grant select only on users. grant all to users_template. The end goal is create_by, created_on, updated_on, updated_by should be updated by trigger only.

    I feel like is possible:

    Note that the user performing the insert, update or delete on the view must have the corresponding insert, update or delete privilege on the view. In addition the view's owner must have the relevant privileges on the underlying base relations, but the user performing the update does not need any permissions on the underlying base relations (see Section 41.5). https://www.postgresql.org/docs/current/sql-createview.html

    https://dbfiddle.uk/?rdbms=postgres_14&fiddle=708e1b8072875ea241a5d49f1f367e6d



  • insert issue:
    except the table owner, other's can only insert via users_template view. So except userid, email column, all other columns will be trigger generated.

    create view users_template AS select userid, email from users;
    GRANT ALL ON users_template TO public;
    grant select, update on users to public;
    

    update issue:
    -- some columns(updated_on, updated_by) value will be changed by trigger.
    -- some columns(created_by, created_on) value will remain the same.

    CREATE OR REPLACE FUNCTION update_users_column()
    RETURNS TRIGGER AS $$
    BEGIN
       NEW.updated_on = now();
       NEW.updated_by = current_user;
       NEW.created_by = OLD.created_by;
       NEW.created_on = OLD.created_on;
       RETURN NEW;
    END
    $$ language 'plpgsql';
    

    CREATE OR REPLACE TRIGGER update_users_column BEFORE UPDATE
    ON users FOR EACH ROW EXECUTE PROCEDURE update_users_column();


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2