Goal This query identifies base table columns whose names suggest they store user passwords (such as password, pwd, or parool) and retrieves a sample value from each flagged column for security inspection. Storing passwords in plaintext—or using weak, unsalted hashes like raw MD5—is a critical security vulnerability: if the database or its backups are compromised, attackers immediately gain unrestricted access to user accounts. Inspecting a sample value allows auditors to verify that stored credentials follow a strong, salted, slow cryptographic hash format (such as bcrypt or Argon2, recognizable by prefixes like $2a$, $2b$, or $argon2id$). Because this query returns actual stored values, it should be executed strictly within secure, access-controlled auditing environments.
Notes The test creates a user-defined function that should be dropped in the end (see the garbage collection). The query considers both column names in English and Estonian. The query returns (randomly) only one value. It could be that some values in the column are hashed and some are not.
Type General Overview of some aspect of the database.
License MIT (opens in new tab)
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
CREATE OR REPLACE FUNCTION f_check_password()
RETURNS TEXT
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, information_schema, pg_temp
AS $$
DECLARE
    sql_stmt TEXT;
    passwd_columns RECORD;
    sample_value TEXT;
    return_str TEXT := '';
BEGIN

    FOR passwd_columns IN
        SELECT c.table_schema, c.table_name, c.column_name
        FROM information_schema.columns AS c
        INNER JOIN information_schema.tables AS t
            USING (table_schema, table_name)
        WHERE c.data_type IN ('character', 'character varying', 'text')
          AND t.table_type = 'BASE TABLE'
          AND c.table_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 c.column_name ~* '(parool|salasona|passw|pwd|(^|_)pass($|_))'
        ORDER BY c.table_schema, c.table_name, c.ordinal_position
    LOOP
        sql_stmt := format(
            'SELECT %I::text FROM %I.%I WHERE %I IS NOT NULL LIMIT 1',
            passwd_columns.column_name,
            passwd_columns.table_schema,
            passwd_columns.table_name,
            passwd_columns.column_name
        );

        EXECUTE sql_stmt INTO sample_value;

        return_str := return_str
            || CASE WHEN return_str <> '' THEN E'\n' ELSE '' END  -- veebivaates võid E'\n' asendada '<br>'
            || format(
                '%I.%I.%I=%s',
                passwd_columns.table_schema,
                passwd_columns.table_name,
                passwd_columns.column_name,
                COALESCE(sample_value, '<NULL / empty table>')
            );
    END LOOP;

    RETURN NULLIF(return_str, '');
END;
$$;

REVOKE ALL ON FUNCTION f_check_password() FROM PUBLIC;

WITH res AS (
    SELECT * FROM f_check_password()
)
SELECT *
FROM res
WHERE sample_value IS NOT NULL;

DROP FUNCTION IF EXISTS f_check_password();

Fixing Suggestion

If any returned sample value appears as plaintext or a weak hash, migrate the column to store salted cryptographic hashes using Argon2id or bcrypt (e.g., via PostgreSQL's pgcrypto crypt() function). Consider adding a CHECK constraint to enforce valid hash formatting and require password resets for any exposed accounts.

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 by overview

Queries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems or not .

NameDescription
Find problems by overviewQueries that results point to different aspects of database that might have problems. A human reviewer has to decide based on the results as to whether there are problems 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.