supabase-postgres-best-practices/examples/run_rls_fixture.py
Version c9be0e931b79.bb1 · MIT. This preview displays packaged text and does not execute code. Treat the contents as untrusted instructions.
← Return to resource and package checksum
"""Synthetic PostgreSQL role/policy experiment; run only in its disposable Docker DB."""
import datetime as dt,hashlib,json,os,re
from pathlib import Path
import psycopg
from psycopg import sql
root=Path(os.environ.get('RLS_INPUT_DIR','/input'))
output=Path(os.environ.get('RLS_OUTPUT_FILE','/work/rls-fixture-evidence.json'))
host=os.environ['PGFIXTURE_HOST']
assert host=='bb-v50-rls-db' and os.environ.get('PGFIXTURE_DISPOSABLE')=='yes'
assert os.getuid()==10001
source=root/'previous/supabase-postgres-best-practices'
manifest=json.loads((root/'source-manifest.json').read_text());commit=manifest['commit']
assert commit=='c9be0e931b7930f7d02126d04774d904c381e7d7'
digest=lambda raw:hashlib.sha256(raw).hexdigest()
for item in manifest['files']:
raw=(root/'previous'/item['package_path']).read_bytes()
assert digest(raw)==item['sha256'] and hashlib.sha1(b'blob '+str(len(raw)).encode()+b'\0'+raw).hexdigest()==item['git_blob_sha1']
cases=[];executed=[];connections=[]
def check(name,condition,detail=None):
assert condition,name
cases.append({'name':name,'status':'passed','detail':detail})
def connect(user,password):
connection=psycopg.connect(host=host,dbname='fixture',user=user,password=password,autocommit=True,
connect_timeout=10,application_name='BB-Skills-disposable-RLS-fixture')
connection.execute("SET statement_timeout='2s'")
connections.append(connection);return connection
def native(connection,path,index):
raw=(source/path).read_text();blocks=re.findall(r'```sql\s*\n(.*?)```',raw,re.S);code=blocks[index]
cursor=connection.execute(code);results=[]
while True:
if cursor.description is not None:results.append(cursor.fetchall())
if not cursor.nextset():break
executed.append({'source':path,'block_index':index,'block_sha256':digest(code.encode()),'source_rewritten':False})
return results
def denied(connection,name,statement,state):
try:
connection.execute(statement)
except psycopg.Error as error:
check(name,error.sqlstate==state,{'sqlstate':error.sqlstate});return
raise AssertionError('Unexpectedly accepted: '+name)
def rows(connection):return connection.execute('SELECT id,user_id,quantity FROM public.orders ORDER BY id').fetchall()
def context(connection,identity):connection.execute('SELECT set_config(%s,%s,false)',('app.current_user_id',str(identity)))
try:
admin=connect('fixture_bootstrap',os.environ['PGFIXTURE_BOOTSTRAP_PASSWORD'])
assert admin.execute('SELECT current_database(),session_user').fetchone()==('fixture','fixture_bootstrap')
assert admin.execute("SELECT count(*) FROM pg_class c JOIN pg_namespace n ON c.relnamespace=n.oid WHERE n.nspname='public' AND c.relkind IN ('r','p')").fetchone()[0]==0
version=admin.execute('SHOW server_version').fetchone()[0]
assert int(admin.execute('SHOW server_version_num').fetchone()[0])//10000==17
admin.execute('REVOKE CREATE ON SCHEMA public FROM PUBLIC')
admin.execute('REVOKE TEMPORARY ON DATABASE fixture FROM PUBLIC')
for role in ['fixture_table_owner','fixture_reader','fixture_unprivileged']:
admin.execute(sql.SQL('CREATE ROLE {} LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION NOBYPASSRLS PASSWORD {}').format(sql.Identifier(role),sql.Literal('synthetic-rls-only')))
admin.execute('GRANT USAGE ON SCHEMA public TO fixture_table_owner,fixture_unprivileged')
admin.execute('CREATE TABLE public.orders(id serial PRIMARY KEY,user_id bigint NOT NULL,quantity integer NOT NULL CHECK(quantity>0))')
admin.execute('ALTER TABLE public.orders OWNER TO fixture_table_owner')
admin.execute('CREATE TABLE public.products(id integer PRIMARY KEY)')
admin.execute('CREATE TABLE public.categories(id integer PRIMARY KEY)')
admin.execute('INSERT INTO public.products VALUES(1)')
admin.execute('INSERT INTO public.categories VALUES(1)')
admin.execute('INSERT INTO public.orders(user_id,quantity) VALUES(123,1),(456,2),(123,3)')
# Original least-privilege block creates app_readonly, app_writer and app_user.
# Its documented placeholder password is used only in this internal fixture.
native(admin,'references/security-privileges.md',1)
native(admin,'references/security-privileges.md',2)
admin.execute('GRANT app_readonly TO fixture_reader')
writer=connect('app_user','xxx');reader=connect('fixture_reader','synthetic-rls-only')
owner=connect('fixture_table_owner','synthetic-rls-only');unprivileged=connect('fixture_unprivileged','synthetic-rls-only')
role_flags={}
for name,connection in [('writer',writer),('reader',reader),('owner',owner),('unprivileged',unprivileged)]:
flags=connection.execute('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=current_user').fetchone()
role_flags[name]=list(flags);check(name+'_has_no_elevated_role_attributes',not any(flags))
rights=writer.execute("SELECT has_table_privilege(current_user,'public.orders','SELECT'),has_table_privilege(current_user,'public.orders','INSERT'),has_table_privilege(current_user,'public.orders','UPDATE'),has_table_privilege(current_user,'public.orders','DELETE'),has_table_privilege(current_user,'public.orders','TRUNCATE')").fetchone()
check('native_writer_inherits_only_declared_order_privileges',rights==(True,True,True,False,False),list(rights))
check('native_reader_can_select_declared_product_table',reader.execute('SELECT id FROM public.products').fetchall()==[(1,)])
denied(reader,'native_reader_cannot_select_ungranted_orders','SELECT * FROM public.orders','42501')
check('ordinary_writer_sees_all_rows_before_RLS',rows(writer)==[(1,123,1),(2,456,2),(3,123,3)])
# Execute the complete original correct RLS block, as an actual ordinary owner.
result=native(owner,'references/security-rls-basics.md',1)
check('native_RLS_block_filters_ordinary_owner',result==[[(1,123,1),(3,123,3)]])
metadata=admin.execute("SELECT c.relrowsecurity,c.relforcerowsecurity,p.polcmd,p.polpermissive,p.polwithcheck IS NULL,p.polqual IS NOT NULL FROM pg_class c JOIN pg_policy p ON p.polrelid=c.oid WHERE c.oid='public.orders'::regclass").fetchone()
check('native_policy_enabled_forced_all_and_implicit_check',metadata==(True,True,'*',True,True,True),list(metadata))
context(writer,123);check('ordinary_writer_sees_own_rows',rows(writer)==[(1,123,1),(3,123,3)])
context(writer,456);check('caller_can_change_session_claim_to_other_identity',rows(writer)==[(2,456,2)])
context(writer,123)
check('superuser_bypasses_even_forced_RLS',rows(admin)==[(1,123,1),(2,456,2),(3,123,3)])
# Additional fixture grant is separate from the unchanged upstream grants.
admin.execute('GRANT SELECT ON public.orders TO app_readonly')
denied(reader,'missing_session_context_reports_error','SELECT * FROM public.orders','42704')
context(reader,'not-an-integer');denied(reader,'invalid_session_context_reports_cast_error','SELECT * FROM public.orders','22P02')
context(reader,123);check('granted_reader_is_filtered_by_RLS',rows(reader)==[(1,123,1),(3,123,3)])
denied(reader,'reader_insert_denied_by_table_privilege','INSERT INTO public.orders(user_id,quantity) VALUES(123,4)','42501')
context(unprivileged,123);denied(unprivileged,'policy_does_not_grant_table_access','SELECT * FROM public.orders','42501')
inserted=writer.execute('INSERT INTO public.orders(user_id,quantity) VALUES(123,4) RETURNING id').fetchone()[0]
check('native_all_policy_allows_matching_insert',inserted==4 and len(rows(writer))==3)
denied(writer,'implicit_with_check_rejects_other_identity_insert','INSERT INTO public.orders(user_id,quantity) VALUES(456,5)','42501')
denied(writer,'implicit_with_check_rejects_ownership_change','UPDATE public.orders SET user_id=456 WHERE id=1','42501')
check('rejected_ownership_change_leaves_row_unchanged',admin.execute('SELECT user_id,quantity FROM public.orders WHERE id=1').fetchone()==(123,1))
check('other_identity_update_affects_zero_rows',writer.execute('UPDATE public.orders SET quantity=9 WHERE id=2').rowcount==0)
check('other_identity_row_remains_unchanged',admin.execute('SELECT user_id,quantity FROM public.orders WHERE id=2').fetchone()==(456,2))
check('matching_update_succeeds',writer.execute('UPDATE public.orders SET quantity=7 WHERE id=1').rowcount==1)
denied(writer,'writer_delete_denied_by_table_privilege','DELETE FROM public.orders WHERE id=4','42501')
denied(writer,'writer_truncate_denied_by_table_privilege','TRUNCATE public.orders','42501')
index_code=re.findall(r'```sql\s*\n(.*?)```',(source/'references/security-rls-performance.md').read_text(),re.S)[3]
denied(owner,'table_owner_without_schema_create_cannot_create_index',index_code,'42501')
native(admin,'references/security-rls-performance.md',3)
index=admin.execute("SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indexrelid='public.orders_user_id_idx'::regclass").fetchone()[0]
check('native_RLS_column_index_created','USING btree (user_id)' in index,index)
owner.execute('ALTER TABLE public.orders NO FORCE ROW LEVEL SECURITY')
check('ordinary_table_owner_bypasses_without_force',len(rows(owner))==4)
owner.execute('ALTER TABLE public.orders FORCE ROW LEVEL SECURITY')
check('force_restores_owner_filtering',len(rows(owner))==3 and all(row[1]==123 for row in rows(owner)))
owner.execute('CREATE POLICY fixture_unsafe_read_all ON public.orders FOR SELECT USING(true)')
check('permissive_extra_policy_widens_select',len(rows(writer))==4)
owner.execute('DROP POLICY fixture_unsafe_read_all ON public.orders')
check('removing_extra_policy_restores_select_filter',len(rows(writer))==3)
writer.execute('BEGIN')
try:
writer.execute("SET LOCAL app.current_user_id='456'")
check('transaction_local_context_selects_other_identity',rows(writer)==[(2,456,2)])
writer.execute('COMMIT')
except Exception:
writer.execute('ROLLBACK');raise
check('transaction_local_context_restores_session_value',writer.execute("SELECT current_setting('app.current_user_id')").fetchone()[0]=='123' and len(rows(writer))==3)
admin.execute('CREATE TABLE public.fixture_no_policy(id integer PRIMARY KEY)')
admin.execute('INSERT INTO public.fixture_no_policy VALUES(1)')
admin.execute('ALTER TABLE public.fixture_no_policy ENABLE ROW LEVEL SECURITY')
admin.execute('GRANT SELECT,INSERT ON public.fixture_no_policy TO app_user')
check('enabled_RLS_without_policy_hides_rows',writer.execute('SELECT * FROM public.fixture_no_policy').fetchall()==[])
denied(writer,'enabled_RLS_without_policy_rejects_insert','INSERT INTO public.fixture_no_policy VALUES(2)','42501')
writer.execute('SET row_security=off')
denied(writer,'row_security_off_does_not_bypass_policy','SELECT * FROM public.orders','42501')
writer.execute('SET row_security=on')
check('restored_row_security_keeps_matching_rows',rows(writer)==[(1,123,7),(3,123,3),(4,123,4)])
check('fixture_retains_other_identity_and_no_failed_inserts',rows(admin)==[(1,123,7),(2,456,2),(3,123,3),(4,123,4)])
assert len({item['name'] for item in cases})==len(cases) and len(executed)==4
record={'executed_at':dt.datetime.now(dt.UTC).isoformat(),'python':os.sys.version.split()[0],'psycopg':psycopg.__version__,
'postgresql_version':version,'source_commit':commit,'original_files_verified':37,'observations':len(cases),
'cases':cases,'native_SQL_blocks_executed':4,'executed_blocks':executed,'role_attributes':role_flags,
'source_hashes':{name:digest((source/name).read_bytes()) for name in ['references/security-privileges.md','references/security-rls-basics.md','references/security-rls-performance.md']},
'runner_sha256':digest(Path(__file__).read_bytes()),'server_image_id':os.environ['PG_SERVER_IMAGE_ID'],
'client_image_id':os.environ['PG_CLIENT_IMAGE_ID'],'runtime_tested':False,'ai_client_executed':False,
'production_tested':False,'hosted_supabase_used':False,'real_identity_verified':False,'paid_API_used':False,
'performance_speedup_measured':False,'external_network_access':False,'synthetic_data_only':True,
'scope':'Four unchanged upstream SQL blocks plus independent role/claim/error controls in an empty PostgreSQL 17 fixture. Ordinary roles connect separately; bootstrap superuser is only setup and explicit bypass control. Session variables are deliberately caller-modifiable and are not authentication. No auth.uid(), hosted Supabase, private SECURITY DEFINER helper, concurrency, production authorization or full AI-client execution.'}
output.write_text(json.dumps(record,indent=2),encoding='utf-8');output.chmod(0o600)
print(json.dumps({k:v for k,v in record.items() if k not in ['cases','source_hashes','role_attributes','executed_blocks']}))
finally:
for connection in connections:connection.close()