asmhatreandClaude Sonnet 5.5
98f4ed7411
Co-Authored-By: Claude Sonnet 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_017ic4b4VQVEJWetTT1dYzyJ
39 lines
2.5 KiB
Plaintext
39 lines
2.5 KiB
Plaintext
# schema-per-tenant, ONE shared pool + search_path, PostgreSQL (PostgreSQL 17.11), Hibernate 7.4.14.Final
|
|
|
|
== provisioning (the application's job, not Hibernate's)
|
|
schemas created: acme, globex, hooli, initech, stark, umbrella
|
|
-- provision("x; drop schema public cascade")
|
|
java.lang.IllegalArgumentException: not an acceptable tenant identifier
|
|
|
|
== the SQL Hibernate sends is unqualified; the connection decides which schema it hits
|
|
-- as acme: save(INV-1)
|
|
sql> insert into invoice (amount,number) values (?,?)
|
|
-- as acme: findAll()
|
|
sql> select i1_0.id,i1_0.amount,i1_0.number from invoice i1_0
|
|
rows = 2, numbers = [INV-1, INV-2]
|
|
-- as acme, inside the transaction: select current_setting('search_path')
|
|
acme
|
|
|
|
== cross-tenant reads: blocked by the schema, but ids overlap
|
|
ids: acme INV-1=1, acme INV-2=2, globex INV-9=1 (each schema has its own identity column)
|
|
-- as globex: findById(1) -> INV-9 (acme's id 1 is also globex's id 1: a different row)
|
|
-- as globex: findById(2) -> Optional.empty (acme's INV-2 is unreachable)
|
|
|
|
== native SQL needs no workaround here
|
|
-- as acme: native select count(*) from invoice -> 2 (acme's schema only; globex's row is not counted)
|
|
|
|
== failure modes
|
|
-- no tenant set: repo.findAll()
|
|
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction <- caused by com.ankurm.multitenancy.tenant.MissingTenantException: no tenant in TenantContext: refusing to open a tenant-scoped session
|
|
-- valid-looking tenant with no schema (ghostco): repo.findAll()
|
|
org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [ERROR: relation "invoice" does not exist <- caused by org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [ERROR: relation "invoice" does not exist <- caused by org.postgresql.util.PSQLException: ERROR: relation "invoice" does not exist
|
|
-- hostile tenant string reaching the provider: repo.findAll()
|
|
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction <- caused by java.lang.IllegalArgumentException: not an acceptable tenant identifier
|
|
|
|
== after all that work: what does an unrelated component see on the pool?
|
|
-- direct DataSource connection: show search_path -> "$user", public
|
|
|
|
== connections held by the shared pool after exercising 6 tenants
|
|
config: spring.datasource.hikari.maximum-pool-size=5, 6 tenants touched
|
|
pg_stat_activity rows with application_name='shared-pool': 5
|