PostgreSQL schema not working

[Ryszard-Trojnacki]

Expected behavior

Setting schema schould make effect on database connection:

databaseConfig.setDbSchema("test1");

https://ebean.io/docs/database/postgres/#schema

Actual behavior

Setting databaseConfig.setDbSchema("test1") does nothing, schema is public.

Comment

I have checked source code and is seems, that Ebean is setting property schema but Postgresql uses currentSchema.
Setting ?currentSchema=test in URL solves this problem.

Source code from PostgreSQL JDBC driver ConnectionFactoryImpl.java:

  private List<String[]> getParametersForStartup(String user, String database, Properties info) {
....
    String currentSchema = PGProperty.CURRENT_SCHEMA.get(info);
    if (currentSchema != null) {
      paramList.add(new String[]{"search_path", currentSchema});
    }
....
}

And PGProperty.java:

  CURRENT_SCHEMA(
      "currentSchema",
      null,
      "Specify the schema (or several schema separated by commas) to be set in the search-path"),

I think, that ConnectionPool.java should be changed from:

    final String schema = params.getSchema();
    if (schema != null) {
      this.connectionProps.setProperty("schema", schema);
    }

to:

    final String schema = params.getSchema();
    if (schema != null) {
      this.connectionProps.setProperty("currentSchema", schema);
    }

[jnehlmeier]

Shouldn't ConnectionPool be JDBC driver independent? Seems like configuring the schema is different for different JDBC drivers / databases, so it should be configured elsewhere. For example in Oracle it seems you need to execute an alter session set current_schema=myschema query to switch schema.

[Ryszard-Trojnacki]

Not sure.
According to Ebean documentation it is only for Postgres https://ebean.io/docs/database/postgres/#schema and I was using this function base on code example. It didn't work. Therefore is this issue.

[rbygrave]

Setting schema should make effect on database connection:

Ah, no that is not the intention here.

Sorry, this is confusing and dealing with a very Postgres specific "convention" / "best practice" ... which is TLDR

• Do [NOT] use public schema [Edit: added in the NOT here]
• Instead use a schema that exactly matches the database user name
• ... and then we use the schema without specifying it, without explicitly using currentSchema, and without modifying the search path.

That is, the motivation is more specifically with Postgres having a default public schema and that "best practice" suggests that we should actually prefer to use another schema to put our objects in AND ... that schema name should match the database username because then ...

• our tables, views, functions etc go into a schema that isn't public
• we don't state the schema via @Table, we don't modify the currentSchema or modify the search path

> ... there is no need to use currentSchema or modify the search path

Said another way:

• We are using Postgres
• We desire not to just use the public schema
• We instead desire to use a named schema that exactly matches our database user name

Under these conditions, we do NOT specify currentSchema, we do NOT modify the search path and we do NOT explicitly specify the schema in @Table.

... but the objects (tables, views etc) are put under that schema and NOT the public schema. [edit: Hence all things that run DDL are interested in this flag/value]

Let's say our Postgres database username is my_app, we want a schema called my_app and our tables and objects go in that schema AND we do not need to use currentSchema OR modify the search path OR specify the schema in @Table.

Said another way, this is a Postgres specific convention, that if followed means that the objects do not go into the public schema.

---

If we want to use a schema explicitly ... like @Table(name="myschema.customer") ... that's different. It's explicitly use of schema.

Dang, I've probably not explained this well. Did any of the above help clarify?

[rbygrave]

It could almost be a boolean flag like boolean useDatabaseUsernameAsDefaultSchema; that only applies to Postgres ... so could even be boolean postgresUseUsernameAsDefaultSchema;.

When this is set to true then (for Postgres only) make sure that the objects (tables, views, functions) go into a database schema that is the same name as the database username / role [and not the public schema].

In order to make it a boolean, ebean would need to extract the database username when 1. running db migrations and 2. when the DdlRunner runs during testing. With this "kind of magic schema" / postgres specific convention the schema name must always match the database username / role.

Implementation wise, instead of using a boolean and extracting the username, we've got a really poorly named databaseConfig.setDbSchema("..."); ...

[rbygrave]

Ok, I'm going to move this into Discussions - Backlog.

The task that should be done at some point is:

• Rename this from dbSchema -> defaultSchema
• Make this a DDL generation only feature. That is, when defined any table that doesn't have an explicit schema gets the defaultSchema assigned to it. This would be done when generating DDL for migrations and generating DDL for create-all/drop-all

I'll move this to Discussions - Backlog as a kind of test to see how that workflow goes.
@Ryszard-Trojnacki