How to fix PGRST117: HTTP verb not allowed in Supabase

-- Check if RLS is enabled on the table
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND tablename = 'your_table_name';

-- View existing RLS policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual, with_check
FROM pg_policies
WHERE schemaname = 'public' AND tablename = 'your_table_name';

-- Example: Allow INSERT for authenticated users
CREATE POLICY "Users can insert their own data" ON your_table_name
FOR INSERT WITH CHECK (auth.uid() = user_id);

-- Example: Allow UPDATE for authenticated users
CREATE POLICY "Users can update their own data" ON your_table_name
FOR UPDATE USING (auth.uid() = user_id);

-- Example: Allow DELETE for authenticated users
CREATE POLICY "Users can delete their own data" ON your_table_name
FOR DELETE USING (auth.uid() = user_id);

-- Check privileges for the anon role (public access)
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee = 'anon' AND table_schema = 'public';

-- Check privileges for the authenticated role
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee = 'authenticated' AND table_schema = 'public';

-- Check privileges for the service_role (admin)
SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE grantee = 'service_role' AND table_schema = 'public';

-- Grant INSERT, UPDATE, DELETE permissions to authenticated role
GRANT INSERT, UPDATE, DELETE ON your_table_name TO authenticated;

-- Grant all permissions to service_role (use cautiously)
GRANT ALL PRIVILEGES ON your_table_name TO service_role;

-- For anon role (public access, use with caution)
GRANT INSERT, UPDATE, DELETE ON your_table_name TO anon;

// Correct: Using PATCH for updates
const { error } = await supabase
  .from('profiles')
  .update({ username: 'new_username' })
  .eq('id', user.id);

// Incorrect: Using POST for updates (would cause PGRST117)
const { error } = await supabase
  .from('profiles')
  .post({ username: 'new_username' }) // post() doesn't exist
  .eq('id', user.id);

// Client-side (browser) - use anon key
import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  'https://your-project.supabase.co',
  'your-anon-key' // Public key
)

// Server-side - can use service_role key (keep it secret!)
const supabaseAdmin = createClient(
  'https://your-project.supabase.co',
  'your-service-role-key', // Keep this server-side only!
  {
    auth: {
      autoRefreshToken: false,
      persistSession: false
    }
  }
)

// First, sign in the user
const { data: { user }, error: authError } = await supabase.auth.signInWithPassword({
  email: '[email protected]',
  password: 'password'
});

// Then make authenticated requests
if (user) {
  const { data, error } = await supabase
    .from('protected_table')
    .insert({ user_id: user.id, data: 'some data' });
}

// Test 1: Unauthenticated request (anon key)
async function testUnauthenticated() {
  const { data, error } = await supabase
    .from('test_table')
    .insert({ test: 'value' });

  console.log('Unauthenticated result:', { data, error });
}

// Test 2: Authenticated request (user JWT)
async function testAuthenticated() {
  // Sign in first
  const { data: { session }, error: signInError } = await supabase.auth.signInWithPassword({
    email: '[email protected]',
    password: 'testpassword'
  });

  if (session) {
    const { data, error } = await supabase
      .from('test_table')
      .insert({ user_id: session.user.id, test: 'value' });

    console.log('Authenticated result:', { data, error });
  }
}

// Test 3: Service role request (admin, bypasses RLS)
async function testServiceRole() {
  const supabaseAdmin = createClient(
    process.env.SUPABASE_URL,
    process.env.SUPABASE_SERVICE_ROLE_KEY
  );

  const { data, error } = await supabaseAdmin
    .from('test_table')
    .insert({ test: 'value' });

  console.log('Service role result:', { data, error });
}

-- Check if it's a view instead of a table
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = 'your_table_name';

-- Check if table has triggers or rules preventing modifications
SELECT tgname, tgtype, tgrelid::regclass
FROM pg_trigger
WHERE tgrelid = 'your_table_name'::regclass;

-- Check for INSTEAD OF triggers on views
SELECT viewname, definition
FROM pg_views
WHERE schemaname = 'public'
AND viewname = 'your_table_name';