https://supabase.com/ logo
Join Discord
Powered by
How do I grant update access for a specific column...
# sql
t
How do I grant update access for a specific column, for example, I want the user to be able to update their display name, but not some of their other metadata
s
Postgres allows you to protect specific columns. For example:
Copy code
sql
GRANT SELECT(id) ON public.my_table TO authenticated;

GRANT ALL(id) ON public.my_table TO authenticated;

GRANT INSERT(id), UPDATE(id) ON public.my_table TO authenticated;
The first example would grant only read / 
SELECT
permission to the 
authenticated
role, meaning that people in that role (i.e. usually everyone that logs in via the supabase libraries) to the 
id
column of 
my_table
. The second example would grand all permissions (
SELECT
, 
UPDATE
, 
DELETE
, 
INSERT
, etc). The last example would grant only 
INSERT
and 
UPDATE
permissions.
Another option could be to use a trigger. For example:
Copy code
sql
CREATE OR REPLACE FUNCTION public.prevent_user_updating_blocked_fields()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS $BODY$
    BEGIN
        RAISE EXCEPTION 'You can not update these fields';
    END;
$BODY$;

CREATE TRIGGER trigger_prevent_user_updating_blocked_fields
    BEFORE UPDATE OF created_at, email, id
    ON public.my_table
    FOR EACH ROW
    EXECUTE FUNCTION public.prevent_user_updating_blocked_fields();
This should prevent users from updating the 
created_at
, 
email
or 
id
columns of 
my_table
. Generally speaking, using column-level permissions would be the best route to take unless you need finer-grained logic (e.g. checking the 
auth.uid()
of the user trying to modify the table matches a specific value, and then either exiting early or allowing the request to proceed).
In both cases, you should also use RLS policies to ensure users can only access rows they're authorised to access.
t
Thank you for sending this. Any recommendations you have for where I can further learn the syntax of this RLS policy language.
"The last example would grant only INSERT and UPDATE permissions." - So if another RLS policy already gave a user select permissions on the row this would override that?
s
RLS controls access to rows. The examples in the first message control access to columns. Examples: - You don't have an RLS policy, and you don't have a column policy. Any user can perform any action against any column of any row. - You have an RLS policy that only allows users to access rows where the value in the 
id
column matches 
auth.uid()
, but you don't have any column policies. The user can only perform actions against the rows they have access to, but they can modify any column. - You don't have an RLS policy, but you do have a column policy preventing 
UPDATE
of 
id
column. Any user can perform any action against any row, but they can not update the 
ID
column of any row. - You have an RLS policy, and a column policy preventing 
UPDATE
of 
id
column. The user can only modify rows they have access to, and they can perform any action against any column in those rows, except for the 
id
column which they will be unable to update.
PreviousNext
Powered by