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. Postgres Column Level Security
in view there is also an
security_barrier but I don't know how to use.
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