cannot authenticate user

[shawnesquivel]

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:

1. Create a table called todos with these columns

2. 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)

3. Use this code snippet as mentioned in RLS not working after sign-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

[shawnesquivel]

hi，u can try skip

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

，use the token from authresponse by auth.sign in

Hi @Atticuszz thanks for the reply, I'm not sure what you meant but I tried and it's not returning any data.

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

 res = supabase.auth.set_session(
        user.session.access_token, user.session.refresh_token
    )
    print(f"access token {user.session.access_token}")

Still does not print the data. I know that there should be data because I verified by using a cURL request with the access token.

access token eyJhb.....

2023-12-17 19:45:08,570:INFO - HTTP Request: GET https://qctnbkfmquxoifgzwmml.supabase.co/rest/v1/todos?select=%2A "HTTP/1.1 200 OK"

data=[] count=None

[silentworks]

I'm a bit confused as to why you are passing around the access_token when it's handled for you automatically. Can you create a minimal reproducible example repository with the trouble code in it. I have a notes project here which is doing what you mentioned here without any issues https://github.com/silentworks/flask-notes.

[NajibPro]

i have the same problem as you, and still didn't find the solution for it

[esousacosta]

Hello, I'm facing the same issue, but I'm not sure if I'm using the correct functions at the correct place.

Here's an example:

from supabase import create_client, Client
from supabase.lib.client_options import ClientOptions

supabaseUrl = 'https://yutoqmiqyxotuwifignh.supabase.co'
# This is the anon key
supabaseKey = 'myAnonKey'
supabase: Client = create_client(supabaseUrl, supabaseKey)

# I was using this var to print only, and it shows all of the connection data, as expected
signInData = supabase.auth.sign_in_with_password({"email": "email@gmail.com", "password": "my-password"})

# Here I get the same row violation error as the author of the post
data, count = supabase.table("books_history").insert({"isbn": "8888", "dates": [], "prices": []}).execute()

At the insert call, I get the following error:
postgrest.exceptions.APIError: {'code': '42501', 'details': None, 'hint': None, 'message': 'new row violates row-level security policy for table "books_history"'}

In addition, I also tried configuring the headers to send the request using the "Authorization: Bearer access_token" information, but I couldn't figure out how to do it properly. If I inject the POST request using the RESTED chrome extension, it works perfectly.

Does anyone have an idea of what I'm missing here? Or is it an issue?

Thank you in advance!

[silentworks]

Folks I have provided an example repo above showing what you are asking here with no issue and no one with the issue seems to be providing an example repo with the issue. It is really hard to fix something you cannot replicate or even give correct help. Please provide an minimal example repository with this issue present.

[adriantil]

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]

For additional information:

Using postman I can hit the same endpoints: signing in with auth and then doing a select through a GET rather than a POST.

With RLS on that table set to any "authenticated" user I am not able to query the table and receive results using the python package. I AM able to login with postman through the auth endpoint, get the access token, place that in the headers of a new call to the same table (with "bearer <access token" format") and receive the expected information.

So actions using python after a sign in aren't working, but a curl/requests/postman API request does. Did I miss a step between logging in with email and password and trying to execute a query/insert?

[adriantil]

Okay, here we go:

Does not work, returns no data.

supabase: Client = create_client(POSTGRES_URL, POSTGRES_PUBLIC_KEY)
data = supabase.auth.sign_in_with_password(
  {....}
)
# supabase.postgrest.auth(data.session.access_token)

data, count = supabase.table("tasks").select("*").execute()
print(data) # NO DATA, EMPTY LIST
supabase.auth.sign_out()

Manually setting the access token works, unknown about refresh token but that should probably be checked.

supabase: Client = create_client(POSTGRES_URL, POSTGRES_PUBLIC_KEY)
data = supabase.auth.sign_in_with_password(
  {....}
)
supabase.postgrest.auth(data.session.access_token)

data, count = supabase.table("tasks").select("*").execute()
print(data) #WORKS, FINDS EXPECTED RESULTS
supabase.auth.sign_out()

[brianft]

@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()

[AtticusZeller]

+1，https://github.com/Atticuszz/supabase-py-async，
failed as set

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

WITH CHECK (true)

os：win11
version 2.3.1
in tests.test_postgrest_client

import pytest
from postgrest import APIResponse

from tests.base_client import TestBaseClient


class TestPostgrestClient(TestBaseClient):
    @pytest.mark.asyncio
    async def test_insert(self):
        rsp = await self.superuser_sign_in()
        self.client.postgrest.auth(rsp.session.access_token)
        assert rsp.session.user is not None, "user is None"
        text = self.fake_data.text()
        api_rsp: APIResponse = (
            await self.client.table("test_table")
            .insert([{"user_id": rsp.session.user.id, "text": text}])
            .execute()
        )
        assert api_rsp.data[0]["user_id"] == rsp.session.user.id, "user_id is not equal"
        assert api_rsp.data[0]["text"] == text, "text is not equal"

        # FIXME: not work for rls of insert bu authed user, {'code': '42501', 'details': None, 'hint': None, 'message': 'new row violates row-level security policy for table "test_table"'}

i do sure the user is authenticated，but it occured
so i disable the rls for normal usage😴

[AtticusZeller]

I'm a bit confused as to why you are passing around the access_token when it's handled for you automatically. Can you create a minimal reproducible example repository with the trouble code in it. I have a notes project here which is doing what you mentioned here without any issues https://github.com/silentworks/flask-notes.

thanks for sharing! In the flask example, still have confusions
on how it can recognize who send the request?