Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    3

    Unanswered: Re-using a JDBC connection for a different user

    Hi,

    I'm investigating different ways to implement a multi-tenant architecture in my application. Some of the options involve using a different database user per tenant.

    I develop in Java, and Java application servers come with JDBC connection pooling. I don't want to have one pool of connections per tenant because there could be thousands of tenants.

    I'm wondering if it is possible to re-use an existing connection by switching to a different user. I've heard Oracle can do that. For example:

    CONNECT username / password;

    Thanks,
    Ryan

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    No, you cannot switch the database in Postgres (neither in JDBC nor in any other protocol)

    The only way I can think of, is to use one schema for every tenant, and then switch the current schema (using SET SCHEMA xyz) when obtaining a connection from the pool.

  3. #3
    Join Date
    Sep 2010
    Posts
    3
    Quote Originally Posted by shammat View Post
    No, you cannot switch the database in Postgres (neither in JDBC nor in any other protocol)

    The only way I can think of, is to use one schema for every tenant, and then switch the current schema (using SET SCHEMA xyz) when obtaining a connection from the pool.
    Thank you. I will investigate schemas in more detail. I don't think they will offer the same level of security, but there may be some industry standard multi-tenant architecture I can implement based on schema per tenant (single db user).


    Ryan

  4. #4
    Join Date
    Sep 2010
    Posts
    3
    Quote Originally Posted by shammat View Post
    No, you cannot switch the database in Postgres (neither in JDBC nor in any other protocol)
    My original question was about changing the USER of an existing JDBC connection. Your reply talks about changing the DATABASE of an existing connection. Can you please confirm that I also cannot change the USER of an existing connection?


    Thanks,
    Ryan

  5. #5
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Correct. You'll have to close the connection and reconnect.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by rdelaplante View Post
    My original question was about changing the USER of an existing JDBC connection. Your reply talks about changing the DATABASE of an existing connection.
    Sorry, didn't see that.

    Can you please confirm that I also cannot change the USER of an existing connection?
    Confirmed.
    My suggestion with using different schemas would still work with this approach (unless you need to assign different privileges to the users)
    Last edited by shammat; 09-15-10 at 13:58.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •