Function sqlAuthenticationQuery

internal/pgbouncer/postgres.go:29–55

sqlAuthenticationQuery returns the SECURITY DEFINER function that allows PgBouncer to access non-privileged and non-system user credentials.

(sqlFunctionName string)

// sqlAuthenticationQuery returns the SECURITY DEFINER function that allows
// PgBouncer to access non-privileged and non-system user credentials.
func sqlAuthenticationQuery(sqlFunctionName string) string {
	// Only a subset of authorization identifiers should be accessible to
	// PgBouncer.
	// - https://www.postgresql.org/docs/current/catalog-pg-authid.html
	sqlAuthorizationConditions := strings.Join([]string{
		// Only those with permission to login.
		`pg_authid.rolcanlogin`,
		// No superusers. This is important: allowing superusers would make the
		// PgBouncer user a de facto superuser.
		`NOT pg_authid.rolsuper`,
		// No replicators.
		`NOT pg_authid.rolreplication`,
		// Not the PgBouncer role itself.
		`pg_authid.rolname <> ` + postgres.QuoteLiteral(PostgresqlUser),
		// Those without a password expiration or an expiration in the future.
		`(pg_authid.rolvaliduntil IS NULL OR pg_authid.rolvaliduntil >= CURRENT_TIMESTAMP)`,
	}, "\n    AND ")

	return strings.TrimSpace(`
CREATE OR REPLACE FUNCTION ` + sqlFunctionName + `(username TEXT)
RETURNS TABLE(username TEXT, password TEXT) AS ` + postgres.QuoteLiteral(`
  SELECT rolname::TEXT, rolpassword::TEXT
  FROM pg_catalog.pg_authid
  WHERE pg_authid.rolname = $1
    AND `+sqlAuthorizationConditions) + `
LANGUAGE SQL STABLE SECURITY DEFINER;`)
}

// DisableInPostgreSQL removes any objects created by EnableInPostgreSQL.
func DisableInPostgreSQL(ctx context.Context, exec postgres.Executor) error {