Hi everyone, I have a bit of a complex problem that I don't know what's the best way to solve: I have the following

Message

table:

CREATE TABLE IF NOT EXISTS public.message
(
    id uuid NOT NULL,
    sender_user_id uuid NOT NULL,
    recipient_user_id uuid NOT NULL,
    conversation_id uuid NOT NULL,
    content text COLLATE pg_catalog."default" NOT NULL,
    read boolean DEFAULT false,
    message_warning boolean DEFAULT false,
    CONSTRAINT message_pkey PRIMARY KEY (id),
    CONSTRAINT message_conversation_id_fkey FOREIGN KEY (conversation_id)
        REFERENCES public.conversation (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT message_sender_user_id_fkey FOREIGN KEY (sender_user_id)
        REFERENCES public."user" (id) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
)

The table is pretty simple but I have some the need of some

"custom" Column Level Security

. For example, I would like to let the user, through a

RLS Policy

, to only UPDATE the

content

and the

read

column. As far as I know this is not possible through a

RLS Policy

. Is there any other way to do this? or what's the best way to do this? maybe without using a

db function

? Thanks!

It is not possible thru RLS. Postgres has a concept of column security, but it is on postgres users/roles and might or might not work for you. As you noted you could use RPC function to do the writes and have RLS block Updates. Another way is to create a trigger on before update. Then you use the old and new row info to make sure the columns you want to protect are always set to the old value before returning new.

Inside of the trigger you could even setup role checking if you only want certain users to be able to edit all fields

I start saying thank you guys for the real quick response! Postgres' column security would be nice but I don't know where to start and even which users/roles to touch. I guess then setting up triggers will be the best way to handle this.