Function EnableInPostgreSQL

internal/pgbouncer/postgres.go:124–202

EnableInPostgreSQL creates the PgBouncer user, schema, and SECURITY DEFINER function that allows it to authenticate clients using their password stored in PostgreSQL.

(
	ctx context.Context, exec postgres.Executor, clusterSecret *corev1.Secret,
)

// function that allows it to authenticate clients using their password stored
// in PostgreSQL.
func EnableInPostgreSQL(
	ctx context.Context, exec postgres.Executor, clusterSecret *corev1.Secret,
) error {
	log := logging.FromContext(ctx)

	stdout, stderr, err := exec.ExecInAllDatabases(ctx,
		strings.Join([]string{
			// Quiet NOTICE messages from IF NOT EXISTS statements.
			// - https://www.postgresql.org/docs/current/runtime-config-client.html
			`SET client_min_messages = WARNING;`,

			// Do not wait for changes to be replicated. [Since PostgreSQL v9.1]
			// - https://www.postgresql.org/docs/current/runtime-config-wal.html
			`SET synchronous_commit = LOCAL;`,

			// Create the following objects in a transaction so that permissions
			// are correct before any other session sees them.
			// - https://www.postgresql.org/docs/current/ddl-priv.html
			`BEGIN;`,

			// Create the PgBouncer user if it does not already exist.
			// Permissions are granted later.
			strings.TrimSpace(`
SELECT pg_catalog.format('CREATE ROLE %I NOLOGIN', :'username')
 WHERE NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = :'username')
\gexec`),

			// Ensure the user can only access the one schema. Revoke anything
			// that might have been granted on other schemas, like "public".
			strings.TrimSpace(`
SELECT pg_catalog.format('REVOKE ALL PRIVILEGES ON SCHEMA %I FROM %I', nspname, :'username')
  FROM pg_catalog.pg_namespace
 WHERE pg_catalog.has_schema_privilege(:'username', oid, 'CREATE, USAGE')
   AND nspname NOT IN ('pg_catalog', :'namespace')
\gexec`),

			// Create the one schema and lock it down. Only the one user is
			// allowed to use it.
			strings.TrimSpace(`
CREATE SCHEMA IF NOT EXISTS :"namespace";
REVOKE ALL PRIVILEGES
    ON SCHEMA :"namespace" FROM PUBLIC, :"username";
 GRANT USAGE
    ON SCHEMA :"namespace" TO :"username";`),

			// The "get_auth" function returns the appropriate credentials for
			// a user's password-based authentication and works with PgBouncer's
			// "auth_query" setting. Only the one user is allowed to execute it.
			// - https://www.pgbouncer.org/config.html#auth_query
			sqlAuthenticationQuery(`:"namespace".get_auth`),
			strings.TrimSpace(`
REVOKE ALL PRIVILEGES
    ON FUNCTION :"namespace".get_auth(username TEXT) FROM PUBLIC, :"username";
 GRANT EXECUTE
    ON FUNCTION :"namespace".get_auth(username TEXT) TO :"username";`),

			// Remove "public" from the PgBouncer user's search_path.
			// - https://www.postgresql.org/docs/current/perm-functions.html