8 Security bad practices

PostgreSQL offers strong security defaults and a vigilant security team, but real-world breaches often result from everyday operator mistakes. This chapter surveys the practices that most commonly undermine a secure deployment: relying on misleading password prompts, exposing instances to untrusted networks, overbroad access rules, and writing or configuring database code in ways that invite privilege escalation or data leaks.

On connectivity and authentication, the -W/--password switch is a footgun: it prompts even when the server doesn’t require a password, masking dangerous passwordless setups and training users to trust “successful” logins even with mistyped credentials. Avoid embedding cleartext passwords in commands or configs; let the server prompt when needed and use proper secret storage. Network-wise, setting listen_addresses to “*” expands the attack surface by accepting connections on every interface; bind only to required private addresses and layer firewall allowlists. In pg_hba.conf, trust is not authentication and should be avoided entirely; instead, use scram-sha-256, enforce SSL where appropriate, and craft narrow rules per database, role, and source host.

On authorization and code execution, don’t let superusers own application databases or connect as the application, as superuser privileges bypass most checks, weaken auditing, and enable accidental or intentional damage. SECURITY DEFINER turns functions into setuid-like entry points; restrict EXECUTE to intended roles, keep definitions simple, validate inputs, and set a safe search_path. Finally, control schema resolution to prevent object hijacking: revoke unnecessary CREATE on the public schema (especially on upgraded clusters), restrict who can create objects in shared schemas, prefer explicit schema qualification, and organize per-application schemas to block malicious overrides and unintended code execution.

A superuser, analogous to the root user in UNIX, can bypass all privilege checks and do more things that no other user can. It is far more dangerous than simply having all privileges on all databases.

• Using psql -W or --password can be confusing and lead to lapses in security. Rely on PostgreSQL’s automatic built-in password prompt mechanism instead.
• Setting listen_addresses = '*' can expose your database server to insecure networks, so you should only enable the trusted network interfaces that are necessary for database connectivity.
• Using the trust method in pg_hba.conf in production environments is unacceptable. You should always enforce proper authentication to your server and restrict access as much as is practical.
• Having your databases and their contents owned by a superuser can lead to security problems and accidental damage to your data. Instead, create roles that have only the relevant permissions to own and manage these databases, and grant permissions selectively to other roles.
• Declaring functions as SECURITY DEFINER can cause data leaks and enable privilege escalation. To reduce risk, use it sparingly and with a safe search_path, and prefer the combination of SECURITY INVOKER with explicit GRANTs.
• Not securing your search_path can let others hijack queries and escalate their privileges. Apply tight control over object creation in schemas, and reference objects owned by trusted users only in queries.

FAQ