Can't connect using a non-root user

I want to establish a ‘web-safe’ user for my MySQL database (i.e. one that only has select, create, update and delete permissions).

If I drop my web-safe user from the database, I get this error which is totally expected because I have no such user in the database:

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

If I then go and create the ‘web-safe’ user in the database, even assigning the user with full grant privileges, I get a new error:

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

Say wha???

Why, now that it has the web@localhost user defined, is it attempting to connect using the root@localhost user?

My repo.ex file is the only place any connection information is established and I am definitely not attempting to use the root database user for any of my queries (i.e. override my connection settings).

If I have my app deliberately specify the root@localhost user for it’s connection information, then all works well but that totally defeats the purpose and reason for wanting to use a ‘web-safe’ database user.

Please help… I want to get this app deployed ASAP.
Thanks for your time and troubles.

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!

I cannot reproduce this, if you could create a sample project that I can run locally I’m happy to take a look.

I’ll see what I can do…

Thanks again!

I added an IO.inspect(system_opts) to the repo.ex, after the system_opts list has been set, to confirm that it is picking up what I set and this is the result (it is picking up the correct values):

PORT=4005 MIX_ENV=prod mix phx.server
[hostname: "localhost", username: "web", password: "something"]
13:57:42.968 [error] MyXQL.Connection (#PID<0.266.0>) failed to connect: ** (MyXQL.Error) (1045) (ER_ACCESS_DENIED_ERROR) Access denied for user 'root'@'localhost' (using password: YES)

If I set the system_opts to pick up the root DB user then all is well!?

Using mysql I double-checked that the ‘web’ DB user can select, create, update on a table, and he/she can.

I guess the moral of the story is don’t try to use MyXQL with any DB user other than root.

That is the wrong conclusion. MyXQL should work with any user and you should definitely not use root to login into MySQL. There is some other error here.

Start from the very beginning, it is a very good place to start.
How about testing MyXQL directly:

$ iex -S mix
iex(1)>  {:ok, pid} = MyXQL.start_link([username: "web", password: "something", database: "yourdb", hostname: "localhost"])
{:ok, #PID<0.186.0>}
iex(2)> MyXQL.query(pid, "SELECT count(*) FROM your_table")
{:ok,
 %MyXQL.Result{
   columns: ["count(*)"],
   connection_id: 30,
   last_insert_id: nil,
   num_rows: 1,
   num_warnings: 0,
   rows: [[516]]
 }}

This cuts out anything but MyXQL and tests the parameters directly. I just tested this with a new mix repo (elixir 1.9.1 and erlang 22.1) MyXQL 0.3.0 and MySQL 5.7.28.

Dependency tree of the test repo (because MyXQL depends on DBConnection))

xyxy
└── myxql ~> 0.3.0 (Hex package)
    ├── db_connection ~> 2.0 (Hex package)
    │   └── connection ~> 1.0.2 (Hex package)
    └── decimal ~> 1.6 (Hex package)
4 Likes

I don’t think that’s a fair characterisation. A lot of work went through testing myxql against different combinations of authentication methods, server defaults, ssl, and more, and all that is done through authenticating as different users.

Yes, that’s a very good suggestion. I got caught up thinking it might be configuration issue, but the issue can be something else. In v0.3.0 there’s a bug fix for mysql_native_password authentication (under certain circumstances) and that can be a culprit too.

3 Likes

I didn’t mean any ill-will towards MyXQL - sorry if you felt maligned. I certainly couldn’t have managed to do what the MyXQL team have done - thank you for making it available to the community!

I found a solution that works and allows me to use encrypted environment variables:

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")
      the_host = Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_PTRACK_DB_HOST"), secret)
      the_user = Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_PTRACK_DB_USER_NAME"), secret)
      the_pass = Pcafe.Encrypt.decrypt(System.get_env(prefix<>"_PTRACK_DB_USER_PASSWORD"), secret)

      system_opts = [
        hostname: the_host,
        username: the_user,
        password: the_pass
        # hostname: System.get_env("HOST"),
        # username: System.get_env("USER"),
        # password: System.get_env("MYSQL_PWD")
      ]

      Keyword.merge(opts, system_opts)
    end
  end

Works perfectly so something else was going on!?!?

How does your config/prod.exs look like?