Error: failed to connect to database: password authentication failed in Rust

Question

I am trying to connect to database in Rust using sqlx crate and Postgres database.

main.rs:

use dotenv;
use sqlx::Pool;
use sqlx::PgPool;
use sqlx::query;

#[async_std::main]
async fn main() -> Result<(), Error> {
    dotenv::dotenv().ok();
    pretty_env_logger::init();
    let url = std::env::var("DATABASE_URL").unwrap();
    dbg!(url);

    let db_url = std::env::var("DATABASE_URL")?;
    let db_pool: PgPool = Pool::new(&db_url).await?;

    let rows = query!("select 1 as one").fetch_one(&db_pool).await?;
    dbg!(rows);

    let mut app = tide::new();
    app.at("/").get(|_| async move {Ok("Hello Rustacean!")});
    app.listen("127.0.0.1:8080").await?;

    Ok(())
}

#[derive(thiserror::Error, Debug)]
enum Error {
    #[error(transparent)]
    DbError(#[from] sqlx::Error),

    #[error(transparent)]
    IoError(#[from] std::io::Error),

    #[error(transparent)]
    VarError(#[from] std::env::VarError),
}

Here is my .env file:

DATABASE_URL=postgres://localhost/twitter
RUST_LOG=trace

Error log:

error: failed to connect to database: password authentication failed for user "ayman"
  --> src/main.rs:19:16
   |
19 |     let rows = query!("select 1 as one").fetch_one(&db_pool).await?;
   |                ^^^^^^^^^^^^^^^^^^^^^^^^^
   |
   = note: this error originates in a macro (in Nightly builds, run with -Z macro-backtrace for more info)

error: aborting due to previous error

error: could not compile `backend`.

Note:

1. There exists a database called twitter.
2. I have include macros for sqlx's dependency

sqlx = {version="0.3.5", features = ["runtime-async-std", "macros", "chrono", "json", "postgres", "uuid"]}

Am I missing some level of authentication for connecting to database? I could not find it in docs for sqlx::Query macro

Answer 1

The reason why it is unable to authenticate is that you must provide credentials before accessing the database

There are two ways to do it

Option 1: Change your URL to contain the credentials - For instance -

DATABASE_URL=postgres://localhost?dbname=mydb&user=postgres&password=postgres

Option 2 Use PgConnectionOptions - For instance

let pool_options = PgConnectOptions::new()
        .host("localhost")
        .port(5432)
        .username("dbuser")
        .database("dbtest")
        .password("dbpassword");

    let pool: PgPool = Pool::<Postgres>::connect_with(pool_options).await?;

Note: The sqlx version that I am using is sqlx = {version="0.5.1"}

For more information refer the docs - https://docs.rs/sqlx/0.5.1/sqlx/postgres/struct.PgConnectOptions.html#method.password

Hope this helps you.