0
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. https://stackoverflow.com/questions/24604447/create-hidden-column-in-postgresql#:~: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

db fiddle

3
  • Sure, that is easily possible. What problems do you encounter? Apr 27 at 6:21
  • I added the db fiddle link, now the trigger works on updated_by,updated_on, but we can still change the value of created_on ,cerated_by. then how to make created_on ,cerated_by. the value first time insert is the default insert value, after that these two columns value never change. @LaurenzAlbe
    – jian
    Apr 27 at 6:37
  • The question should be understandable even without the dbfiddle (which is a good addition). Apr 27 at 6:57

1 Answer 1

1

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();

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.