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

rphl-snts

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?

10 comments

/phoenix #ecto #deployment #postgrex #heroku #troubleshooting

0 1873 10

2022-07-30 06:36:18 UTC

Showing Posts 1 to 10

Show Best Posts
Show All (oldest first)
Show All (newest first)

evadne

See Heroku page https://help.heroku.com/63D7ALXT/why-am-i-seeing-user-does-not-have-connect-privilege-error-with-heroku-postgres-on-review-apps

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

Post #1

rphl-snts

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)

Post #2

evadne

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

Post #3

rphl-snts

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

Post #4

evadne

ecto.setup contains code to create the db with pg

Post #5

rphl-snts

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"

Post #6

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

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?

Post #7

rphl-snts

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")

Post #8

benbonnet

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 ?

Post #9