Can't connect using a non-root user

Show us your config files.

config.exs

# This file is responsible for configuring your application
# and its dependencies with the aid of the Mix.Config module.
#
# This configuration file is loaded before any dependency and
# is restricted to this project.

# General application configuration
use Mix.Config

config :pcafe,
  ecto_repos: [Pcafe.Repo]

# Configures the endpoint
config :pcafe, PcafeWeb.Endpoint,
  url: [host: "localhost"],
  secret_key_base: System.get_env("SECRET_KEY_BASE"),
  render_errors: [view: PcafeWeb.ErrorView, accepts: ~w(html json)],
  pubsub: [name: Pcafe.PubSub, adapter: Phoenix.PubSub.PG2]

# Configures Elixir's Logger
config :logger, :console,
  format: "$time $metadata[$level] $message\n",
  metadata: [:request_id]

# Use Jason for JSON parsing in Phoenix
config :phoenix, :json_library, Jason

# Import environment specific config. This must remain at the bottom
# of this file so it overrides the configuration defined above.
import_config "#{Mix.env()}.exs"

dev.exs

use Mix.Config

the_port = System.get_env("PORT")

# For development, we disable any cache and enable
# debugging and code reloading.
#
# The watchers configuration can be used to run external
# watchers to your application. For example, we use it
# with webpack to recompile .js and .css sources.
config :pcafe, PcafeWeb.Endpoint,
  http: [port: the_port || 4005], # Default Port if none provided
  debug_errors: true,
  code_reloader: true,
  check_origin: false,
  watchers: [
    node: [
      "node_modules/webpack/bin/webpack.js",
      "--mode",
      "development",
      "--watch-stdin",
      cd: Path.expand("../assets", __DIR__)
    ]
  ]

# ## SSL Support
#
# In order to use HTTPS in development, a self-signed
# certificate can be generated by running the following
# Mix task:
#
#     mix phx.gen.cert
#
# Note that this task requires Erlang/OTP 20 or later.
# Run `mix help phx.gen.cert` for more information.
#
# The `http:` config above can be replaced with:
#
#     https: [
#       port: 4001,
#       cipher_suite: :strong,
#       keyfile: "priv/cert/selfsigned_key.pem",
#       certfile: "priv/cert/selfsigned.pem"
#     ],
#
# If desired, both `http:` and `https:` keys can be
# configured to run both http and https servers on
# different ports.

# Watch static and templates for browser reloading.
config :pcafe, PcafeWeb.Endpoint,
  live_reload: [
    patterns: [
      ~r{priv/static/.*(js|css|png|jpeg|jpg|gif|svg)$},
      ~r{priv/gettext/.*(po)$},
      ~r{lib/pcafe_web/views/.*(ex)$},
      ~r{lib/pcafe_web/templates/.*(eex)$}
    ]
  ]

# Do not include metadata nor timestamps in development logs
config :logger, :console, format: "[$level] $message\n"

# Set a higher stacktrace during development. Avoid configuring such
# in production as building large stacktraces may be expensive.
config :phoenix, :stacktrace_depth, 20

# Initialize plugs at runtime for faster development compilation
config :phoenix, :plug_init_mode, :runtime

# Configure your database
config :pcafe, Pcafe.Repo,
  adapter: Ecto.Adapters.MyXQL,
  username: nil,
  password: nil,
  database: "phoenix",
  hostname: nil,
  pool_size: 10,
  log: :debug

prod.exs

use Mix.Config

# For production, don't forget to configure the url host
# to something meaningful, Phoenix uses this information
# when generating URLs.
#
# Note we also include the path to a cache manifest
# containing the digested version of static files. This
# manifest is generated by the `mix phx.digest` task,
# which you should run after static files are built and
# before starting your production server.
config :pcafe, PcafeWeb.Endpoint,
  http: [:inet6, port: System.get_env("PORT") || 4005],
  url: [scheme: "http", host: "192.168.8.27", port: 80],
  cache_static_manifest: "priv/static/cache_manifest.json",
  secret_key_base: Map.fetch!(System.get_env(), "SECRET_KEY_BASE")

# Do not print debug messages in production
config :logger, level: :info

# ## SSL Support
#
# To get SSL working, you will need to add the `https` key
# to the previous section and set your `:url` port to 443:
#
#     config :pcafe, PcafeWeb.Endpoint,
#       ...
#       url: [host: "example.com", port: 443],
#       https: [
#         :inet6,
#         port: 443,
#         cipher_suite: :strong,
#         keyfile: System.get_env("SOME_APP_SSL_KEY_PATH"),
#         certfile: System.get_env("SOME_APP_SSL_CERT_PATH")
#       ]
#
# The `cipher_suite` is set to `:strong` to support only the
# latest and more secure SSL ciphers. This means old browsers
# and clients may not be supported. You can set it to
# `:compatible` for wider support.
#
# `:keyfile` and `:certfile` expect an absolute path to the key
# and cert in disk or a relative path inside priv, for example
# "priv/ssl/server.key". For all supported SSL configuration
# options, see https://hexdocs.pm/plug/Plug.SSL.html#configure/1
#
# We also recommend setting `force_ssl` in your endpoint, ensuring
# no data is ever sent via http, always redirecting to https:
#
#     config :pcafe, PcafeWeb.Endpoint,
#       force_ssl: [hsts: true]
#
# Check `Plug.SSL` for all available options in `force_ssl`.

# ## Using releases (distillery)
#
# If you are doing OTP releases, you need to instruct Phoenix
# to start the server for all endpoints:
#
#     config :phoenix, :serve_endpoints, true
#
# Alternatively, you can configure exactly which server to
# start per endpoint:
#
config :pcafe, PcafeWeb.Endpoint, server: true
#
# Note you can't rely on `System.get_env/1` when using releases.
# See the releases documentation accordingly.

# Finally import the config/prod.secret.exs which should be versioned
# separately.
import_config "prod.secret.exs"

repo.ex

defmodule Pcafe.Repo do
  use Ecto.Repo,
    otp_app: :pcafe,
    adapter: Ecto.Adapters.MyXQL

    @doc """
    Dynamically loads the repository url from the
    DATABASE_URL environment variable.
    """
    # def init(_, opts) do
    #   {:ok, Keyword.put(opts, :url, System.get_env("DATABASE_URL"))}
    # end
    def init(_, opts) do
      {:ok, build_opts(opts)}
    end

    defp build_opts(opts) do
      the_port = System.get_env("PORT") # evaluates to nil during development

      prefix =
        case the_port do
          "4005" -> "LOCAL" #"FOREIGN"
          nil -> "LOCAL" #Sensible default
          _ -> "LOCAL"
        end

      secret = System.get_env("DB_SECRET")

      system_opts = [
        hostname: Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_DB_HOST"), secret),
        username: Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_DB_USER_NAME"), secret),
        password: Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_DB_USER_PASSWORD"), secret)
      ]

      Keyword.merge(opts, system_opts)
    end
  end

And yes, the encryption routines work fine - I use them all the time and they especially work for this app when I am running the app on my source machine.

MyXQL defaults :username to System.get_env("USER"), but you are explicitly passing :username option so it’s unclear to me what’s going on. What is the system user you’re starting elixir process with? If you could reproduce it in a minimal project please open up an issue in myxql repo and I’ll take a look.

I noticed that same thing the other day so I went through the trouble of setting up the same environment variables MyXQL is looking for in their source code (HOST, USER and MYSQL_PWD) , but I thought they were daft names and wanted to use my own.

I’ll go back to try using their environment variable names and see if that fixes my problem - I’ll let you know the results.

No luck. I’m now using the environment variables MyXQL is expecting and I still get this error:

12:09:11.664 [error] MyXQL.Connection (#PID<0.405.0>) failed to connect: ** (MyXQL.Error) (1045) (ER_ACCESS_DENIED_ERROR) Access denied for user 'root'@'localhost' (using password: YES)

Note you don’t need to set the environment variables if you are setting the repo configuration. Could you paste the output (without secrets) of:

iex> MyApp.Repo.config()

? Also please paste your myxql, ecto, and ecto_sql versions, and make you sure you tried on the latest myxql release (although I don’t think we ever had such bug)

Well, that didn’t go well.
First, I had repo.ex assign the user and password from the environment variables “USER” and “MYSQL_PWD” and when running the app, I got a bevy of errors.

I then set the show_sensitive_data_on_connection_error flag to true to see what it was really complaining about. It report that :username is missing.

So then, in the repo.ex file, I directly assigned the username and password (without using environment variables) and now we are back to this error:

12:25:54.315 [error] MyXQL.Connection (#PID<0.280.0>) failed to connect: ** (MyXQL.Error) (1045) (ER_ACCESS_DENIED_ERROR) Access denied for user 'root'@'localhost' (using password: YES)

(and I am not using ‘root’ as the username!!)

I tried to give you the results of iex> MyApp.Repo.config() but iex complains that module Ecto.Repo is not loaded and can't be found so I gave up.

myxql version is 0.2.10
ecto version is 3.1.4
ecto_sql version is 3.1.2

BTW: I appreciate your time and patience!

username: Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_DB_USER_NAME"), secret),

if this evaluates to nil, which is my guess, then you’d get the :username is missing error. If that’s the case the error message is arguably a little bit misleading, the username was set it’s just it’s set to nil which is not allowed.

If you set :username in the config file, but you still have the code mentioned above, the code takes precedence.

Could you hardcode username in the repo.ex and see if that works?

That’s exactly what I did (set it in the repo.ex file) and it still wants to connect to the root db user:

defmodule Pcafe.Repo do
  use Ecto.Repo,
    otp_app: :pcafe,
    adapter: Ecto.Adapters.MyXQL

    @doc """
    Dynamically loads the repository url from the
    DATABASE_URL environment variable.
    """
    # def init(_, opts) do
    #   {:ok, Keyword.put(opts, :url, System.get_env("DATABASE_URL"))}
    # end
    def init(_, opts) do
      {:ok, build_opts(opts)}
    end

    defp build_opts(opts) do
      the_port = System.get_env("PORT") # evaluates to nil during development

      prefix =
        case the_port do
          "4005" -> "LOCAL" #"FOREIGN"
          nil -> "LOCAL" #Sensible default
          _ -> "LOCAL"
        end

      secret = System.get_env("DB_SECRET")

      system_opts = [
        #hostname: Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_DB_HOST"), secret),
        # username: Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_DB_USER_NAME"), secret),
        # password: Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_DB_USER_PASSWORD"), secret)
        #hostname: System.get_env("HOST"),
        #username: System.get_env("USER"),
        #password: System.get_env("MYSQL_PWD")
        username: "web",
        password: "something"
      ]

      Keyword.merge(opts, system_opts)
    end
  end

But you bring up a good point - I should add some error trapping in there to ensure that I’m not trying to use nil values!