Aggregation Store - Sample DB config for production environment

[thaingo]

I've already had below configuration for Aggregation Store:

{
  dbconfigs: [
    {
      name: productionConnection
      url: jdbc:postgresql://hostname:5432/abc
      driver: org.postgresql.Driver
      user: postgres
      dialect: Postgres
      propertyMap:
            {
              encrypted.password: encodePassword
            }
    }
  ]
}

Also respective bean defined:

@Bean
    public DBPasswordExtractor getDBPasswordExtractor() {
        return config -> {
            String encrypted = (String) config.getPropertyMap().get("encrypted.password");
            byte[] decrypted = Base64.getDecoder().decode(encrypted.getBytes());
            return new String(decrypted, StandardCharsets.UTF_8);
        };
    }

But Postgres keeps throwing this error:

08-12-2021 23:37:59.358 [main] INFO  com.zaxxer.hikari.HikariDataSource.getConnection - HikariPool-1 - Starting...
08-12-2021 23:38:00.436 [main] ERROR com.zaxxer.hikari.pool.HikariPool.throwPoolInitializationException - HikariPool-1 - Exception during pool initialization.
org.postgresql.util.PSQLException: The server requested SCRAM-based authentication, but no password was provided.
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:770)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:163)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:215)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:51)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:225)
	at org.postgresql.Driver.makeConnection(Driver.java:466)
	at org.postgresql.Driver.connect(Driver.java:265)
	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
	at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
	at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
	at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
	at liquibase.integration.spring.SpringLiquibase.afterPropertiesSet(SpringLiquibase.java:266)
	a

Could you please advice?

[aklish]

Is the password present in the property map or is it missing?

[thaingo]

Its there, in the encoded format as I indicated in the question

[thaingo]

I fixed the issue by using plain text password in the bean.

FYI: while debugging, I found that encrypted is null, hence NPE and could not create QueryEngine bean respectively.

[thaingo]

How about other setting properties for DB connection pool like max connections, idle connections, etc.? Should we specify them in the propertyMap?

[aklish]

By default, this is what happens to those properties:

https://github.com/yahoo/elide/blob/master/elide-datastore/elide-datastore-aggregation/src/main/java/com/yahoo/elide/datastores/aggregation/queryengines/sql/DataSourceConfiguration.java#L32

[thaingo]

Does the Aggregation Store leverage db connection pool of JPA store? I already looked into source code but still not so sure.

Also, is it recommended to have a separate connection pool for Aggregation Store?

[aklish]

By default, if you don't define any data sources in HJSON, Elide will use the same data source as your JPA data source (and hence the same connection pool). You can configure separate data sources for JPA and analytic queries though if you want. Check out the QueryEngine bean setup:

https://github.com/yahoo/elide/blob/master/elide-spring/elide-spring-boot-autoconfigure/src/main/java/com/yahoo/elide/spring/config/ElideAutoConfiguration.java#L293-L319