I'm just getting my feet wet with PostgreSQL RLS and I'm pretty confused. I have the following tables:

users
| id|role          |email            |
|--:|:-------------|:----------------|
|  1|authenticated | [email protected]   |
|  2|authenticated | [email protected] |
|  3|authenticated | [email protected]  |
|  4|authenticated | [email protected]  |

profiles
| id|name  | company_id|
|--:|:-----|----------:|
|  1|Bob   |          1|
|  2|Alice |          1|
|  3|Jane  |          2|
|  4|Mark  |         NA|

companies
| id|name |
|--:|:----|
|  1|ABC  |
|  2|DEF  |

Note that I am using Supabase, which sets up the users table and roles automatically.

I am trying to set an update policy for the companies table. Supabase has a starter template which looks like this

CREATE POLICY "companies"
ON public.companies
FOR UPDATE USING (
  auth.email() = email
) WITH CHECK (
  auth.email() = email
);

Can someone ELI5? What is auth in this context? How can I adapt this to fit my schema?

CodePudding user response：

There are a few things to unpack here.

What's auth in this context?

Auth is the schema where Supabase deploys the auth-related tables and helper functions.

What does the function auth.email() do?

If you use PSQL, then you can check the definition of these helper functions:

\df auth.email

select     
coalesce(     
nullif(current_setting('request.jwt.claim.email', true), ''),
(nullif(current_setting('request.jwt.claims', true), '')::jsonb ->> 'email')
)::text 

So, this is a function to check the user's email is the same as the authenticated user's. This is to ensure that the user is trying to update their own row. Please note that this function is deprecated. The current recommendation is to use auth.jwt() ->> 'email' instead.

These are the helper functions listed in the auth schema:

• auth.email()
• auth.jwt()
• auth.role()
• auth.uid()

Exploring further:

The documentation also hints at a more complex scenario where only users with a defined email ending can update something:

create policy "Only Blizzard staff can update leaderboard"
  on my_scores
  for update using (
    right(auth.jwt() ->> 'email', 13) = '@blizzard.com'
  );

Suppose you want to cover more broad and complex cases when creating Row Level Security policies. In that case, I do recommend using custom claims as it provides more flexibility to create more scenarios and different roles for your platform.

Using custom claims to create a company update policy:

Install custom claims by running the SQL script in the SQL Editor:

Set the claim for the user:

select set_claim('00000-0000-45c1-8dfb-6eeb7cf0b92e', 'company', '1');

Then, you can use it on RLS policies:

CREATE POLICY "User can update team details if they belong to the company."
ON public.companies
FOR UPDATE USING (
  get_my_claim('company') = id
);

Another way to achieve this is using joins in your RLS, but that will be less performant as you are using a join query to check every update.