PgBouncer
PgBouncer is made available on Bridge instances by default to ease connection management by multiplexing native Postgres connections across its own "virtual" connections.
Although the PgBouncer service is available on Bridge instances by default,
you'll need to take one extra step on each database you want to use it on by
installing the crunchy_pooler
extension.
Activating PgBouncer with the crunchy_pooler
extension
As the superuser, run this in the database to install the crunchy_pooler
extension:
CREATE EXTENSION crunchy_pooler;
crunchy_pooler
is simple extension which creates a user called
crunchy_pooler
with access to a single function called user_lookup
that
allows PgBouncer to authenticate incoming connections. Now when a client makes a
connection to PgBouncer, it can check whether its credentials are valid by
querying Postgres' canonical user store.
Info
The crunchy_pooler
extension must be installed in each database where you want to connect to PgBouncer. Otherwise you may receive an error like:
failed: FATAL: bouncer config error
Connect to the database and run: create extension crunchy_pooler;
to resolve the error.
Connecting to PgBouncer
Clients will connect to PgBouncer using the same connection string they'd use
for the main Postgres database, except on port 5431
instead of the usual
5432
:
psql postgres://my_application_user:my_application_password@p.43lmodgbqvdmlpbjirv22dfciu.db.postgresbridge.com:5431/mydb
The user_lookup
function created by crunchy_pooler
will deny lookups on
superusers -- only non-superusers will be able to connect through PgBouncer. The
default role made available through the Bridge Dashboard is a superuser, so
you'll need to create a new role by
running CREATE ROLE
SQL
from the database:
CREATE ROLE my_application_user WITH PASSWORD 'my_application_password';
Or by using the cluster roles API.
Hint
The terms "user" and "role" in Postgres are largely synonymous, with a minor
difference being that CREATE USER
(versus CREATE ROLE
) implies LOGIN
privilege, so according to the principle of least
privilege,
CREATE ROLE
is the better choice for users/roles that meant for use by
applications rather than people.
Disabling PgBouncer
Dropping the crunchy_pooler
exception will functionally disable PgBouncer as
it'll no longer be able to authenticate:
DROP EXTENSION crunchy_pooler;