Postgres storage plugin defaults to the wrong DB on postgres>=12

[vongohren]

After a conversation here: https://chat.hyperledger.org/channel/aries-cloudagent-python?msg=JgzXEfAFPQEAc6CKJ

This issue is created. Following are some screenshots and the explanation

Through the aca-py, and following the docs, it seems to be an issue with the indy SDK connecting to the DB when postgres version is atleast equal or bigger than 12.

Config used

aca-py start \
-it http '0.0.0.0' 10000 \
-ot http \
--admin '0.0.0.0' 5000 \
-e 'http://localhost:10000/' \
--auto-provision \
--wallet-storage-type postgres_storage \
--wallet-storage-config '{\"url\":\"host.docker.internal:5432\"}' \
--wallet-storage-creds '{\"account\":\"local-cloud-agent\",\"password\":\"testtest\",\"admin_account\":\"admin-cloud-agent\",\"admin_password\":\"testtest\"}' \
--wallet-type indy \
--wallet-name diwala2 \
--wallet-key Diwala.Agent421311 \
--seed diwala9jphVquhjphuVIerbf0XEc74WL \
--genesis-url http://test.bcovrin.vonx.io/genesis \
--label 'Diwala Agent 3' \
--admin-insecure-mode \
--multitenant \
--multitenant-admin \
--jwt-secret very_secret_secret \
--log-level info",

Based on the screenshots, it seems to be connecting and trying to make a DB with the name of the admin account.

Which leads to this strange workaround I found which is unclear to me why.

This SDK builds this connection string: postgresql://admin-cloud-agent:testtest@localhost:5432 (ignore the localhost, assume it works)

This string was tested in a minimal connection scenario, and what it complains about is that it is trying to connect to the db 'admin-cloud-agent', which is strange, since that is the user. I just wanted to connect and create a DB. Which this SDK do try to do when using the postgres plugin. But that was not possible until i acctually created that DB by hand, then it could connect and go and create the DB it wanted.

So if I just have a DB with that user name in postgres, it will continue and do its deed and work just fine.

How come? Is it because I run postgresql 12? That some default handling has happened since then and it is not catered for in the code?

Comment from a developer: "I think there may have been some changes in postgres in terms of how it handles the default database for the user Between postgres 9 and 10 maybe? I don't recall exactly offhand ..."

[toshirin33]

I met the same problem recently. The reason is that the connection string does not have the database. As shown in https://www.postgresql.org/docs/current/libpq-connect.html 33.1.2, since database name is not given, the database name is the same as the user name.

The connection string is created in the following: https://github.com/hyperledger/indy-sdk/blob/8c669cff434f5d87e7a2e2e192423c67a9df0b33/experimental/plugins/postgres_storage/src/postgres_storage.rs#L1031-L1046

I have just made a pull request to fix this problem: #2366. I just use 'postgres' as the connected database; which exists as in https://www.postgresql.org/docs/current/app-initdb.html .

[vongohren]

@toshirin33 is this something coming up with newer versions of postgres, or why is it happening now? And not on older versions?

[toshirin33]

@vongohren I have not tried with the older versions. According to the older versions' documents, for example https://www.postgresql.org/docs/9.6/libpq-connect.html , I think the same problem occurs even using the older versions.

Have you tried with an older version? Didn't that happen?

[vongohren]

@toshirin33 well not really, that is just an assumption. That I guess someone would have detected it using some older versions :P

Because this was such a strange bug to go undetected I guessed it was some versioning thing

[ianco]

This bug doesn't happen on older postgres versions, I think it was introduced in 10 or 11 (I don't recall exactly offhand)