Unable to start plausible using postgres user without permission to create db

[JuniorJPDJ]

I'm trying to run plausible self-hosted, but it fails to start without postgres permissions to create DB.

plausible docker log

$ dclf plausible
Attaching to plausible_plausible_1
plausible_1   | Loading plausible..
plausible_1   | Starting dependencies..
plausible_1   | Starting repos..
plausible_1   | create Plausible.Repo database if it doesn't exist
plausible_1   | ** (MatchError) no match of right hand side value: {:error, "ERROR 42501 (insufficient_privilege) permission denied to create database"}
plausible_1   |     (plausible 0.0.1) lib/plausible_release.ex:56: anonymous fn/2 in Plausible.Release.createdb/0
plausible_1   |     (elixir 1.10.3) lib/enum.ex:2111: Enum."-reduce/3-lists^foldl/2-0-"/3
plausible_1   |     (plausible 0.0.1) lib/plausible_release.ex:55: Plausible.Release.createdb/0
plausible_1   |     (stdlib 3.12.1) erl_eval.erl:680: :erl_eval.do_apply/6
plausible_1   |     (elixir 1.10.3) lib/code.ex:341: Code.eval_string_with_error_handling/3
plausible_plausible_1 exited with code 1

docker-compose fragment

  plausible:
    image: plausible/analytics:latest
    networks:
      - default
      - haproxy
      - postgres
    command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh db init-admin && /entrypoint.sh run"
    depends_on:
      - clickhouse
      - mail
    environment:
      ADMIN_USER_EMAIL: "<censored>"
      ADMIN_USER_NAME: <censored>
      ADMIN_USER_PWD: "<censored>"
      BASE_URL: "https://<censored>" 
      SECRET_KEY_BASE: "<censored>"
      DATABASE_URL: "postgres://plausible:<censored>@postgres:5432/plausible"
      CLICKHOUSE_DATABASE_URL: "http://clickhouse:8123/plausible"

postgres log:

postgres_1  | 2020-12-16 12:16:06.274 UTC [56313] ERROR:  permission denied to create database
postgres_1  | 2020-12-16 12:16:06.274 UTC [56313] STATEMENT:  CREATE DATABASE "plausible" ENCODING 'UTF8'
postgres_1  | 2020-12-16 12:20:38.089 UTC [56431] ERROR:  permission denied to create database
postgres_1  | 2020-12-16 12:20:38.089 UTC [56431] STATEMENT:  CREATE DATABASE "plausible" ENCODING 'UTF8'
postgres_1  | 2020-12-16 12:21:32.475 UTC [56455] ERROR:  permission denied to create database
postgres_1  | 2020-12-16 12:21:32.475 UTC [56455] STATEMENT:  CREATE DATABASE "plausible" ENCODING 'UTF8'

postgres dbs

postgres=# \l
                                     List of databases
       Name       |      Owner       | Encoding | Collate | Ctype |    Access privileges
------------------+------------------+----------+---------+-------+-------------------------
[..]
 plausible        | plausible        | UTF8     | C       | C     | =Tc/plausible          +
                  |                  |          |         |       | plausible=CTc/plausible
 postgres         | postgres         | UTF8     | C       | C     |
[..]
 template0        | postgres         | UTF8     | C       | C     | =c/postgres            +
                  |                  |          |         |       | postgres=CTc/postgres
 template1        | postgres         | UTF8     | C       | C     | postgres=CTc/postgres  +
                  |                  |          |         |       | =c/postgres
(11 rows)

[cirrusflyer]

Same issue for me.

[JuniorJPDJ]

Anything?

[JuniorJPDJ]

Problem is that this db already exists (shown in main post as postgres \l) and I don't want to give plausible perms to create dbs.
Yes, I'm using external postgres.

[ukutaht]

Ah sorry I missed that you've already create the plausible db.

Hmm it might be the case that Ecto ignores the connections string and always uses the postgres superuser to set up the DB in the first place. Can you try removing the DB creation from your docker command like so:

before:

    command: sh -c "sleep 10 && /entrypoint.sh db createdb && /entrypoint.sh db migrate && /entrypoint.sh db init-admin && /entrypoint.sh run"

after:

    command: sh -c "sleep 10 && /entrypoint.sh db migrate && /entrypoint.sh db init-admin && /entrypoint.sh run"

For migrations it should connect with the credentials you've provided. Let me know if this works

[JuniorJPDJ]

I don't get it..
It still thinks there's no db like this:

plausible_1   | 2021-02-16T00:00:45.829541794Z Loading plausible..                                                                                                                                                                          
plausible_1   | 2021-02-16T00:00:45.837685277Z Starting dependencies..                                                                                                                                                                      
plausible_1   | 2021-02-16T00:00:45.961532761Z Starting repos..                                                                                                                                                                             
plausible_1   | 2021-02-16T00:00:46.019884477Z Running migrations for Elixir.Plausible.Repo                                                                                                                                                 
plausible_1   | 2021-02-16T00:00:46.135338183Z 00:00:46.125 [error] Clickhousex.Protocol (#PID<0.179.0>) failed to connect: ** (ErlangError) Erlang error: "Code: 81, e.displayText() = DB::Exception: Database plausible doesn't exist (ver
sion 21.2.3.15 (official build))\n"                                                                                                                                                                                                         
plausible_1   | 2021-02-16T00:00:46.135492203Z 00:00:46.125 [error] Clickhousex.Protocol (#PID<0.178.0>) failed to connect: ** (ErlangError) Erlang error: "Code: 81, e.displayText() = DB::Exception: Database plausible doesn't exist (ver
sion 21.2.3.15 (official build))\n"                                                                                                                                                                                                         
plausible_1   | 2021-02-16T00:00:46.582533134Z Running migrations for Elixir.Plausible.ClickhouseRepo                                                                                                                                       
plausible_1   | 2021-02-16T00:00:47.882008151Z 00:00:47.881 [error] Clickhousex.Protocol (#PID<0.179.0>) failed to connect: ** (ErlangError) Erlang error: "Code: 81, e.displayText() = DB::Exception: Database plausible doesn't exist (ver
sion 21.2.3.15 (official build))\n"                                                                                                                                                                                                         
plausible_1   | 2021-02-16T00:00:48.252885917Z 00:00:48.252 [error] Clickhousex.Protocol (#PID<0.178.0>) failed to connect: ** (ErlangError) Erlang error: "Code: 81, e.displayText() = DB::Exception: Database plausible doesn't exist (version 21.2.3.15 (official build))\n"
plausible_1   | 2021-02-16T00:00:49.023796066Z 00:00:49.023 [error] Could not create schema migrations table. This error usually happens due to the following:
plausible_1   | 2021-02-16T00:00:49.023832201Z 
plausible_1   | 2021-02-16T00:00:49.023839792Z   * The database does not exist
plausible_1   | 2021-02-16T00:00:49.023844944Z   * The "schema_migrations" table, which Ecto uses for managing
plausible_1   | 2021-02-16T00:00:49.023850098Z     migrations, was defined by another library
plausible_1   | 2021-02-16T00:00:49.023855011Z   * There is a deadlock while migrating (such as using concurrent
plausible_1   | 2021-02-16T00:00:49.023859891Z     indexes with a migration_lock)
plausible_1   | 2021-02-16T00:00:49.023864602Z 
plausible_1   | 2021-02-16T00:00:49.023869031Z To fix the first issue, run "mix ecto.create".
plausible_1   | 2021-02-16T00:00:49.023873879Z 
plausible_1   | 2021-02-16T00:00:49.023878225Z To address the second, you can run "mix ecto.drop" followed by
plausible_1   | 2021-02-16T00:00:49.023883094Z "mix ecto.create". Alternatively you may configure Ecto to use
plausible_1   | 2021-02-16T00:00:49.023887951Z another table and/or repository for managing migrations:
plausible_1   | 2021-02-16T00:00:49.023892511Z 
plausible_1   | 2021-02-16T00:00:49.023896903Z     config :plausible, Plausible.ClickhouseRepo,
plausible_1   | 2021-02-16T00:00:49.023901460Z       migration_source: "some_other_table_for_schema_migrations",
plausible_1   | 2021-02-16T00:00:49.023911120Z       migration_repo: AnotherRepoForSchemaMigrations
plausible_1   | 2021-02-16T00:00:49.023915677Z 
plausible_1   | 2021-02-16T00:00:49.023920083Z The full error report is shown below.
plausible_1   | 2021-02-16T00:00:49.023924635Z 
plausible_1   | 2021-02-16T00:00:49.025476627Z ** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2430ms. This means requests are coming in and your connection pool cannot serve them fast enough. You can address this by:
plausible_1   | 2021-02-16T00:00:49.025519468Z 
plausible_1   | 2021-02-16T00:00:49.025528834Z   1. By tracking down slow queries and making sure they are running fast enough
plausible_1   | 2021-02-16T00:00:49.025534542Z   2. Increasing the pool_size (albeit it increases resource consumption)

I double checked, there's plausible db and plausible user is owner of it, so it should have permissions to it..

[ukutaht]

OK, no errors thrown from the Postgres connection but you're getting errors from Clickhouse. Makes sense since you don't manage your own Clickhouse so the DB needs to be created. Since we've abandoned Ecto db creationg, it needs to be created manually. Can you try the following:

$  docker-compose up -d plausible_events_db 
$ docker run -it --net hosting_default --rm --link hosting_plausible_events_db_1:clickhouse-server yandex/clickhouse-client --host clickhouse-server --query "CREATE DATABASE IF NOT EXISTS plausible_events_db"
$  docker-compose down

This should create the Clickhouse database manually. Then you should be able to run migrations on both databases with docker-compose up.

Sorry this is so convoluted. The setup is clearly a struggle when you want to bring your own database (and not give pg superuser access to plausible). Would be good to document this process and also to improve some of our scripting. It shouldn't be this difficult.

[JuniorJPDJ]

God! Thanks!
It worked!

It was this command in my case:

docker run -it --net plausible_default --rm yandex/clickhouse-client  --host clickhouse --query "CREATE DATABASE IF NOT EXISTS plausible"

[whysthatso]

maybe this somehow can make it into the docs? i've found this site, but didn't see the read more right away so i didn't see the solution here. was about to write a gh issue myself after wasting more time googleing :D and then i read once more in the morning :D:D

[ukutaht]

Thanks for the suggestion.

You can contribute back to the community by improving the docs yourself as well. Feel free to open a PR here: https://github.com/plausible/docs/