sqlx::test fails with "permission denied for table pg_namespace"

[jangerhard]

I've tried using the examples from the docs, using the following files:

.env:

DATABASE_URL="postgres://some_database_user:dummy_pass@localhost/some_database"

test-file:

    use sqlx::pool::{PoolConnection, PoolOptions};
    use sqlx::postgres::PgConnectOptions;
    use sqlx::{PgPool, Postgres};

    #[sqlx::test]
    async fn db(
        _pool_options: PoolOptions<Postgres>,
        _conn_options: PgConnectOptions,
    ) -> sqlx::Result<()> {
        Ok(())
    }

    #[sqlx::test]
    async fn pool_test(_pool: PgPool) -> sqlx::Result<()> {
        Ok(())
    }

    #[sqlx::test]
    async fn conn_test(_conn: PoolConnection<Postgres>) -> sqlx::Result<()> {
        Ok(())
    }

All test-functions fail with the following message:

failed to connect to setup test database: 
Database(PgDatabaseError { 
    severity: Error, 
    code: "42501", 
    message: "permission denied for table pg_namespace", 
    detail: None, 
    hint: None, 
    position: None, 
    where: None, 
    schema: None, 
    table: None, 
    column: None, 
    data_type: None, 
    constraint: None, 
    file: Some("aclchk.c"), 
    line: Some(3449), 
    routine: Some("aclcheck_error") 
    }
)

What am I doing wrong?

[abonander]

The user credentials you use with #[sqlx::test] need superuser permissions since they create and drop databases. The specific permission check that's failing there is the need for a LOCK grant on the pg_catalog.pg_namespace table in some_database as it also creates a _sqlx_test schema to host the objects it uses to track the databases it's created so it can clean them up later.

[sassman]

I struggle with this one as well.
I'm trying to create a dedicated user that has the minimal required privileges to run a #[sqlx::test]. What is left now is only the LOCK permission is still an issue.

[jangerhard]

I created a superuser and the tests started working! Thanks!

[alextes]

Many cloud SQL solutions will not grant superuser roles. Most will however grant broad permissions, even to create databases. Is there a way to use #[sqlx::test] without having access to a superuser?

Creating a schema sounds fine for example. It could be a one-off command to run. "creating a superuser" unfortunately will be impossible for many.

[alextes]

Wrote a small implementation that tries to mimic this feature as a workaround. After having spent hours struggling with tests that use transactions when actual code uses pools I think some form of isolated testing like this is the way forward. I think not requiring superuser is also the way forward. So better get coding 😅 .

This code is mediocre at best. Any improvements welcome. Or better yet, contribute them to sqlx it has a lot of good parts 💛 .

    pub struct TestDb {
        pool: PgPool,
        name: String,
    }

    static DB_ID_COUNTER: AtomicUsize = AtomicUsize::new(1);
    fn get_id() -> usize {
        DB_ID_COUNTER.fetch_add(1, std::sync::atomic::Ordering::Relaxed)
    }

    impl TestDb {
        pub async fn new() -> Self {
            let name = format!("testdb_{}", get_id());

            let mut connection = get_test_db_connection().await;
            sqlx::query(&format!("CREATE DATABASE {}", name))
                .execute(&mut connection)
                .await
                .unwrap();

            let pool = PgPoolOptions::new()
                .max_connections(1)
                .connect(&DB_URL.to_string().replace("testdb", &name))
                .await
                .unwrap();

            sqlx::migrate!("./migrations").run(&pool).await.unwrap();

            Self { pool, name }
        }

        pub fn pool(&self) -> &PgPool {
            &self.pool
        }

        pub async fn cleanup(&self) {
            self.pool.close().await;
            let mut connection = get_test_db_connection().await;
            sqlx::query(&format!("DROP DATABASE {}", self.name))
                .execute(&mut connection)
                .await
                .unwrap();
        }
    }

[agirorn]

I think the error message can also be improved to here to improve usability. Here is the full error message I got

failed to connect to setup test database: Database(PgDatabaseError { severity: Error, code: "42501", message: "permission denied for table pg_namespace", detail: None, hint: None, position: None, where: None, schema: None, table: None, column: None, data_type: None, constraint: None, file: Some("aclchk.c"), line: Some(3650), routine: Some("aclcheck_error") })

My main issues with this error message are 2 things

1. The error is wrong since it states it could not connect to the database, but it did connect and the real issue here is that it did not have the necessary privileges to do its job.
2. Since this is a known issue, then the error could provide more information to the user on how to resolve the issue.

The error message could say.

"Unable to run the tests because the user 'username' does not have SUPERUSER privileges on the server 'server-name-or-ip', required to create and drop the database under test."