** (Postgrex.Error) FATAL 42501 (insufficient_privilege) permission denied for database "postgres"

Questions / Help
, , , , ,
1

Hi folks, hope you are all doing well.
I’m having trouble to deploy my app on heroku using two databases.

[error] GenServer #PID<0.268.0> terminating
** (Postgrex.Error) FATAL 42501 (insufficient_privilege) permission denied for database "postgres"

User does not have CONNECT privilege.
    (db_connection 2.4.2) lib/db_connection/connection.ex:100: DBConnection.Connection.connect/2
    (connection 1.1.0) lib/connection.ex:622: Connection.enter_connect/5
    (stdlib 3.14.2) proc_lib.erl:226: :proc_lib.init_p_do_apply/3
Last message: nil
State: Postgrex.Protocol

My prod.exs:

import Config

database_url =
  System.get_env("DATABASE_URL") ||
    raise """
    environment variable DATABASE_URL is missing.
    For example: ecto://USER:PASS@HOST/DATABASE
    """

database_url_2 =
  System.get_env("HEROKU_POSTGRESQL_JADE_URL") ||
    raise """
    environment variable DATABASE_URL is missing.
    For example: ecto://USER:PASS@HOST/DATABASE
    """

config :multi_repo, MultiRepo.Repo,
  ssl: true,
  url: database_url,
  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10")

config :multi_repo, MultiRepo.Repo2,
  ssl: true,
  url: database_url_2,
  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10")

secret_key_base =
  System.get_env("SECRET_KEY_BASE") ||
    raise """
    environment variable SECRET_KEY_BASE is missing.
    You can generate one by calling: mix phx.gen.secret
    """

config :multi_repo, MultiRepoWeb.Endpoint,
  http: [
    port: String.to_integer(System.get_env("PORT") || "4000"),
    transport_options: [socket_opts: [:inet6]]
  ],
  secret_key_base: secret_key_base

It works when I use only one database and locally both works.
I created a NodeJs app that connects to two databases on Heroku and it worked fine, so I believe there may be something wrong with Postgrex.
Has anyone had the same error?

2

See Heroku page Why am I seeing "User does not have CONNECT privilege" error with Heroku Postgres on Review Apps? - Heroku Help

Would need more info as to WHEN you are getting the error

3

I had consulted this page but unfortunately I didn’t find a solution.
The error happens when I try to create the database, that is, when I run the command mix ecto.setup.

(I use elixir release, so I created a task for creating/migrating the database)

4

Well there you go, do not create the DB, it is already created by Heroku when you added that add-on, only run migrations

In the page:

We typically see CONNECT privilege issues where applications try to create database

5

Yes, I know that. I didn’t mean literally create the DB

6

ecto.setup contains code to create the db with pg

7

Yes, you’re right about that, but I use Elixir Release on Heroku.
I just mentioned the mix ecto.setup to try to make it understand that the error is related to the database setup, I apologize if I didn’t make myself understood.
And I run my migrations in production with the command: _build/prod/rel/my_app/bin/my_app eval "MyApp.Release.migrate"

8

If you are using releases, the prod.exs is evaluated when the release is built not when the release is executed. Are you setting those environment variables in the build environment or in the environment when the release is being run?

9

In the build environment.
I logged the opts paramenters from DBConnection.Connection.init/1
(I just remove the credentials to post here)

[
  pool_index: 1,
  types: Postgrex.DefaultTypes,
  max_restarts: 0,
  backoff_type: :stop,
  database: "postgres",
  force_drop: false,
  telemetry_prefix: [:multi_repo, :repo],
  otp_app: :multi_repo,
  timeout: 15000,
  ssl: true,
  hostname: "ec2-...",
  username: "adew...",
  password: "9dd5...",
  port: 5432
]

looks like the database is being replaced by “postgres”, the other options are correct like password, hostname and username.
The database name only appears when I use the :maintenance_database option.
Exmaple:

config :multi_repo, MultiRepo.Repo,
  ssl: true,
  url: database_url,
  maintenance_database: database_name,
  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "10")
10

coming from rails, the following is relatively common within the Procfile :

web: ...run the server...
release: ...run the migrations on each deployment...

If prod.exs is evaluated when the release is built not when the release is executed, what’s the best place to run mix ecto.migrate for each deployment ?

11

Mix is not available in releases, one strategy for handling migrations is listed here:

https://hexdocs.pm/ecto_sql/Ecto.Migrator.html#module-example-running-migrations-in-a-release

2 Likes