CODEWITHSUNDEEP
postgresqleclipselinkglassfish-3saasmulti-tenant
Joe Javac
Joe Javac

Reputation: 111

eclipselink change schema at EntityManager callbacks

i have a multi-tenant application and i need to change the schema name at runtime so it is gonna be shared DB seperate schema SaaS design.

because creating an EntityManagerFactory is very expensive, i would like to create the EMF application-scoped and specify the schema before every DB calls after initiating the EntityManger. i am using Postgresql 8.1 and because Postgesql doesn't support schema selection at setting up the DB connection, i thought the only way to query from tables for different schemas is querying 'SET search_path = "my.schema"' before making the required DB calls.

i have tried;

StringBuilder sb = new StringBuilder();                
sb.append("SET search_path TO my.schema");            
entityManager_.createNativeQuery(sb.toString()).executeUpdate();

i have got an exception saying 'java.lang.IllegalStateException: You cannot call executeUpdate() on this query. It is the incorrect query type'

i am using eclipselink as PersistenceProvider and glassfish as application manager

is there anyway i can get this done ?

i am open to any suggesstions if there is a better way of accomplishing this

thanks in advance

Upvotes: 0

Views: 1117

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324455

Since you're doing tenant-per-schema, are you also using tenant specific database login roles (user IDs)? If so, you can bind a default search path to your user:

ALTER USER thetenant SET search_path = 'thetenant';

If you also:

REVOKE ALL ON SCHEMA thetenant FROM public;
GRANT ALL ON SCHEMA thetenant TO tenant;

you will isolate the users from each other to a much greater extent, though they'll still see stuff in pg_catalog and INFORMATION_SCHEMA.

This requires you to use a login role per tenant. This can be difficult where connection pooling is in play because Java connection pools can't usually switch the user ID of a pooled connection and have to keep one pool per user Id. PostgresSQL's SET SESSION AUTHORISATION statement can be useful, allowing you to log in as a single master user then switch to the user you need for a particular job, but I don't know if any Java pools support it directly. You can use external connection pools like PgBouncer and PgPool-II that are SET SESSION AUTHORISATION aware, or see if there's any way to write an interceptor so you can issue a SET SESSION AUTHORISATION on connections as they're checked out from the pool, and RESET SESSION AUTHORISATION when they're checked back in.

Even if you can't use role-based access, I'd try the same approach with your search path: see if you can do it with the connection pooler's help by trapping connections as they're checked out of the pool for a task, and as they're checked back in at release. How to do this would depend on the connection pool, though, and you may not want to get into the specifics of that.

BTW, why on earth are you using such a prehistoric version of PostgreSQL?

I don't know why EclipseLink is refusing your command. At face value it looks reasonable. Are you using ancient versions of other things, too? Which Glassfish and EclipseLink versions are you using?

Upvotes: 2

Related Questions