"""Integration research on isolated PostgreSQL schema and private MinIO bucket. Run through scripts/test-reconciliation.sh; credentials stay in environment. Only UUID-named test resources are removed on completion. """ import asyncio import hashlib import io import json import subprocess import sys import time from concurrent.futures import ThreadPoolExecutor from datetime import datetime, timedelta, timezone from unittest.mock import patch from uuid import uuid4 from minio import Minio from minio.error import S3Error from urllib.parse import urlsplit from PIL import Image from sqlalchemy import event, func, select, text from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine from src.core.db.models import ( Attachment, Base, Chat, ChatMember, Message, MessageAttachment, StorageFinding, UploadIntent, User, ) from src.core.errors import ApiError from src.core.db.repo.message import create_idempotent_message from src.core.settings import settings from src.core.storage import StorageMetrics, make_storage from src.core.upload_coordination import lock_object from src.maintenance.reconcile import Reconciler from src.service.attachment import AttachmentService schema = 'reconcile_' + uuid4().hex bucket = 'reconcile-' + uuid4().hex original_bucket = settings.s3_bucket root = make_storage(role='bootstrap') content_buffer = io.BytesIO() Image.new('RGB', (32, 24), 'green').save(content_buffer, format='PNG') content = content_buffer.getvalue() user_id, chat_id = uuid4(), uuid4() checks = [] def passed(name): checks.append(name) print(json.dumps({'check': name, 'result': 'PASS'}), flush=True) def fake_request(): from starlette.requests import Request delivered = False async def receive(): nonlocal delivered if delivered: return {'type': 'http.disconnect'} delivered = True return {'type': 'http.request', 'body': content, 'more_body': False} return Request({'type':'http', 'method':'POST', 'path':'/', 'headers':[(b'content-type', b'image/png'), (b'content-length', str(len(content)).encode())]}, receive) def error(code): return S3Error(None, code, 'injected', 'test-resource', 'test-request', 'test-host') class FailPut: def put_object(self, *args, **kwargs): raise OSError('injected S3 failure') class FailCommit(AsyncSession): async def commit(self): raise OSError('injected commit failure') async def snapshot(factory): async with factory() as s: attachments = [(str(a.id), a.object_key, str(a.storage_missing_since)) for a in (await s.scalars(select(Attachment).order_by(Attachment.id)))] intents = [(a.object_key,a.state) for a in (await s.scalars(select(UploadIntent).order_by(UploadIntent.object_key)))] findings = [(f.object_key,f.kind,str(f.first_seen),str(f.last_seen),str(f.resolved_at),f.fingerprint) for f in (await s.scalars(select(StorageFinding).order_by(StorageFinding.object_key,StorageFinding.kind)))] objects = sorted((o.object_name,o.etag) for o in root.list_objects(bucket,recursive=True)) return attachments,intents,findings,objects async def main(): global schema, bucket admin_engine = create_async_engine(settings.sqlalchemy_database_url) async with admin_engine.begin() as conn: await conn.execute(text(f'CREATE SCHEMA {schema}')) engine = create_async_engine(settings.sqlalchemy_database_url, connect_args={'server_settings': {'search_path':schema}}) factory = async_sessionmaker(engine, expire_on_commit=False) # Verify the deployed application's credentials against its actual bucket. api_client=make_storage(role='api') cleaner_client=make_storage(role='reconciler') probe='originals/permission-probe/'+uuid4().hex outside='outside/'+uuid4().hex root.put_object(original_bucket,probe,io.BytesIO(content),len(content)) root.put_object(original_bucket,outside,io.BytesIO(content),len(content)) try: response=api_client.get_object(original_bucket,probe) assert response.read()==content response.close();response.release_conn() for operation in [ lambda: api_client.remove_object(original_bucket,probe), lambda: api_client.get_object(original_bucket,outside), lambda: cleaner_client.put_object(original_bucket,probe,io.BytesIO(content),len(content)), lambda: cleaner_client.remove_object(original_bucket,outside), ]: try: operation() except S3Error as exc: assert exc.code=='AccessDenied' else: raise AssertionError('role/prefix policy allowed a forbidden operation') endpoint=urlsplit(settings.s3_endpoint) anonymous=Minio(endpoint.netloc,secure=endpoint.scheme=='https',region=settings.s3_region) try: anonymous.get_object(original_bucket,probe) except S3Error as exc: assert exc.code=='AccessDenied' and exc.response.status==403 else: raise AssertionError('private bucket allowed an anonymous read') passed('direct unsigned GET of an existing S3 object returns 403') cleaner_client.remove_object(original_bucket,probe) passed('deployed API cannot delete; reconciler cannot upload; both are confined to originals/') finally: root.remove_object(original_bucket,probe) root.remove_object(original_bucket,outside) root.make_bucket(bucket) settings.s3_bucket = bucket try: async with engine.begin() as conn: await conn.run_sync(Base.metadata.create_all) now = datetime.now(timezone.utc) async with factory.begin() as s: s.add(User(id=user_id,username='test_user',display_name='Test',password_hash='test',is_admin=False,created_at=now)) await s.flush() s.add(Chat(id=chat_id,title='Test',created_at=now)) await s.flush() s.add(ChatMember(chat_id=chat_id,user_id=user_id)) with patch('src.service.attachment.get_storage', return_value=root): async with factory.begin() as s: good = await AttachmentService(s).upload(fake_request(),chat_id,user_id,'photo.png') async with factory.begin() as s: intent = await s.get(UploadIntent,good.object_key) assert intent.state=='committed' s.add(Message(id=uuid4(),chat_id=chat_id,sender_id=user_id,text='',created_at=now, attachments=[MessageAttachment(attachment_id=good.id,chat_id=chat_id,position=0)])) passed('real upload atomically commits Attachment and intent; image is referenced by a message') async with factory.begin() as s: confirmed_message=await create_idempotent_message(s,message_id=uuid4(),chat_id=chat_id, sender_id=user_id,text='confirmed',created_at=now,idempotency_key='confirmed-image', attachment_ids=[good.id]) # Failure on S3 side: durable intent remains, no ready attachment is published. with patch('src.service.attachment.get_storage', return_value=FailPut()): try: async with factory.begin() as s: await AttachmentService(s).upload(fake_request(),chat_id,user_id,'fail.png') except ApiError as exc: assert exc.status_code==503 else: raise AssertionError('S3 failure accepted') async with factory() as s: failed_put_key = await s.scalar(select(UploadIntent.object_key).where(UploadIntent.state=='pending')) assert failed_put_key passed('S3 PUT failure leaves an accounted pending attempt, no ready attachment') # Failure on DB side AFTER the actual S3 PUT; registration uses independent session. broken_factory = async_sessionmaker(engine, class_=FailCommit, expire_on_commit=False) with patch('src.service.attachment.get_storage', return_value=root): try: async with broken_factory.begin() as s: await AttachmentService(s).upload(fake_request(),chat_id,user_id,'db-fail.png') except OSError: pass else: raise AssertionError('DB failure accepted') async with factory() as s: pending = list(await s.scalars(select(UploadIntent.object_key).where(UploadIntent.state=='pending'))) db_fail_key = next(k for k in pending if k!=failed_put_key) assert root.stat_object(bucket,db_fail_key).size==len(content) passed('DB commit failure after S3 PUT leaves a durable discoverable orphan') # Kill the actual upload implementation after PUT, before its final COMMIT. import base64 child = """ import asyncio,base64,json,os,sys from unittest.mock import patch from starlette.requests import Request from sqlalchemy.ext.asyncio import AsyncSession,async_sessionmaker,create_async_engine from src.core.settings import settings from src.core.storage import make_storage from src.service.attachment import AttachmentService from uuid import UUID p=json.load(sys.stdin) settings.s3_bucket=p['bucket'] class CrashSession(AsyncSession): async def commit(self): os._exit(23) async def main(): engine=create_async_engine(settings.sqlalchemy_database_url,connect_args={'server_settings':{'search_path':p['schema']}}) factory=async_sessionmaker(engine,class_=CrashSession,expire_on_commit=False) data=base64.b64decode(p['content']) async def receive(): return {'type':'http.request','body':data,'more_body':False} request=Request({'type':'http','method':'POST','path':'/','headers':[(b'content-type',b'image/png')]},receive) with patch('src.service.attachment.get_storage',return_value=make_storage(role='bootstrap')): async with factory.begin() as s: await AttachmentService(s).upload(request,UUID(p['chat']),UUID(p['user']),'crash.png') asyncio.run(main()) """ crash = subprocess.run([sys.executable,'-c',child],input=json.dumps({ 'bucket':bucket,'schema':schema,'chat':str(chat_id),'user':str(user_id), 'content':base64.b64encode(content).decode()}),text=True,capture_output=True) assert crash.returncode==23, 'upload child did not reach the injected crash' async with factory() as s: crashed_keys=set(await s.scalars(select(UploadIntent.object_key).where(UploadIntent.state=='pending')))-set(pending) assert len(crashed_keys)==1 assert root.stat_object(bucket,crashed_keys.pop()).size==len(content) passed('process death after real PUT preserves intent; PostgreSQL releases the upload lock') # Existing journal plus old object, with a live writer's lock. active_key='originals/active-'+uuid4().hex late_key='originals/late-'+uuid4().hex orphan_key='originals/orphan-'+uuid4().hex for key in (active_key,late_key,orphan_key): root.put_object(bucket,key,io.BytesIO(content),len(content)) async with factory.begin() as s: for key in (active_key,late_key): s.add(UploadIntent(object_key=key,state='pending',created_at=now-timedelta(hours=1))) root.put_object(bucket,'outside/keep',io.BytesIO(b'keep'),4) await asyncio.sleep(1.2) before=await snapshot(factory) report=await Reconciler(factory,root,grace_seconds=1).run() assert before==await snapshot(factory) assert report['counts']['would_delete_orphan']>=3 passed('dry-run changes neither DB records nor S3 objects') # Publish one candidate after dry-run, before apply. Cleaner must recheck. async with factory.begin() as s: s.add(Attachment(id=uuid4(),chat_id=chat_id,owner_id=user_id,filename='late.png', object_key=late_key,content_type='image/png',size_bytes=len(content), sha256=hashlib.sha256(content).hexdigest(),created_at=now,updated_at=now)) (await s.get(UploadIntent,late_key)).state='committed' fresh_key='originals/fresh-'+uuid4().hex root.put_object(bucket,fresh_key,io.BytesIO(content),len(content)) async with factory.begin() as s: s.add(UploadIntent(object_key=fresh_key,state='pending',created_at=datetime.now(timezone.utc))) async with factory.begin() as held: await lock_object(held,active_key,wait=True) report=await Reconciler(factory,root,apply=True,grace_seconds=1).run() assert report['counts']['protected_active']>=1 assert report['counts']['protected_fresh']>=1 assert report['counts']['deleted_orphan']>=2 assert root.stat_object(bucket,active_key) assert root.stat_object(bucket,good.object_key) assert root.stat_object(bucket,late_key) assert root.stat_object(bucket,fresh_key) assert root.stat_object(bucket,'outside/keep') async with factory() as s: assert (await s.get(UploadIntent,failed_put_key)).state=='abandoned' passed('apply cleans old orphan/empty attempt; preserves active, fresh, referenced and late-committed objects') async with factory.begin() as s: s.add(Attachment(id=uuid4(),chat_id=chat_id,owner_id=user_id,filename='fresh.png', object_key=fresh_key,content_type='image/png',size_bytes=len(content), sha256=hashlib.sha256(content).hexdigest(),created_at=now,updated_at=now)) (await s.get(UploadIntent,fresh_key)).state='committed' # Missing metadata counterpart: do not delete messages or fabricate bytes. root.remove_object(bucket,good.object_key) before=await snapshot(factory) report=await Reconciler(factory,root,grace_seconds=1).run() assert report['counts']['would_mark_missing']==1 assert before==await snapshot(factory) await Reconciler(factory,root,apply=True,grace_seconds=1).run() async with factory() as s: assert (await s.get(Attachment,good.id)).storage_missing_since is not None assert await s.scalar(select(func.count()).select_from(MessageAttachment))==2 try: await AttachmentService(s).get(good.id,user_id) except ApiError as exc: assert exc.status_code==503 else: raise AssertionError('missing original reported ready') passed('missing original is quarantined with 503; metadata and message link survive') async with factory.begin() as s: replay=await create_idempotent_message(s,message_id=uuid4(),chat_id=chat_id, sender_id=user_id,text='confirmed',created_at=now,idempotency_key='confirmed-image', attachment_ids=[good.id]) assert replay==confirmed_message try: async with factory.begin() as s: await create_idempotent_message(s,message_id=uuid4(),chat_id=chat_id, sender_id=user_id,text='new',created_at=now,idempotency_key='new-missing-image', attachment_ids=[good.id]) except ApiError as exc: assert exc.status_code==409 else: raise AssertionError('new message accepted a missing attachment') passed('missing content blocks new links, but confirmed idempotency replays stay intact') root.put_object(bucket,good.object_key,io.BytesIO(b'x'*len(content)),len(content)) report=await Reconciler(factory,root,apply=True,grace_seconds=1).run() assert report['counts']['restore_checksum_mismatch']==1 root.put_object(bucket,good.object_key,io.BytesIO(content),len(content)) report=await Reconciler(factory,root,apply=True,grace_seconds=1).run() assert report['counts']['recovered']==1 async with factory() as s: assert (await AttachmentService(s).get(good.id,user_id)).storage_missing_since is None report=await Reconciler(factory,root,apply=True,grace_seconds=1).run() assert report['counts'].get('recovered',0)==0 assert report['counts'].get('deleted_orphan',0)==0 passed('recovery requires exact SHA-256; repeat apply is safe') # Authorization failure is not absence. No metadata quarantine or deletions. class DeniedHead: def list_objects(self,*args,**kwargs): return iter(()) def stat_object(self,*args,**kwargs): raise error('AccessDenied') before=await snapshot(factory) try: await Reconciler(factory,DeniedHead(),apply=True,grace_seconds=1).run() except S3Error as exc: assert exc.code=='AccessDenied' else: raise AssertionError('AccessDenied was interpreted as missing') assert before==await snapshot(factory) with patch('src.maintenance.reconcile.lock_object',side_effect=OSError('DB unavailable')): try: await Reconciler(factory,root,apply=True,grace_seconds=1).run() except OSError: pass else: raise AssertionError('DB unavailable did not stop repair') assert before==await snapshot(factory) passed('S3 AccessDenied and unavailable DB abort safely, without false missing/deletion') # Lost DELETE response: S3 changed, DB transaction rolls back. Next scan resolves it. lost_key='originals/delete-loss/'+uuid4().hex root.put_object(bucket,lost_key,io.BytesIO(content),len(content)) async with factory.begin() as s: s.add(UploadIntent(object_key=lost_key,state='pending',created_at=now-timedelta(hours=1))) await asyncio.sleep(1.2) class LostDelete: def __getattr__(self,name): return getattr(root,name) def remove_object(self,*args,**kwargs): root.remove_object(*args,**kwargs) raise OSError('injected lost DELETE response') try: await Reconciler(factory,LostDelete(),apply=True,grace_seconds=1,prefix='originals/delete-loss/').run() except OSError: pass else: raise AssertionError('lost DELETE response was ignored') await Reconciler(factory,root,apply=True,grace_seconds=1,prefix='originals/delete-loss/').run() async with factory() as s: assert (await s.get(UploadIntent,lost_key)).state=='abandoned' passed('lost DELETE response is repaired on repeat; no live object is affected') # Failure BEFORE deletion must retain the ability to retry after recovery. retry_key='originals/delete-retry/'+uuid4().hex root.put_object(bucket,retry_key,io.BytesIO(content),len(content)) async with factory.begin() as s: s.add(UploadIntent(object_key=retry_key,state='pending',created_at=now-timedelta(hours=1))) await asyncio.sleep(1.2) class FailedDelete: def __getattr__(self,name): return getattr(root,name) def remove_object(self,*args,**kwargs): raise OSError('injected S3 delete outage') try: await Reconciler(factory,FailedDelete(),apply=True,grace_seconds=1,prefix='originals/delete-retry/').run() except OSError: pass else: raise AssertionError('failed delete was reported successful') assert root.stat_object(bucket,retry_key) async with factory() as s: assert (await s.get(UploadIntent,retry_key)).state=='pending' await Reconciler(factory,root,apply=True,grace_seconds=1,prefix='originals/delete-retry/').run() async with factory() as s: assert (await s.get(UploadIntent,retry_key)).state=='abandoned' try: root.stat_object(bucket,retry_key) except S3Error as exc: assert exc.code=='NoSuchKey' else: raise AssertionError('delete was not retried after S3 recovery') passed('failed S3 DELETE preserves pending; recovery retries and actually removes the orphan') # Cost experiment: bounded listing must cross the 1000-object page boundary. metrics=StorageMetrics() measured=make_storage(role='bootstrap',metrics=metrics) statement_count=0 @event.listens_for(engine.sync_engine,'before_cursor_execute') def count(*_): nonlocal statement_count statement_count+=1 results=[] for n in (10,100,1005): prefix=f'originals/benchmark/{n}/' def put(i): root.put_object(bucket,prefix+f'{i:06}',io.BytesIO(content),len(content)) with ThreadPoolExecutor(max_workers=16) as pool: list(pool.map(put,range(n))) for repeat in range(3): metrics.requests.clear(); statement_count=0; started=time.perf_counter() report=await Reconciler(factory,measured,prefix=prefix,grace_seconds=300,sample_limit=0).run() elapsed=time.perf_counter()-started assert report['counts']['objects_scanned']==n assert metrics.requests['LIST']==(2 if n>1000 else 1) results.append({'objects':n,'repeat':repeat+1,'seconds':round(elapsed,4), 's3_requests':dict(metrics.requests),'db_statements':statement_count}) print(json.dumps({'experiment':'scan_cost','runs':results}),flush=True) passed('1005 objects cross ListObjectsV2 pagination; cost measured at 3 sizes x 3 runs') print(json.dumps({'result':'PASS','checks':checks,'isolated_resources':True}),flush=True) finally: # These resources belong exclusively to this test run. for obj in root.list_objects(bucket,recursive=True): root.remove_object(bucket,obj.object_name) root.remove_bucket(bucket) settings.s3_bucket=original_bucket await engine.dispose() async with admin_engine.begin() as conn: await conn.execute(text(f'DROP SCHEMA {schema} CASCADE')) await admin_engine.dispose() asyncio.run(main())