Bug Report: RLS WITH CHECK Clause Fails for Soft Delete from supabase-js Client

[brunocek]

Describe the bug

Bug Report: RLS WITH CHECK Clause Fails for Soft Delete from supabase-js Client

Summary

UPDATE queries with RLS policies fail when setting deleted = true (soft delete) from the supabase-js client, but succeed when executed directly in SQL Editor with the same user context. Regular UPDATE operations (updating other fields) work correctly.

Environment

• Supabase JS Version: @supabase/supabase-js (latest)
• Database: PostgreSQL via Supabase
• Client: Browser (React application)
• Authentication: JWT-based (anon key with authenticated user session)

Expected Behavior

When a user has the units.delete permission and attempts to soft delete a record by setting deleted = true, the UPDATE should succeed because the RLS policy's WITH CHECK clause explicitly allows it:

WITH CHECK (
  user_has_access_to_organization(organization_id) AND
  (((deleted = false) AND has_permission('units.update')) OR
   ((deleted = true) AND has_permission('units.delete')))
)

Actual Behavior

The UPDATE fails with error code 42501:

new row violates row-level security policy for table "data_units"

Steps to Reproduce

1. Database Setup

Create the table:

CREATE TABLE data_units (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  organization_id uuid NOT NULL,
  deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

ALTER TABLE data_units ENABLE ROW LEVEL SECURITY;

Create helper functions:

-- Check if user has access to organization
CREATE OR REPLACE FUNCTION user_has_access_to_organization(org_id uuid)
RETURNS boolean
LANGUAGE plpgsql
STABLE SECURITY DEFINER
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM user_organizations
    WHERE user_id = auth.uid()
    AND organization_id = org_id
  );
END;
$$;

-- Check if user has permission (simplified example)
CREATE OR REPLACE FUNCTION has_permission(permission_code text)
RETURNS boolean
LANGUAGE plpgsql
STABLE SECURITY DEFINER
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM role_permissions rp
    JOIN permissions p ON rp.permission_id = p.id
    JOIN users u ON u.role_id = rp.role_id
    WHERE u.id = auth.uid()
      AND p.permission_code = permission_code
      AND rp.deleted = false
  );
END;
$$;

Create RLS policies:

-- SELECT policy
CREATE POLICY "Users can view data_units in their organization"
ON data_units FOR SELECT
USING (
  deleted = false AND
  user_has_access_to_organization(organization_id) AND
  has_permission('units.view')
);

-- INSERT policy
CREATE POLICY "Users can create data_units in their organization"
ON data_units FOR INSERT
WITH CHECK (
  deleted = false AND
  user_has_access_to_organization(organization_id) AND
  has_permission('units.create')
);

-- UPDATE policy (handles both regular updates AND soft deletes)
CREATE POLICY "Users can update data_units in their organization"
ON data_units FOR UPDATE
USING (
  deleted = false AND
  user_has_access_to_organization(organization_id) AND
  (has_permission('units.update') OR has_permission('units.delete'))
)
WITH CHECK (
  user_has_access_to_organization(organization_id) AND
  (((deleted = false) AND has_permission('units.update')) OR
   ((deleted = true) AND has_permission('units.delete')))
);

-- Block hard deletes
CREATE POLICY "Block hard deletes on data_units"
ON data_units FOR DELETE
USING (false);

2. Test Data Setup

-- Assume user with ID '06ffcce0-4b6a-4b59-a180-469db3d35902' exists
-- Assume organization with ID 'ce63ed5e-88d9-495b-9247-0a882182cef2' exists
-- User has both 'units.update' and 'units.delete' permissions

INSERT INTO data_units (id, name, organization_id)
VALUES (
  'cbb8bb04-d1a0-4e49-a857-c124f5714765',
  'Test Unit',
  'ce63ed5e-88d9-495b-9247-0a882182cef2'
);

3. Test from SQL Editor (WORKS ✅)

-- Set user context to match the authenticated user
BEGIN;
SET LOCAL request.jwt.claim.sub = '06ffcce0-4b6a-4b59-a180-469db3d35902';
SET LOCAL request.jwt.claim.role = 'authenticated';

-- Verify permissions
SELECT
  has_permission('units.update') AS has_update,
  has_permission('units.delete') AS has_delete,
  user_has_access_to_organization('ce63ed5e-88d9-495b-9247-0a882182cef2'::uuid) AS has_org_access;
-- Result: has_update=true, has_delete=true, has_org_access=true

-- Test the WITH CHECK clause logic directly
SELECT (
  user_has_access_to_organization('ce63ed5e-88d9-495b-9247-0a882182cef2'::uuid) AND
  (((false = false) AND has_permission('units.update')) OR
   ((true = true) AND has_permission('units.delete')))
) AS with_check_result;
-- Result: with_check_result=true

-- Attempt soft delete
UPDATE data_units
SET deleted = true, updated_at = NOW()
WHERE id = 'cbb8bb04-d1a0-4e49-a857-c124f5714765';
-- Result: SUCCESS ✅

COMMIT;

4. Test from supabase-js Client (FAILS ❌)

JavaScript code:

import { createClient } from '@supabase/supabase-js';

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

// User is authenticated with session
const { data: { session } } = await supabase.auth.getSession();
console.log('Session user ID:', session.user.id);
// Output: 06ffcce0-4b6a-4b59-a180-469db3d35902

// Test 1: Regular UPDATE (updating name) - WORKS ✅
const { data: updateResult, error: updateError } = await supabase
  .from('data_units')
  .update({ name: 'Updated Name' })
  .eq('id', 'cbb8bb04-d1a0-4e49-a857-c124f5714765')
  .select()
  .single();

console.log('Regular update:', { data: updateResult, error: updateError });
// Result: SUCCESS ✅

// Test 2: Soft delete (setting deleted = true) - FAILS ❌
const { data: deleteResult, error: deleteError } = await supabase
  .from('data_units')
  .update({ deleted: true })
  .eq('id', 'cbb8bb04-d1a0-4e49-a857-c124f5714765')
  .select()
  .single();

console.log('Soft delete:', { data: deleteResult, error: deleteError });
// Result: FAILS ❌
// Error: {
//   code: '42501',
//   details: null,
//   hint: null,
//   message: 'new row violates row-level security policy for table "data_units"'
// }

Diagnostic Information

Session State (from console logs)

{
  hasSession: true,
  userId: '06ffcce0-4b6a-4b59-a180-469db3d35902',
  expiresAt: 1765519682,
  isExpired: false
}

Payload Being Sent

{
  deleted: true,
  updated_at: '2025-12-12T05:45:58.492Z'
}

RLS Policy Currently in Effect

policyname: "Users can update data_units in their organization"
cmd: UPDATE
using_clause: ((deleted = false) AND user_has_access_to_organization(organization_id) AND (has_permission('units.update'::text) OR has_permission('units.delete'::text)))
with_check_clause: (user_has_access_to_organization(organization_id) AND (((deleted = false) AND has_permission('units.update'::text)) OR ((deleted = true) AND has_permission('units.delete'::text))))

Key Observations

1. Same user, different results: The exact same user ID (06ffcce0-4b6a-4b59-a180-469db3d35902) succeeds in SQL Editor but fails from JS client
2. Regular UPDATEs work: Setting other fields (like name) works fine from the JS client
3. Only soft delete fails: Only when setting deleted = true does the RLS policy fail
4. All permission checks pass in SQL: has_permission('units.delete') returns true when tested directly
5. Session is valid: The JWT token is valid and not expired

Hypothesis

There appears to be a discrepancy in how the has_permission() function (or the WITH CHECK clause evaluation) behaves when called:

• From SQL Editor with SET LOCAL request.jwt.claim.sub
• From supabase-js client with a valid JWT session

The USING clause passes (allowing the row to be selected for update), but the WITH CHECK clause fails specifically when deleted = true, suggesting the has_permission('units.delete') call returns false from the JS client context despite returning true in SQL Editor.

Workaround

Temporarily removing the permission check from WITH CHECK allows soft deletes to work:

DROP POLICY "Users can update data_units in their organization" ON data_units;

CREATE POLICY "Users can update data_units in their organization"
ON data_units FOR UPDATE
USING (deleted = false AND user_has_access_to_organization(organization_id))
WITH CHECK (user_has_access_to_organization(organization_id));

This confirms the issue is specifically with the has_permission() function call within the WITH CHECK clause when invoked from the supabase-js client.

Additional Context

• This issue is reproducible across multiple tables with identical RLS policy patterns
• The issue occurs in both development and production environments
• No errors appear in browser console except the RLS violation
• Database logs show the same error code (42501)

Request

Please investigate why RLS policy functions (like has_permission()) return different results when:

1. Called from SQL Editor with user context set via session variables
2. Called from supabase-js client with a valid authenticated JWT session

Is there a difference in how the JWT claims are passed to PostgreSQL functions in these two scenarios?

Library affected

supabase-js

Reproduction

No response

Steps to reproduce

1. Database Setup

Create the table:

CREATE TABLE data_units (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  organization_id uuid NOT NULL,
  deleted boolean DEFAULT false,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

ALTER TABLE data_units ENABLE ROW LEVEL SECURITY;

Create helper functions:

-- Check if user has access to organization
CREATE OR REPLACE FUNCTION user_has_access_to_organization(org_id uuid)
RETURNS boolean
LANGUAGE plpgsql
STABLE SECURITY DEFINER
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM user_organizations
    WHERE user_id = auth.uid()
    AND organization_id = org_id
  );
END;
$$;

-- Check if user has permission (simplified example)
CREATE OR REPLACE FUNCTION has_permission(permission_code text)
RETURNS boolean
LANGUAGE plpgsql
STABLE SECURITY DEFINER
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM role_permissions rp
    JOIN permissions p ON rp.permission_id = p.id
    JOIN users u ON u.role_id = rp.role_id
    WHERE u.id = auth.uid()
      AND p.permission_code = permission_code
      AND rp.deleted = false
  );
END;
$$;

Create RLS policies:

-- SELECT policy
CREATE POLICY "Users can view data_units in their organization"
ON data_units FOR SELECT
USING (
  deleted = false AND
  user_has_access_to_organization(organization_id) AND
  has_permission('units.view')
);

-- INSERT policy
CREATE POLICY "Users can create data_units in their organization"
ON data_units FOR INSERT
WITH CHECK (
  deleted = false AND
  user_has_access_to_organization(organization_id) AND
  has_permission('units.create')
);

-- UPDATE policy (handles both regular updates AND soft deletes)
CREATE POLICY "Users can update data_units in their organization"
ON data_units FOR UPDATE
USING (
  deleted = false AND
  user_has_access_to_organization(organization_id) AND
  (has_permission('units.update') OR has_permission('units.delete'))
)
WITH CHECK (
  user_has_access_to_organization(organization_id) AND
  (((deleted = false) AND has_permission('units.update')) OR
   ((deleted = true) AND has_permission('units.delete')))
);

-- Block hard deletes
CREATE POLICY "Block hard deletes on data_units"
ON data_units FOR DELETE
USING (false);

2. Test Data Setup

-- Assume user with ID '06ffcce0-4b6a-4b59-a180-469db3d35902' exists
-- Assume organization with ID 'ce63ed5e-88d9-495b-9247-0a882182cef2' exists
-- User has both 'units.update' and 'units.delete' permissions

INSERT INTO data_units (id, name, organization_id)
VALUES (
  'cbb8bb04-d1a0-4e49-a857-c124f5714765',
  'Test Unit',
  'ce63ed5e-88d9-495b-9247-0a882182cef2'
);

3. Test from SQL Editor (WORKS ✅)

-- Set user context to match the authenticated user
BEGIN;
SET LOCAL request.jwt.claim.sub = '06ffcce0-4b6a-4b59-a180-469db3d35902';
SET LOCAL request.jwt.claim.role = 'authenticated';

-- Verify permissions
SELECT
  has_permission('units.update') AS has_update,
  has_permission('units.delete') AS has_delete,
  user_has_access_to_organization('ce63ed5e-88d9-495b-9247-0a882182cef2'::uuid) AS has_org_access;
-- Result: has_update=true, has_delete=true, has_org_access=true

-- Test the WITH CHECK clause logic directly
SELECT (
  user_has_access_to_organization('ce63ed5e-88d9-495b-9247-0a882182cef2'::uuid) AND
  (((false = false) AND has_permission('units.update')) OR
   ((true = true) AND has_permission('units.delete')))
) AS with_check_result;
-- Result: with_check_result=true

-- Attempt soft delete
UPDATE data_units
SET deleted = true, updated_at = NOW()
WHERE id = 'cbb8bb04-d1a0-4e49-a857-c124f5714765';
-- Result: SUCCESS ✅

COMMIT;

4. Test from supabase-js Client (FAILS ❌)

JavaScript code:

import { createClient } from '@supabase/supabase-js';

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

// User is authenticated with session
const { data: { session } } = await supabase.auth.getSession();
console.log('Session user ID:', session.user.id);
// Output: 06ffcce0-4b6a-4b59-a180-469db3d35902

// Test 1: Regular UPDATE (updating name) - WORKS ✅
const { data: updateResult, error: updateError } = await supabase
  .from('data_units')
  .update({ name: 'Updated Name' })
  .eq('id', 'cbb8bb04-d1a0-4e49-a857-c124f5714765')
  .select()
  .single();

console.log('Regular update:', { data: updateResult, error: updateError });
// Result: SUCCESS ✅

// Test 2: Soft delete (setting deleted = true) - FAILS ❌
const { data: deleteResult, error: deleteError } = await supabase
  .from('data_units')
  .update({ deleted: true })
  .eq('id', 'cbb8bb04-d1a0-4e49-a857-c124f5714765')
  .select()
  .single();

console.log('Soft delete:', { data: deleteResult, error: deleteError });
// Result: FAILS ❌
// Error: {
//   code: '42501',
//   details: null,
//   hint: null,
//   message: 'new row violates row-level security policy for table "data_units"'
// }

System Info

npx envinfo --system --npmPackages '{supabase,@supabase/*}' --binaries --browsers

  System:
    OS: Linux 6.14 Ubuntu 24.04.3 LTS 24.04.3 LTS (Noble Numbat)
    CPU: (2) x64 Intel(R) Xeon(R) Platinum 8175M CPU @ 2.50GHz
    Memory: 2.55 GB / 3.75 GB
    Container: Yes
    Shell: 5.2.21 - /bin/bash
  Binaries:
    Node: 20.19.5 - /home/ubuntu/.nvm/versions/node/v20.19.5/bin/node
    npm: 10.8.2 - /home/ubuntu/.nvm/versions/node/v20.19.5/bin/npm
  npmPackages:
    @supabase/supabase-js: ^2.76.1 => 2.81.1 




- **Supabase JS Version**: @supabase/supabase-js (latest)
- **Database**: PostgreSQL via Supabase
- **Client**: Browser (React application)
- **Authentication**: JWT-based (anon key with authenticated user session)

Used Package Manager

npm

Logs

No response

Validations

• Follow our Code of Conduct
• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Make sure this is a Supabase JS Library issue and not an issue with the Supabase platform. If it's a Supabase platform related bug, it should likely be reported to supabase/supabase instead.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Discord Chat Server.
• The provided reproduction is a minimal reproducible example of the bug.

[7ttp]

hi @brunocek thanks for reporting 💚
This looks like an RLS policy design issue rather than a bug😢.
Your has_permission() function queries the users table which likely has its own RLS when PostgreSQL evaluates the WITH CHECK clause, that nested query may be blocked.
Try adding SECURITY DEFINER to your helper functions or check RLS on users/role_permissions tables.

Please let me know if the issue still persists.