Goal This query identifies SQL and PL/pgSQL authentication routines that are vulnerable to user enumeration via timing attacks. When verifying user credentials, if a routine returns FALSE immediately upon determining that an account does not exist, an attacker can measure execution latency to confirm valid usernames. Because cryptographic password hashing (such as crypt() using bcrypt) is intentionally slow, hashing only for existing users creates an observable timing discrepancy. The query inspects boolean routines handling credentials (accepting 2 or 3 parameters) and flags those that fail to implement a dummy hash fallback—such as pairing a static bcrypt hash with COALESCE—to ensure uniform, constant-time execution.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability Medium Medium number of false-positive results
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH routines_base AS (
    SELECT 
        r.routine_schema, 
        r.routine_name, 
        pg_get_function_identity_arguments(p.oid) AS parameters,
        r.data_type AS return_type, 
        r.is_deterministic, 
        r.routine_type,
        r.security_type,
        r.external_language,        
        CASE 
            WHEN p.prokind = 'f' THEN pg_get_functiondef(p.oid)
            ELSE NULL 
        END AS raw_src
    FROM information_schema.routines r
    INNER JOIN pg_catalog.pg_proc p 
        ON p.oid = translate(substring(r.specific_name, '_[0-9]+$'), '_', '')::int::oid
    WHERE r.routine_schema NOT IN (
        SELECT schema_name
        FROM information_schema.schemata
        WHERE schema_name <> 'public' 
          AND schema_owner = 'postgres' 
          AND schema_name IS NOT NULL
    )     
    AND p.prokind = 'f'    
    AND lower(r.data_type) = 'boolean'    
    AND p.pronargs IN (2, 3)
    AND r.external_language IN ('PLPGSQL', 'SQL')
    AND r.routine_name NOT IN (
        'f_assume_you_must_use_files', 
        'f_check_format_comma_separated_list', 
        'f_check_password', 
        'f_default_value_with_no_match'
    )
    AND NOT EXISTS (
        SELECT 1
        FROM pg_catalog.pg_depend d 
        INNER JOIN pg_catalog.pg_extension e ON d.refobjid = e.oid
        WHERE d.objid = p.oid
    )
),
auth_routines AS (
    SELECT 
        routine_schema,
        routine_name,
        parameters,
        return_type,
        is_deterministic,
        routine_type,
        security_type,
        external_language,
        raw_src,
        regexp_replace(regexp_replace(raw_src, '<', '&lt;', 'g'), '[\r\n]', '<br>', 'g') AS routine_src
    FROM routines_base
    WHERE       
      (parameters ~* '\y(parool|password|pwd|pass)\y' AND parameters ~* '\y(kasutaja|user|login|isik)\y')
      OR raw_src ~* '\ycrypt\s*\('
)
SELECT 
    routine_schema, 
    routine_name, 
    parameters,
    return_type, 
    is_deterministic, 
    routine_type,
    security_type,
    external_language,
    routine_src
FROM auth_routines
WHERE     
    NOT (
        raw_src ~* '\$2[abxy]\$[0-9]{2}\$[A-Za-z0-9./]{22,}' 
        AND raw_src ~* '\ycoalesce\s*\('
        AND raw_src ~* '\ycrypt\s*\('
    )
ORDER BY routine_schema, routine_name, parameters;

Fixing Suggestion

To eliminate the timing discrepancy and prevent user enumeration, ensure that the hashing function runs in near-constant time regardless of whether the user exists:

  • Define a dummy hash: Store a static, pre-computed bcrypt dummy hash within the routine (e.g., '$2a$10$abcdefghijklmnopqrstuu...').
  • Apply COALESCE: When querying the stored password, use COALESCE(stored_hash, dummy_hash) so that a valid hash string is always passed to the hashing function.
  • Hash unconditionally: Always execute crypt(input_password, resolved_hash), ensuring that the expensive cryptographic operation executes on every authentication attempt.
  • Return combined result: Return TRUE only if the user actually exists AND the computed hash matches (e.g., RETURN user_exists AND (stored_hash = crypt(input_password, stored_hash));).

Collections

This query belongs to the following collections:

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

Result quality depends on names

Queries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.

Security

Queries of this category provide information about the security measures.

User-defined routines

Queries of this category provide information about the user-defined routines

NameDescription
Result quality depends on namesQueries of this category use names (for instance, column names) to try to guess the meaning of a database object. Thus, the goodness of names determines the number of false positive and false negative results.
SecurityQueries of this category provide information about the security measures.
User-defined routinesQueries of this category provide information about the user-defined routines