I manage a PostgreSQL database cluster - PostgreSQL v12.4.
Our database cluster is on a Linux VM, with OS: Flavor: redhat_7
Release: 3.10.0-1160.15.2.el7.x86_64
We have a repmgr cluster of 1 Primary & 2 Standby servers & use another server with PgBouncer to direct the connections to the current Primary. Our firewall team writes exceptions for the application connections into the PgBouncer server.
We are in the process of adding a new database for a new client.
We normally create our databases & objects with a DB Owner account & the application connects with a "Hero" user account that has read/write permissions.
However, the new customer has asked if it would be possible for their employees to connect (via a VPN) from multiple locations with existing Enterprise Active Directory (EAD) groups instead of using connections from the application with a single "Hero" account.
With the PostgreSQL/PgBouncer combination, we put entries in the pg_hba.conf file for PostgreSQL. A normal entry for an external connection to our databases would look like this:
hostssl dbName userName IPAddress/32 md5
& the PgBouncer database.ini & userlist entries, respectively, would be:
dbAlias = host=PostgreSQLServerName dbname=dbName auth_user=HeroAcct
"HeroAcct" "md5...."
where
dbAlias stands for the PgBouncer database alias
dbName stands for the PostgreSQL database name
HeroAcct stands for the username used for the application connection to the database
PostgreSQLServerName stands for the current Primary PostgreSQL server
and IPAddress is the IP for the PgBouncer server
Is it possible to allow connections from EAD groups instead of individual accounts & if so, can you tell me how the config files would be changed to allow it?
