How can I configure ecto to use an unprivileged user to connect to postgresql ?

Phoenix Forum Questions / Help
,
1

According to page https://hexdocs.pm/ecto/getting-started.html:

NOTE: Your PostgreSQL database may be setup to

  • not require a username and password. If the above configuration doesn’t work, try removing the username and password fields, or setting them both to “postgres”.
  • be running on a non-standard port. The default port is 5432. You can specify your specific port by adding it to the config: e.g. port: 15432.

From my understanding of this part of the documentation, it seems impossible to have an user without ‘create db, create role, superuser’ privileges.

Coming from a security background, I’m confused, from my pov, it’s a huge problem, a simple user must never be able to create or drop database, or even create roles…

What are the best practices regarding database users definitions in phoenix/ecto ?

I do understand that ‘ecto.create’ needs the right to create database, but I do not understand where can I configure another role with no special privileges that will insert or update content in the database…

Is it possible ?

Do I need to put an account in config/config.ex with full privileges and an other in config/prod.ex and config/dev.ex ?

Thanks for your help :slight_smile:

2

In a development environment, i would be hugely inconvenient to be without ‘create db’ privilege. This is what the getting started guide covers. In a production environment, you are not going to run mix ecto.create so you don’t need it.

2 Likes
3

The short answer is: yes it’s possible. The little longer answer is that it’s possible but you’re going to get away from what the majority of tutorial level information is available and have to have some understanding of the toolkit.

I’m going to try to give some pointers to get you going in the right direction. Unfortunately, it’s going to be limited as I’m pretty ill right now and don’t have the stamina to get as in-depth as I should; but I don’t want to leave this question waiting for too long either.

First, you could set up multiple repositories: one for privileged access such as running migrations and the like, and another for regular unprivileged access used by the application. This can be done with multiple repo modules/configs such as:

defmodule PrivilgedRepo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres
end

defmodule AppRepo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.Postgres
end

And a config/*.ex something like:

config :my_app, PrivilegedRepo,
  database: "ecto_simple",
  username: "owner",
  password: "owner",
  hostname: "localhost"

config :my_app, AppRepo,
  database: "ecto_simple",
  username: "application",
  password: "application",
  hostname: "localhost"

And then use the appropriate repository in the appropriate context.

In my application, the application does something similar except that I don’t use config/*.ex for anything Ecto related. I have a privileged database role (CREATEDB as well as group member with admin option for some special groups, but can only create new roles using special SECURITY DEFINER database functions which create specific low privilege roles I need to create at runtime). Then I have (very) low privileged roles with which the Elixir application/Ecto connects for business logic interactions.

In the cases above, I’m starting the Ecto Repos in my code more explicitly by calling Ecto.Repo.start_link/1 (Ecto.Repo — Ecto v3.10.3). The privileged role is transient (generic sense) in that I start my Privileged Repo just before using it and shut it down as soon as whatever task I was using it for is finished. Other low privileged roles are continuously connected, but even then I’m starting them up manually using Ecto.Repo.start_link/1, in part because at compile time I don’t know what low privileged connections are actually going to exist.

I’d suggest reading the Ecto Dynamic Repositories documentation even if you aren’t needing to use dynamic repos… the knowledge for getting dynamic repos running is very helpful in getting a handle on the moving parts of how Ecto connects to repos and can provide insights into the problem you’re describing (Replicas and dynamic repositories — Ecto v3.10.3).

Anyway, hope this helps a bit.

2 Likes