qwen/qwen3-coder-plus · d1_rls_owner · rep 1

env | grep SUPABASE_

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>

psql "$SUPABASE_DB_URL" -c "\dt public.notes"

          List of tables
 Schema | Name  | Type  |  Owner   
--------+-------+-------+----------
 public | notes | table | postgres
(1 row)

psql "$SUPABASE_DB_URL" -c "\d public.notes"

                            Table "public.notes"
 Column  |  Type  | Collation | Nullable |             Default              
---------+--------+-----------+----------+----------------------------------
 id      | bigint |           | not null | generated by default as identity
 user_id | uuid   |           | not null | 
 title   | text   |           | not null | 
 body    | text   |           | not null | ''::text
Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "notes_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE

cat > setup_rls.sql << 'EOF'
-- Enable Row Level Security on the notes table
ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;

-- Create policy to ensure users can only operate on their own notes
CREATE POLICY "Users can manage own notes" ON public.notes
FOR ALL TO authenticated
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Create policy to block all access from anon users
CREATE POLICY "Block anon access" ON public.notes
FOR ALL TO anon
USING (FALSE);
EOF

PGPASSWORD="$SUPABASE_DB_PASSWORD" psql --username="postgres.$SUPABASE_PROJECT_REF" --host=$(echo $SUPABASE_DB_URL | sed -n 's/.*\/\/\([^:]*\):.*/\1/p') --port=$(echo $SUPABASE_DB_URL | sed -n 's/.*:\([0-9]*\)\/.*/\1/p') --dbname=$(echo $SUPABASE_DB_URL | sed -n 's/.*\/\([^\/?]*\).*/\1/p') -f setup_rls.sql

psql: error: could not translate host name "postgres.<ref>" to address: Name or service not known

psql "$SUPABASE_DB_URL" -c "ALTER TABLE public.notes ENABLE ROW LEVEL SECURITY;"

ALTER TABLE

psql "$SUPABASE_DB_URL" -c "CREATE POLICY \"Users can manage own notes\" ON public.notes FOR ALL TO authenticated USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);"

CREATE POLICY

psql "$SUPABASE_DB_URL" -c "CREATE POLICY \"Block anon access\" ON public.notes FOR ALL TO anon USING (FALSE);"

CREATE POLICY

psql "$SUPABASE_DB_URL" -c "\dp public.notes"

                                              Access privileges
 Schema | Name  | Type  |        Access privileges        | Column privileges |           Policies            
--------+-------+-------+---------------------------------+-------------------+-------------------------------
 public | notes | table | postgres=arwdDxtm/postgres     +|                   | Users can manage own notes:  +
        |       |       | anon=arwdDxtm/postgres         +|                   |   (u): (auth.uid() = user_id)+
        |       |       | authenticated=arwdDxtm/postgres+|                   |   (c): (auth.uid() = user_id)+
        |       |       | service_role=arwdDxtm/postgres  |                   |   to: authenticated          +
        |       |       |                                 |                   | Block anon access:           +
        |       |       |                                 |                   |   (u): false                 +
        |       |       |                                 |                   |   to: anon
(1 row)

psql "$SUPABASE_DB_URL" -c "SELECT policyname, permissive, roles[1] as role, cmd as command, qual as \"using\", with_check FROM pg_policies WHERE tablename = 'notes';"

         policyname         | permissive |     role      | command |         using          |       with_check       
----------------------------+------------+---------------+---------+------------------------+------------------------
 Users can manage own notes | PERMISSIVE | authenticated | ALL     | (auth.uid() = user_id) | (auth.uid() = user_id)
 Block anon access          | PERMISSIVE | anon          | ALL     | false                  | 
(2 rows)