Database creation fails on popstgres if the default database does not exist

[acelaya]

The way the db:create command works, it needs to open a database-less connection, which in the case of postgress falls back to the postgres database, making the whole process fail if that database does not exist.

It is possible to change the default database to fall back to, with something like 'default_dbname' => 'template1', but there's no guarantee the template1 is going to exist either.

If there's no way to bypass this, at least, do not try to open the database-less connection if the actual database already exists.

[juergenthomann]

Hi,

I'm not sure if my problem is exactly the same problem you try to solve.

I have a manged PostgreSQL instance where the database already exists and I don't have permissions on any other database. When the Docker image starts it fails at "Creating fresh database if needed..." as it tries to connect to the postgres database which is forbidden.

Is it maybe possible to change the logic to check if a database exists? I tried 3.0.3 and also the latest Docker image and both fail with "FATAL: no pg_hba.conf entry" with database postgres.

[acelaya]

Yes, it's the same. The DB abstraction library always tries to connect to one database when the engine is postgres. If you don't specify one, it tries to connect to postgres one, assuming it's there, and you have permissions.

[Zernov-A]

Hi I have the same problem with PostgreSQL. I don't have permission for postgres db, but shlink database already created. It would be nice to have an option to ignore checkDbExists step. Maybe something like CHECK_DB_EXISTS env variable with default value true.

[Zernov-A]

I created a PR which added an environment variable that allow to skip the database existence check. It will solve this problem

[acelaya]

I have some concerns with that approach, because it's just a workaround, instead of addressing the root cause.

• It requires some not obvious previous manual steps, which can lead to new issues.
• It does not clearly state why is it needed, and in what scenario.
• There's no warranty this will solve the problem in general. It's more like "if you get one of these errors, try to set this and see how it goes".

I would prefer a more resilient approach, for example, one of my original proposals in which, if the database already exists, the whole process is skipped, which would prevent this error.

It would be easier to document in "troubleshooting".

It's still not perfect though, and I would like to spend more time investigating.

[mommys-little-hacker]

@acelaya That is a very valid point. However, before you come up with a better solution, is it possible to integrate a temporary workaround by @Zernov-A? Some cloud providers (Yandex Cloud in our case) do not allow connections to the default database, making it impossible to install Shlink. We could spin up MySQL, however a separate cluster would be an overkill, especially considering it's cost against utilizing an existing one.

[acelaya]

That rationale is just explaining the root problem we are trying to solve here, which nobody questions.

"Solving" it with that approach would just introduce more problems (the ones I explained above), so unless you can provide a very good reasoning in favor of this workaround, I still feel inclined to go on a different direction.

[acelaya]

I have found a possible solution to address this issue.

Up until now, the process to create the database worked as follows:

1. Shlink opens a connection where configured database name is not set. This allows to check if the actual database exists, avoiding an error if it does not because a connection to such database is not attempted.
2. If it does not exist, it is created empty using that same connection.
3. Now that it exists, we open a regular connection against that database and create the tables.

Steps 2 and/or 3 could be skipped if the database and/or tables already exist, making the command idempotent.

The problem with this approach is that apparently, only when using Postgres, the first step tries to connect to the postgres database as a fallback, making it fail if the user does not have permissions.

With the new approach, the process would be as follows:

1. Shlink attempts to open a connection against configured database.
2. If previous step fails, the database is created empty, using the connection where no database name has been set.
3. Now that it exists, we open a regular connection against that database and create the tables.

Step 2 would still fail with the conditions mentioned above (using Postgres and not having permissions to connect to postgres database), but there's an important difference: If you manually create the database empty beforehand, step 2 will be skipped entirely, preventing the error.

Then step 3 can create the tables if they are missing, as the only connection attempt has happened to Shlink's database.

This allows documenting this edge case, where a minimal manual step is required to create an empty database if you receive this error.

[acelaya]

I have just released v3.6.1, which includes the fix above.

I will try to document this now in the most clear possible way.