Goal This query checks whether stored bcrypt password hashes use a cost factor (work factor) lower than 12. It inspects text-based columns in base tables whose names suggest password storage, parses the cost parameter from sample bcrypt hashes ($2a$, $2b$, $2x$, or $2y$), and flags entries where the numeric cost is less than 12. Because the bcrypt cost factor scales exponentially (2cost iterations), an insufficiently low work factor drastically reduces the computational effort required for attackers to perform offline brute-force cracking if the database or its backups are compromised.
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
CREATE OR REPLACE FUNCTION f_check_password()
RETURNS TABLE (
    table_schema TEXT,
    table_name TEXT,
    column_name TEXT,
    sample_value TEXT
)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, information_schema, pg_temp
AS $$
DECLARE
    passwd_col RECORD;
    v_sql TEXT;
    v_sample TEXT;
BEGIN

    FOR passwd_col IN
        SELECT c.table_schema, c.table_name, c.column_name
        FROM information_schema.columns AS c
        INNER JOIN information_schema.tables AS t
            ON c.table_schema = t.table_schema
           AND c.table_name = t.table_name
        WHERE c.data_type IN ('character', 'character varying', 'text')
          AND t.table_type = 'BASE TABLE'
          AND c.table_schema NOT IN (
              SELECT s.schema_name
              FROM information_schema.schemata AS s
              WHERE s.schema_name <> 'public'
                AND s.schema_owner = 'postgres'
                AND s.schema_name IS NOT NULL
          )
          AND c.column_name ~* '(parool|salasona|passw|pwd|(^|_)pass($|_))'
        ORDER BY c.table_schema, c.table_name, c.ordinal_position
    LOOP
        v_sql := format(
            'SELECT %I::text FROM %I.%I WHERE %I IS NOT NULL LIMIT 1',
            passwd_col.column_name,
            passwd_col.table_schema,
            passwd_col.table_name,
            passwd_col.column_name
        );

        EXECUTE v_sql INTO v_sample;

        table_schema := passwd_col.table_schema;
        table_name   := passwd_col.table_name;
        column_name  := passwd_col.column_name;
        sample_value := v_sample;

        RETURN NEXT;
    END LOOP;

END;
$$;

REVOKE ALL ON FUNCTION f_check_password() FROM PUBLIC;

SELECT
    table_schema,
    table_name,
    column_name,
    sample_value,
    substring(sample_value FROM '^\$2[abxy]?\$(\d{2})\$')::integer AS bcrypt_cost_factor
FROM f_check_password()
WHERE sample_value IS NOT NULL
  AND substring(sample_value FROM '^\$2[abxy]?\$(\d{2})\$')::integer < 12;

DROP FUNCTION IF EXISTS f_check_password();

Fixing Suggestion

To strengthen password storage against offline brute-force attacks, increase the bcrypt cost factor to at least 12:

  1. Explicitly set the cost factor: Update your application or database hashing logic to specify a cost factor of 12 or higher (for example, when using PostgreSQL's pgcrypto extension, pass the parameter explicitly: crypt(password, gen_salt('bf', 12))).

  2. Benchmark login performance: Test the updated cost factor on your target production hardware to ensure an optimal balance between brute-force resistance and acceptable user login latency.

  3. Rehash transparently on login: Because existing hashes cannot be upgraded without the user's plaintext password, update your authentication workflow to detect hashes with a cost factor below 12 during successful logins and automatically re-hash and save them with the stronger setting.

SQL statement to clean up temporary or helper database objects created for or by this query.

DROP FUNCTION IF EXISTS f_check_password();

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:

Data at the database logical level

Queries of this category provide information about data in base tables.

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.

NameDescription
Data at the database logical levelQueries of this category provide information about data in base tables.
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.