RLS for Columns?

I'm currently working on a project involving the quizzes and likes tables in our database, and I could use your expertise to address a specific challenge.

The Goal:
I aim to implement a function trigger that updates the quizzes table's likes column whenever a row is inserted or deleted in the likes table. However, there's a hurdle due to the Row-Level Security (RLS) policy applied to the quizzes table. This policy restricts the function from updating the likes or dislikes columns unless the user triggering the update is the owner of the quiz.

Challenge:
Any solutions to enable this functionality, allowing any user to update the likes count for quizzes regardless of the user's ownership. Any insights into potential workarounds or adjustments would be greatly appreciated.

Relevant Information:
The quizzes table is as follows:

create table
  public.quizzes (
    id bigint generated by default as identity,
    quiz_name text null,
    user_id uuid null,
    created_at timestamp with time zone not null default now(),
    quiz_description text null,
    likes bigint null default '0'::bigint,
    dislikes bigint null default '0'::bigint,
    constraint Quizzes_pkey primary key (id),
    constraint quizzes_user_id_fkey foreign key . . . 
  ) tablespace pg_default;

create table
  public.quizzes (
    id bigint generated by default as identity,
    quiz_name text null,
    user_id uuid null,
    created_at timestamp with time zone not null default now(),
    quiz_description text null,
    likes bigint null default '0'::bigint,
    dislikes bigint null default '0'::bigint,
    constraint Quizzes_pkey primary key (id),
    constraint quizzes_user_id_fkey foreign key . . . 
  ) tablespace pg_default;

RLS for quizzes table:

Update for users based on user_uid

AS PERMISSIVE FOR UPDATE
TO public
USING ((auth.uid() = user_id))
WITH CHECK ((auth.uid() = user_id))

Update for users based on user_uid

AS PERMISSIVE FOR UPDATE
TO public
USING ((auth.uid() = user_id))
WITH CHECK ((auth.uid() = user_id))

The likes Table is as follows:

create table
  public.likes (
    id bigint generated by default as identity,
    quiz_id bigint null,
    is_liked boolean null,
    user_id uuid null,
    created_at timestamp with time zone not null default now(),
    constraint UserVotes_pkey primary key (id),
    constraint unique_like_constraint unique (quiz_id, user_id),
    constraint likes_quiz_id_fkey foreign key  . . . 
    constraint likes_user_id_fkey foreign key . . . 
  ) tablespace pg_default;

create table
  public.likes (
    id bigint generated by default as identity,
    quiz_id bigint null,
    is_liked boolean null,
    user_id uuid null,
    created_at timestamp with time zone not null default now(),
    constraint UserVotes_pkey primary key (id),
    constraint unique_like_constraint unique (quiz_id, user_id),
    constraint likes_quiz_id_fkey foreign key  . . . 
    constraint likes_user_id_fkey foreign key . . . 
  ) tablespace pg_default;

RLS for Columns?

create table
  public.quizzes (
    id bigint generated by default as identity,
    quiz_name text null,
    user_id uuid null,
    created_at timestamp with time zone not null default now(),
    quiz_description text null,
    likes bigint null default '0'::bigint,
    dislikes bigint null default '0'::bigint,
    constraint Quizzes_pkey primary key (id),
    constraint quizzes_user_id_fkey foreign key . . . 
  ) tablespace pg_default;

create table
  public.quizzes (
    id bigint generated by default as identity,
    quiz_name text null,
    user_id uuid null,
    created_at timestamp with time zone not null default now(),
    quiz_description text null,
    likes bigint null default '0'::bigint,
    dislikes bigint null default '0'::bigint,
    constraint Quizzes_pkey primary key (id),
    constraint quizzes_user_id_fkey foreign key . . . 
  ) tablespace pg_default;

RLS for quizzes table:

Update for users based on user_uid

AS PERMISSIVE FOR UPDATE
TO public
USING ((auth.uid() = user_id))
WITH CHECK ((auth.uid() = user_id))

Update for users based on user_uid

AS PERMISSIVE FOR UPDATE
TO public
USING ((auth.uid() = user_id))
WITH CHECK ((auth.uid() = user_id))

The likes Table is as follows:

create table
  public.likes (
    id bigint generated by default as identity,
    quiz_id bigint null,
    is_liked boolean null,
    user_id uuid null,
    created_at timestamp with time zone not null default now(),
    constraint UserVotes_pkey primary key (id),
    constraint unique_like_constraint unique (quiz_id, user_id),
    constraint likes_quiz_id_fkey foreign key  . . . 
    constraint likes_user_id_fkey foreign key . . . 
  ) tablespace pg_default;

create table
  public.likes (
    id bigint generated by default as identity,
    quiz_id bigint null,
    is_liked boolean null,
    user_id uuid null,
    created_at timestamp with time zone not null default now(),
    constraint UserVotes_pkey primary key (id),
    constraint unique_like_constraint unique (quiz_id, user_id),
    constraint likes_quiz_id_fkey foreign key  . . . 
    constraint likes_user_id_fkey foreign key . . . 
  ) tablespace pg_default;

RLS for Columns?

RLS for Columns?

Similar Threads

Similar Threads

Similar Threads