Row Level Security (RLS) based on count

[titogarrido]

Hi Guys!

I am trying to create a row level security based on the following condition:
If the user has 3 visible projects, then it can insert a vote in the vote table:

Projects:
id -> int8
user_id -> auth.uuid
visible -> bool

Votes:
id -> int8
user_id -> auth.uuid
project_id -> project.id

This is my RLS for INSERT:

(
  EXISTS (
    SELECT
      (count(*) >= 3) AS can_vote
    FROM
      projects
    WHERE
      (
        (projects.user_id = uid())
        AND (projects.visible = true)
      )
  )
)

Even with 3 visible projects I am receiving:

-d '{ "user_id": "b823bb81-45f4-4cd8-ba98-7daf00a28ae7", "project_id": "1" }'
{"hint":null,"message":"new row violates row-level security policy for table \"votes\"","code":"42501","details":null}%

What am I doing wrong?

[burggraf]

Have you tried something like:

((SELECT
      count(*) 
    FROM
      projects
    WHERE
        projects.user_id = uid()
        AND projects.visible = true) >= 3)
      

[titogarrido]

It works! Thanks @burggraf !

[burggraf]

Happy to help! Simpler is always better for me :)