PostgreSQL using search_path for multi tenancy

Question

I am developing a multi-tenancy web application using PostgreSQL as database, because of its support for schemas in one database instance. But I am stuck on a problem, setting up the search_path for a tenant.

I am using JavaEE 7 with Wildfly 8.2.0. I have created an MultiTenantConnectionProvider which uses a DataSourceConnectionProvider loading the configured DataSource.

The method for retrieveing a connection set the search_path to the given tenantId:

@Override
public Connection getConnection(String tenantId) throws SQLException
{
    Connection con = getAnyConnection();
    try
    {
        con.createStatement().execute("SET search_path = '" + tenantId + "'");
        LOG.info("Using " + tenantId + " as database schema");
    }
    catch (SQLException ex)
    {
        throw new HibernateException("Could not alter connection for specific schema");
    }
    return con;
}

For first tests I am returning always the same tenantId "customer1".

On Postgres I have created one user, which has its own database and one schema "customer1". I have an entity user defined as follows:

@Entity
@Table(name = "user")
public class User implements Serializable
{
    @Id
    @GeneratedValue
    private Long id;
    @Column(unique = true, nullable = false)
    private String username;
    private String firstname;
    private String lastname;
    private String gender;
    @Column(unique = true, nullable = false)
    private String email;
    @Column(nullable = false)
    private byte[] password;
    private String passwordResetToken;
    @Column(nullable = false)
    private byte[] salt;
...
}

I have created the table in the schema "customer1". No w my problem is that the select statement for table user is returning an other user-table. I have to explicitly set the schema name with the table, otherwise I query the wrong table.

The statement:

select * from user; -> current_user name: user1

returns:

| current_user name |
--------------------
| "skedflex"        |

The statement:

select * from customer1.user;

returns:

| id | username | firstname | lastname | ... |
----------------------------------------------
| 1  | johnnie  | John      | Doe      | ... |

It would be no option to use the schema name in the query, cause that value is determined during runtime and I am using JPA. So there is no way to insert the schema name during runtime query execution.

I have expected, that the search_path is sufficient for querying data.

http://www.postgresql.org/docs/9.4/interactive/sql-alteruser.html You can set the search_path *per user* — wildplasser, Jul 19 '15 at 23:22
Can't you hook the connection pool so it invokes a callback when acquiring a connection? Also, you've got logging in place: does the logging show your SET is actually run? If you enable `log_statement = 'all'` in `postgresql.conf` do you see the expected statement run? Try setting a `log_line_prefix` in `postgresql.conf`…. — Craig Ringer, Jul 20 '15 at 00:08
I have tested this alos with pgAdmin 3, first running the command `set search_path = 'customer1';` and then trying to select the user with id = 1: `select * from user where id = 1;` but it gives me the wrong result mentioned in my question. — Georg Leber, Jul 20 '15 at 08:03

score 0 · Accepted Answer · answered Jul 20 '15 at 18:44

0

I have found the problem with my setup. In PostgreSQL user is a keyword and tables with this name cannot be created, except user is escaped with quotes: "user". I have changed the table name to user_account and the queries work as expected.

answered Jul 20 '15 at 18:44

Georg Leber

3,470
5
40
63

PostgreSQL using search_path for multi tenancy

1 Answers1