search

yandex / odyssey

Scalable PostgreSQL connection pooler
BSD 3-Clause "New" or "Revised" License
3.21k stars 161 forks source link

Config for conditional md5/scram-sha-256 frontend and scram-sha-256 backend #108

Open ruimarinho opened 4 years ago

ruimarinho commented 4 years ago

Hi,

I would like to enable a basic config where the frontend (app client -> odyssey) is secured by md5 with an auth_query and odyssey -> server is secured by auth_query_user with scram-sha-256.

This is possible in pgbouncer and should be even easier with odyssey because it supports more granularity. This is a possible approach for rolling out incremental SCRAM support to an existing database cluster.

database "foobar" {
    user "authenticator" {
        authentication "scram-sha-256"
        storage "postgres_server"
                password "SCRAM-SHA-256$4096:...70="
        pool "transaction"
    }

    user "client_with_scram" {
        authentication "scram-sha-256"
        storage "postgres_server"
                password "SCRAM-SHA-256$4096:...41="
        pool "transaction"
    }

    user default {
        authentication "md5"
        storage "postgres_server"

                auth_query "SELECT * FROM authenticator.authenticate('%u')"
                auth_query_db "foobar"
                auth_query_user "authenticator"

        pool "transaction"
    }
}

The output on the remote is:

FATAL:  password authentication failed for user "authenticator"
DETAIL:  Connection matched pg_hba.conf line 82: "host    all    authenticator    all    scram-sha-256"

And on odyssey:

postgres_1         | 1 28 Jan 01:43:51.526 debug [none s3e75880938e1] (startup) Authentication
postgres_1         | 1 28 Jan 01:43:51.526 debug [none s3e75880938e1] (auth) requested SASL authentication
postgres_1         | 1 28 Jan 01:43:51.526 debug [none s3e75880938e1] (startup) Authentication
postgres_1         | 1 28 Jan 01:43:51.526 debug [none s3e75880938e1] (auth) continue SASL authentication
postgres_1         | 1 28 Jan 01:43:51.540 debug [none s3e75880938e1] (startup) ErrorResponse
postgres_1         | 1 28 Jan 01:43:51.541 error [a32e7510450cb s3e75880938e1] (startup) FATAL 28P01 password authentication failed for user "authenticator"
postgres_1         | 1 28 Jan 01:43:51.541 error [c9581d35e2700 none] (auth) failed to make auth_query
x4m commented 4 years ago

Hi! I think you can use these settings to authenticate in server storage_db "database" storage_user "test" storage_password "test"

ruimarinho commented 4 years ago

@x4m on the user "authenticator" entry?