how to speed up authentication?

[algoritmsystems]

Hi! it takes much time (>40ms) to authenticate from backend to postgresql (version 14). But when I change scram-sha-256 to md5, authentication works fine (10ms). what slows down when I use the scrum-sha-256 authentication method?

lscpu | grep -i aes

... aes ...

openssl engine -t

(rdrand) Intel RDRAND engine [ available ] (dynamic) Dynamic engine loading support [ unavailable ]

openssl version

OpenSSL 3.0.2 15 Mar 2022 (Library: OpenSSL 3.0.2 15 Mar 2022)

pool_mode = transaction

Thanks in advance!

[vitabaks]

Hi! The delay with SCRAM-SHA-256 is likely due to higher computational demands compared to MD5, which is faster but less secure.

[Jamic28]

Hi! The delay with SCRAM-SHA-256 is likely due to higher computational demands compared to MD5, which is faster but less secure.

Hi @vitabaks! Does it make sense to use scram-sha-256 instead of md5 when pgbouncer and posrgresql are on the same host? Well configure the application that runs on the other host with pgbouncer via scram-sha-256!

[vitabaks]

I use md5 on many clusters. I don't see this as a big problem because the databases are not on the public network.

[Jamic28]

This configuration belongs to bgbouncer and postgresql?

postgresql_pg_hba:

• { type: "local", database: "all", user: "{{ patroni_superuser_username }}", address: "", method: "trust" }
• { type: "local", database: "all", user: "{{ pgbouncer_auth_username }}", address: "", method: "trust" } # required for pgbouncer auth_user
• { type: "local", database: "replication", user: "{{ patroni_superuser_username }}", address: "", method: "trust" }
• { type: "local", database: "all", user: "all", address: "", method: "{{ postgresql_password_encryption_algorithm }}" }
• { type: "host", database: "all", user: "all", address: "127.0.0.1/32", method: "{{ postgresql_password_encryption_algorithm }}" }
• { type: "host", database: "all", user: "all", address: "::1/128", method: "{{ postgresql_password_encryption_algorithm }}" }
• { type: "host", database: "all", user: "all", address: "0.0.0.0/0", method: "{{ postgresql_password_encryption_algorithm }}" }

[vitabaks]

The choice between MD5 and SCRAM-SHA-256 for PostgreSQL authentication is context-specific and depends on the service, company requirements, and security policies. Internal systems with restricted access might allow MD5, while more critical or public-facing systems should use SCRAM-SHA-256 for stronger protection. There isn't a one-size-fits-all solution; the decision should align with the security level needed for each specific environment.

[Jamic28]

Thanks!

[vitabaks]

See postgresql_password_encryption_algorithm variable.