Secure Your Postgres Password The Command Line Fix
When you reset a user password using a standard ALTER USER or CREATE USER SQL command, the statement (along with the clear-text password) gets printed to the PostgreSQL server logs depending on your server's log_statement settings.
This happens because PostgreSQL's logging code logs the raw queries as they are submitted. It is difficult for the logging system to automatically mask passwords because if a user makes a syntax error (for example, typing ALTER YSER instead of ALTER USER), the system fails to parse the command but still logs the raw text for debugging purposes.
To safely reset your password without it showing up in the logs, the sources recommend a few different approaches:
Use the \password command in psql: This is the simplest and safest method. When you connect to your server locally using the psql client and type \password, you will be prompted to enter and confirm your new password. This command encrypts the password on the client side before sending it, ensuring that the plain-text password is never transmitted in the SQL statement and leaves no trace in the server logs.
Temporarily disable logging (The "Hack" Method): If you must use a standard SQL command, you can temporarily suppress statement logging for your session by running set log_statement='none'; right before your ALTER USER command. Keep in mind that this means the password change event won't be logged at all.
Upgrade your authentication methods: For long-term security, experts recommend moving away from basic clear-text or LDAP authentication (which sends passwords in clear text during authentication). Instead, you should use SCRAM authentication (available in PostgreSQL v10 and later), Kerberos, or client-side certificates.