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_barrierbut 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_byshould 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
Demir last edited by
except the table owner, other's can only
users_templateview. 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;
-- 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();