sea-orm-cli does not seem to support UNIX socket connections for PostgreSQL properly

[maikelwever]

Description

When using PostgreSQL over a local UNIX socket, using peer authentication, sea-orm-cli refuses to connect to the database.

Entering postgres:///database_name, should connect over the default unix socket, using local user authentication, which does not require a username, password, or hostname in the URL. Not even the database name is required, since the username is used as the default database name.

Using sea_orm::Database::connect(db_url) this does work as expected.

Steps to Reproduce

1. Ensure UNIX socket and Peer authentication on the socket is enabled in PostgreSQL (default on for example Debian), the local user exists in PostgreSQL, and a database exists.
2. Then run sea-orm-cli generate entity -o /tmp/ --database-url postgresql:///database_name

Expected Behavior

sea-orm-cli connects on socket /var/run/postgresql/.s.PGSQL.5432, using peer authentication, and generates entity code for database database_name.

Actual Behavior

sea-orm-cli panics: thread 'main' panicked at 'No username was found in the database url', /home/maikel/.cargo/registry/src/github.com-1ecc6299db9ec823/sea-orm-cli-0.6.0/src/main.rs:62:21

Reproduces How Often

Always reproducible when using the URL given in this issue.

Versions

sea-orm-cli v0.6.0 installed globally using cargo install.
Debian GNU/Linux 11 (Bullseye), PostgreSQL 13.

Additional Information

Sort-of works after removing the if block at https://github.com/SeaQL/sea-orm/blob/master/sea-orm-cli/src/main.rs#L59 . Still fails to accept empty database URL as well.

Because of this issue, several tests in sea-orm-cli also are expected to fail, while the URLs that are tested are sort-of valid (eg: test_generate_entity_no_host, test_generate_entity_no_database_section).

postgresql://root:root@localhost:3306 should attempt to connect to database root.
postgres://root:root@/database should attempt to connect to socket /var/run/postgresql/.s.PGSQL.5432

The psql command line tool for PostgreSQL supports URLs as the first parameter, and thus can be used to easily test if a given URL is valid.

[billy1624]

Hey @maikelwever, sorry for the delay. Could you please try this?

• PostgreSQL Unix Domain Sockets with DATABASE_URL launchbadge/sqlx#1202 (comment)

[maikelwever]

Hey @maikelwever, sorry for the delay. Could you please try this?

* [PostgreSQL Unix Domain Sockets with DATABASE_URL launchbadge/sqlx#1202 (comment)](https://github.com/launchbadge/sqlx/issues/1202#issuecomment-830728943)

It works fine in SQLx or when using sea_orm::Database::connect(db_url), my issue is specifically about sea-orm-cli, which explicitly cheks for username & hostname outside of DATABASE_URL parsing, here: https://github.com/SeaQL/sea-orm/blob/master/sea-orm-cli/src/main.rs#L61 , which is resulting in invalid behavior.

[billy1624]

So, /var/run/postgresql/.s.PGSQL.5432 would works fine in SQLx and sea_orm::Database::connect(db_url)?

[maikelwever]

So, /var/run/postgresql/.s.PGSQL.5432 would works fine in SQLx and sea_orm::Database::connect(db_url)?

I'm using postgres:///database_name as the DATABASE_URL, which implicitly uses /var/run/postgresql/.s.PGSQL.5432 as the connection endpoint, which works in SQLx and using sea_orm::Database::connect(db_url).

[billy1624]

I think we can relax the rules here and allow UNIX socket connection

sea-orm/sea-orm-cli/src/main.rs

Lines 58 to 67 in e80aa07

	// Skip checking if it's SQLite
	if !is_sqlite {
	// Panic on any that are missing
	if url_username.is_empty() {
	panic!("No username was found in the database url");
	}
	if url_host.is_none() {
	panic!("No host was found in the database url");
	}
	}

[billy1624]

Hey @maikelwever, sorry the the delay. Please check the PR I just created

• [cli] skip checking connection string contains username and host #851

[qwuik]

For those who trying to connect via unix socket. This works well:
DATABASE_URL=postgres://USERNAME:PASSWORD@localhost/DBNAME?host=/var/run/postgresql/
So path to unix socket should be as query string at the end. Maybe this shoud be documented.

[tyt2y3]

@qwuik Thank you for sharing.