Is Trust Authentication possible with Ecto?

Questions / Help
1

Using psql postgres:///db, I can successfully connect to my database. Can I use postgres:///db as DATABASE_URL? I get this error when I do:

invalid url postgres:///db, host is not present.

Thank you in advance!

2

Can you show how exactly are you passing the environment variable? As a precaution you should always surround the value with single quotes like so:

DATABASE_URL='postgres:///some_user:a_password@myhost:5678' mix phx.server
3

That given URL is not parsed as you expect:

iex(1)> URI.parse("postgres:///db")
%URI{
  authority: nil,
  fragment: nil,
  host: nil,
  path: "/db",
  port: nil,
  query: nil,
  scheme: "postgres",
  userinfo: nil
}

You probably want to use only 2 slashes:

iex(2)> URI.parse("postgres://db")
%URI{
  authority: "db",
  fragment: nil,
  host: "db",
  path: nil,
  port: nil,
  query: nil,
  scheme: "postgres",
  userinfo: nil
}

Also please read using URLs with ecto in the docs. The URLs schema does not cary any information of value for ecto, it will be ignored, you still have to configure postgres as the backend by other means.

4

It is. db should be a path, not a host.

@jpbecotte have you tried postgres://localhost/db?

5

I tried with and without, and it didn’t worked. I think Ecto cannot manage Trust Authentification. From the doc:

When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). Of course, restrictions made in the database and user columns still apply. This method should only be used when there is adequate operating-system-level protection on connections to the server.

6

@NobbZ Thanks for your answer. No, two slashes won’t work. We should be able to connect without using a password if no host, no password and no username is present in the schema. If Postgres allows it, why Ecto won’t?

When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). Of course, restrictions made in the database and user columns still apply. This method should only be used when there is adequate operating-system-level protection on connections to the server.

7

Yeah, @hauleth already told me how incorrect I was.

Still, you need to provide a hostname to connect to, and also a user at least. Once is that ecto knows where to connect to, and one that postgres knows as who to connect.

The psql CLI tool will use $USER if no extra name is provided.

8

Hi @hauleth! Using localhost breaks the Trust Auth method. I tried it and it doesn’t work. postgres:///db is totally acceptable for Postgres, and Postgres understands that whatever follows three slashes is the name of the DB (In my case, db).

9

Hi @Nobbz. If no user, no password and no host is provided, Trust Authentification is used. It means Postgres will authenticated according to the currently connected user.

So why Trust Authentification cannot be used with Ecto?

10

Let’s backtrack. What happens when you pass your URL to psql?

11

You quoted the docs (emphasis mine):

When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names)

I clearly read this as, “the user still needs to tell who he pretends to be”.

There is no default user that the system will assume.

There is only the default user that psql uses when no user is explicitely specified.

1 Like
12

Hello @dimitarvp ! As I said, when I use the terminal and I type psql postgres:///db , it works. I mean, it connects to my DB (which is db), and I can query, show tables definition, etc.

13

I don’t see why that’s relevant. If Postgres accepts postgres:///db as a valid URL, so Ecto should.

14

psql is just a client that provides some default values, which ecto doesn’t.

Just provide a host and a user and it should work.

If you think ecto should behave like postgres, open a ticket, but be aware that someone else will ask for mysql behaviour, which tries to use the socket-file at default location even when localhost or 127.0.0.1 as host is specified…

1 Like
15

Sorry I’m awfully misreading today. My bad!

16

Is this a psql feature or a postgres feature?

17

Hello! This is a Postgres feature. I mean, the server accepts those URL as a valid connection URL. I have some Node.js apps that use knext as query builder and this URL scheme works with those apps.

I understand many find weird not using a username and a password. But for some use cases, this is ideal.

18

Looks like a bug. https://github.com/elixir-ecto/ecto/issues/3212 ! Wow. They’ve been very reactive!

1 Like