Skip to main content

Troubleshoot PostgreSQL

This document describes methods to resolve issues related to PostgreSQL service.

Expose user password in PostgreSQL log​

When using commands such as CREATE or ALTER, sensitive information like user passwords is exposed in plain text in PostgreSQL logs.

Example of password exposure
# Use CREATE command.
postgres=> CREATE ROLE test_kc_mapo WITH PASSWORD ‘test0701’;

# When checking the log, the password is exposed as is.
2025-07-01 12:00:00 UTC [12345] LOG: statement: CREATE ROLE test_kc_mapo WITH PASSWORD 'test0701'

▶️ Solution: To prevent security risks, use the \password command, which operates on the client side, instead of SQL statements for password-related tasks.

  1. Use \password command for user password operations.

    Example of using \password
    # 1. Create 'test_kc_mapo' account. Do not grant login permission to check ALTER log.
    postgres=> CREATE ROLE test_kc_mapo NOLOGIN;CREATE ROLE

    # 2. Use `\password` to change the password and grant login permission.
    postgres=> \password test_kc_mapo
    Enter new password:
    Enter it again:
    postgres=> ALTER ROLE test_kc_mapo LOGIN;
    ALTER ROLE
    postgres=>

  2. Check logs for the changes.

    Example of log check
    # Encryption method varies by PostgreSQL, but passwords are not exposed in plain text.
    2025-07-01 12:00:29 UTC [12345] LOG:  statement: CREATE ROLE test_kc_mapo NOLOGIN;
    2025-07-01 12:00:48 UTC [12345] LOG:  statement: ALTER USER test_kc_mapo PASSWORD '*******'
    2025-07-01 12:01:06 UTC [12345] LOG:  statement: CREATE ROLE test_kc_mapo LOGIN;