How to fix insufficient privilege to bypass row security in PostgreSQL

-- Check if RLS is enabled on a table:
SELECT schemaname, tablename, rowsecurity, forcerowsecurity
FROM pg_tables
WHERE tablename = 'your_table_name';

-- View all RLS policies on the table:
SELECT tablename, policyname, permissive, roles, qual, with_check
FROM pg_policies
WHERE tablename = 'your_table_name';

-- Shorter check:
\d your_table_name
-- Look for "Policies:" section in output

-- Check if a user has BYPASSRLS privilege:
SELECT rolname, rolbypassrls, rolsuper
FROM pg_roles
WHERE rolname = 'your_username';

-- View all users and their BYPASSRLS status:
SELECT rolname, rolbypassrls, rolsuper
FROM pg_roles
ORDER BY rolname;

-- Connect as superuser (postgres or admin account)
-- Grant BYPASSRLS to the user:
ALTER ROLE your_username BYPASSRLS;

-- Verify it was granted:
SELECT rolname, rolbypassrls FROM pg_roles WHERE rolname = 'your_username';

-- First, ensure RLS is enabled on the table:
ALTER TABLE your_table_name ENABLE ROW LEVEL SECURITY;

-- Create a policy that allows users to see only their own data
-- (assuming a user_id column and current_user_id session variable):
CREATE POLICY user_isolation_policy
ON your_table_name
FOR ALL
TO your_role
USING (user_id = current_setting('app.current_user_id')::int);

-- Or for role-based access:
CREATE POLICY role_based_policy
ON your_table_name
FOR ALL
TO your_role
USING (role = current_user);

-- For a simple "allow all" policy (if RLS is enforced but you want public access):
CREATE POLICY allow_all_policy
ON your_table_name
FOR ALL
TO your_role
USING (true);

-- Policy for SELECT (USING clause):
CREATE POLICY select_own_data
ON your_table_name
FOR SELECT
TO your_role
USING (user_id = current_setting('app.current_user_id')::int);

-- Policy for INSERT (WITH CHECK clause):
CREATE POLICY insert_own_data
ON your_table_name
FOR INSERT
TO your_role
WITH CHECK (user_id = current_setting('app.current_user_id')::int);

-- Policy for UPDATE:
CREATE POLICY update_own_data
ON your_table_name
FOR UPDATE
TO your_role
USING (user_id = current_setting('app.current_user_id')::int)
WITH CHECK (user_id = current_setting('app.current_user_id')::int);

-- Policy for DELETE:
CREATE POLICY delete_own_data
ON your_table_name
FOR DELETE
TO your_role
USING (user_id = current_setting('app.current_user_id')::int);

-- Set session variable at the start of a connection:
SET app.current_user_id = 123;  -- Replace 123 with actual user ID

-- Or in PostgreSQL 13+, use transaction-scoped settings:
SET LOCAL app.current_user_id = 123;

-- From application code (example in PostgreSQL client libraries):
-- Python (psycopg2/psycopg3):
with conn.cursor() as cur:
    cur.execute("SET app.current_user_id = %s", (user_id,))
    cur.execute("SELECT * FROM your_table_name")

-- Node.js (pg):
await client.query("SET app.current_user_id = $1", [user_id]);
await client.query("SELECT * FROM your_table_name");

-- Connect as the regular user (not superuser):
psql -U your_username -d your_database

-- Set session variable if needed:
SET app.current_user_id = 123;

-- Try to query the table:
SELECT * FROM your_table_name;

-- Check how many rows the user can see:
SELECT COUNT(*) FROM your_table_name;

-- Try to insert data:
INSERT INTO your_table_name (user_id, data) VALUES (123, 'test');

-- Check user's table privileges:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public' AND table_name = 'your_table_name' AND grantee = 'your_username';

-- Check active RLS policies:
SELECT policyname, permissive, roles, qual, with_check
FROM pg_policies
WHERE tablename = 'your_table_name';

-- Combine view: what can this user actually access?
-- Run as the user themselves:
SELECT * FROM your_table_name;  -- Should return only permitted rows