How to Tell Which pg_hba.conf Rule Allowed Your Connection in YugabyteDB

				
					CREATE SCHEMA IF NOT EXISTS util;

CREATE OR REPLACE FUNCTION util.show_hba_match()
RETURNS TABLE (
  client_ip        text,
  dbname           text,
  username         text,
  application_name text,
  ssl              boolean,
  clientdn         text,
  matched_line     int,
  matched_type     text,
  matched_db       text,
  matched_user     text,
  matched_address  text,
  matched_method   text,
  matched_options  text
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog
AS $$
DECLARE
  v_client_inet inet := inet_client_addr();
  v_client_ip   text := COALESCE(host(inet_client_addr()), 'local');
  v_username    text := session_user;
  v_dbname      text := current_database();
  v_appname     text := COALESCE(current_setting('application_name', true), '');
  v_ssl         boolean := COALESCE(
                           (SELECT s.ssl
                            FROM pg_stat_ssl s
                            WHERE s.pid = pg_backend_pid()),
                           false
                         );
  v_clientdn    text := '';
BEGIN
  -- pg_stat_ssl column name differs by version: client_dn vs clientdn
  IF EXISTS (
    SELECT 1
    FROM pg_attribute
    WHERE attrelid = 'pg_stat_ssl'::regclass
      AND attname = 'client_dn'
  ) THEN
    EXECUTE 'SELECT COALESCE(s.client_dn::text, '''')
             FROM pg_stat_ssl s
             WHERE s.pid = pg_backend_pid()'
      INTO v_clientdn;
  ELSE
    EXECUTE 'SELECT COALESCE(s.clientdn::text, '''')
             FROM pg_stat_ssl s
             WHERE s.pid = pg_backend_pid()'
      INTO v_clientdn;
  END IF;

  RETURN QUERY
  WITH me AS (
    SELECT
      v_client_inet AS client_inet,
      v_client_ip   AS client_ip,
      v_username    AS username,
      v_dbname      AS dbname,
      v_appname     AS application_name,
      v_ssl         AS ssl,
      v_clientdn    AS clientdn
  ),
  candidates AS (
    SELECT r.*
    FROM pg_hba_file_rules r, me
    WHERE
      -- SSL vs non-SSL rule types
      ((me.ssl AND r.type = 'hostssl') OR ((NOT me.ssl) AND r.type = 'hostnossl'))

      -- Database match
      AND ('all' = ANY(r.database) OR me.dbname = ANY(r.database))

      -- User match: all | exact | +role
      AND (
        'all' = ANY(r.user_name)
        OR me.username = ANY(r.user_name)
        OR EXISTS (
          SELECT 1
          FROM unnest(r.user_name) u(entry)
          WHERE left(entry, 1) = '+'
            AND pg_has_role(me.username, substr(entry, 2), 'member')
        )
      )

      -- Address match: 'all' OR client_inet within (address/netmask)
      AND (
        r.address IS NULL
        OR r.address = 'all'
        OR (
          me.client_inet IS NOT NULL
          AND me.client_inet <<= (r.address || '/' || masklen(r.netmask::inet))::cidr
        )
      )
    ORDER BY r.line_number
    LIMIT 1
  )
  SELECT
    me.client_ip,
    me.dbname,
    me.username,
    me.application_name,
    me.ssl,
    NULLIF(me.clientdn, '') AS clientdn,
    c.line_number,
    c.type,
    array_to_string(c.database, ',') AS matched_db,
    array_to_string(c.user_name, ',') AS matched_user,
    CASE
      WHEN c.address IS NULL THEN ''
      WHEN c.address = 'all' THEN 'all'
      ELSE c.address || '/' || masklen(c.netmask::inet)
    END AS matched_address,
    c.auth_method,
    COALESCE(array_to_string(c.options, ','), '') AS matched_options
  FROM me
  LEFT JOIN candidates c ON true;
END;
$$;

GRANT USAGE ON SCHEMA util TO PUBLIC;
GRANT EXECUTE ON FUNCTION util.show_hba_match() TO PUBLIC;