supabase-py: cannot authenticate user

Describe the bug Even after verifying the user is authenticated, I cannot access my data.

I’ve confirmed through print statements that

My user is authenticated as it generates me the access token
The SQL is valid (works when I turn off RLS or if I provide the access_token to the cURL request)

I created two simple RLS policies for allowing

inserts for authenticated users
selects for authenticated users


CREATE POLICY "Enable insert for authenticated users only" ON "public"."todos"
AS PERMISSIVE FOR INSERT
TO authenticated

WITH CHECK (true)

To Reproduce Steps to reproduce the behavior:

Create a table called todos with these columns
Create a RLS for insert

CREATE POLICY "Enable insert for authenticated users only" ON "public"."todos"
AS PERMISSIVE FOR INSERT
TO authenticated

WITH CHECK (true)

Use this code snippet as mentioned in #185

    user = supabase.auth.sign_in_with_password({"email": email, "password": password})
    session = supabase.auth.get_session()  # this refreshes the access token if needed
    # tried this first
    supabase.postgrest.auth(
        # verified that this was real
        token=session.access_token
    )  # this sets the right "authorization" header on HTTP requests to PostgREST
    res = supabase.auth.set_session(session.access_token, session.refresh_token)
    select_data = supabase.table("todos").select("*").execute()
    print(select_data)
    insert_data = supabase.table("todos").insert({"name": "Buy milk"}).execute()
    print(insert_data)

Results: Data is empty

data=[] count=None

RLS error for insert:

    insert_data = supabase.table("todos").insert({"name": "Buy milk"}).execute()
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/postgrest/_sync/request_builder.py", line 70, in execute
    raise APIError(r.json())
postgrest.exceptions.APIError: {'code': '42501', 'details': None, 'hint': None, 'message': 'new row violates row-level security policy for table "todos"'}

Expected behavior As tested with RLS off, I can retrieve the contents of the rows. I can also view do a cURL request with the access_token of the session and retrieve the rows. So my best guess is that although the user itself is successfully authenticated, there’s no way for us to successfully tell the client which executes the queries, that we have given it an authenticated user, even though I have used the workarounds mentioned by the community.

Screenshots If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

OS: Mac 14.1.2
Version: 2.3.0

About this issue

Original URL
State: closed
Created 6 months ago
Comments: 34 (6 by maintainers)

Most upvoted comments

@adriantil I’m running into the same issue you are having, but with inserting a row. Manually setting the access token the way you did not work. I’ve checked that I’m logged in and I have a valid access_token, and if I disable RLS, I’m able to perform an insert. Is there something else that needs to be done for Inserts?

data = supabase.auth.sign_in_with_password({"email": "xxxxx", "password": "xxxxx"})

supabase.postgrest.auth(data.session.access_token)

@app.post("/items/")
async def create_item(item: Item):
    item_data = item.model_dump()
    print(item_data)

data, count = supabase.table('uploads')\
          .insert(item_data)\
          .execute()

brianft on Jan 6, 2024

It should work without the service ‘superuser’ key as long as you first log in with the email and password of a Supabase user that meets the RLS policy as in the OP

E.g: to update the example given by eformx above

from supabase import create_client
API_URL = 'https://blahblah'
API_KEY = 'blahblah'
SUPABASE_ANON_KEY = 'blahblahAnonKey'
supabase = create_client(API_URL, SUPABASE_ANON_KEY)

--- this part here ---
# Log in with your supabase email and password
my_session = supabase.auth.sign_in_with_password({"email": "my-supabase-serviceaccount@email.com", "password": "some-secure-password"})
--- this part here ---

# delete a row
supabase.table("your_table_name").delete().eq("id", 1).execute()

data, count = supabase.table("your_table_name").select("*").execute()
print(data)

You can then fetch the users access and refresh token from my_session as needed. It’s a bit of a pain to manage when you’re implementing this on a backend server somewhere but I prefer this approach over using the service key. Least privilege yada yada.

After the 2.3.3 update this works great for me, but the service key works too.

snags141 on Jan 22, 2024

I’ve released version 2.3.3 which should address this issue, please report back if it has been fixed for you.

silentworks on Jan 11, 2024

I’m having the same issue and came to this through Google. @silentworks I can understand the frustration with not getting a repo, but the issue doesn’t need a full repository worth of code. Reproducing it can be done by creating a table in supabase, turning on RLS for only authenticated users, and then trying an insert or select using the Python supabase import. Token authentication is NOT being handled automatically, that is the issue here:

data = supabase.auth.sign_in_with_password({
     ...
})
data, count = supabase.table("tasks").insert({
     ...
}).execute

Sanitized output:

2024-01-05 12:55:59,371:INFO - HTTP Request: POST <URL>/auth/v1/token?grant_type=password "HTTP/1.1 200 OK"
2024-01-05 12:55:59,513:INFO - HTTP Request: POST <URL>rest/v1/tasks "HTTP/1.1 403 Forbidden"
Traceback (most recent call last):
  File "<PATH>/postgres-test.py", line 311, in <module>
    }).execute()
       ^^^^^^^^^
  File "<venv path>/lib/python3.12/site-packages/postgrest/_sync/request_builder.py", line 70, in execute
    raise APIError(r.json())
postgrest.exceptions.APIError: {'code': '42501', 'details': None, 'hint': None, 'message': 'new row violates row-level security policy for table "tasks"'}

I’m not trying to be an a-hole here, but providing an example flask app and telling people to look through it isn’t particularly helpful. If there is a step I’m missing in between sign-in and interacting with the db I’d super appreciate some direction. I’ve read the docs and think I’m doing things as indicated but could totally be missing something. Happy to help debug as well.

adriantil on Jan 5, 2024