PostgreSQL is a robust and trusted database platform, but it’s not invincible. The PostgreSQL Global Development Group “takes security seriously,” enabling users to trust it with mission-critical data (PostgreSQL: Security Information). However, simply running PostgreSQL with default settings or careless practices can be like leaving your solid steel front door unlocked or even opened. You have the protection mechanisms, you just aren’t using them.

A very common concern is that security vulnerabilities can creep in through mistakes in how we write queries, configure the server, or especially how we manage user privileges. In fact, the PostgreSQL documentation itself emphasizes that security is a shared responsibility between the database and its environment (OS, applications, etc.) and provides guidance on how to configure and run PostgreSQL securely.

In this post, I will dive into three critical areas of PostgreSQL security: SQL injection, misconfiguration, and privilege escalation. We’ll explore how SQL injection attacks occur (with code examples in SQL and Node.js), how to harden PostgreSQL’s configuration to avoid common pitfalls, and how to prevent users or attackers from escalating privileges.

Guarding Against SQL Injection in PostgreSQL

SQL injection is one of the most critical vulnerabilities for any database-backed application. It occurs when an attacker injects malicious SQL fragments into an application’s queries, tricking the database into executing unintended commands. In PostgreSQL (as with any SQL database), if user inputs are embedded directly into SQL statements without proper handling, an attacker can modify the query’s logic or execute additional commands.

As one guide explains, SQL injection allows attackers to “subvert the original intent” of SQL statements by using unescaped parameters or destructive phrases (Preventing SQL Injection Attacks in Postgres | Crunchy Data Blog). This can lead to data theft, data tampering, or even deletion of entire tables (for example, injecting a DROP TABLE command. The fallout from such an attack is devastating to data integrity and the company’s reputation.

How SQL Injection Happens

Consider a simple application Node.js application using the pg client (node-postgres) to authenticate users. A naïve implementation might build a query by concatenating a username and password into a SQL string:

In this vulnerable code, an attacker could input alice’; — as the username. The resulting SQL would be:

The -- starts a comment, causing the password check to be ignored. This query will log in the attacker as “alice” without a valid password. Worse, an attacker could try an input like alice'; DROP TABLE accounts; -- to terminate the query and then drop the table. PostgreSQL will happily execute both the original query and the injected DROP TABLE if the query string is sent in one go. Clearly, an injection like this can fully compromise the database.

Preventing SQL Injection – Parameterization and Validation

The most effective way to stop injection is to avoid concatenating untrusted input into SQL commands. Instead, use parameterized queries (also known as prepared statements with placeholders). Parameterization means the SQL text is sent to the database with placeholders for data, while the data values are sent separately.

The database then safely binds the values to the placeholders, treating them purely as data rather than SQL (Queries – node-postgres). In node-postgres, for example, one can use $1, $2, etc. as placeholders in the query text and supply an array of values:

Here, no matter what user or pass contain (even if an attacker inputs SQL keywords or symbols), PostgreSQL will not treat them as SQL commands. The node-postgres driver sends the query text and data separately to the server, and the server’s “battle-tested” parameter substitution logic ensures the values cannot alter the SQL syntax.

In other words, $1 and $2 act as safe placeholders – the data 'alice'; DROP TABLE accounts;-- would be bound as a string literal, not as executable code. This approach is strongly recommended: “avoid string concatenating parameters into the query text… (as it) often leads to SQL injection vulnerabilities” (Queries – node-postgres). When using parameterized queries, even complex inputs or quotes are handled correctly by the database driver.

In addition to placeholder binding, always perform input validation on the application side. Ensure that inputs match the expected format and type (e.g., if an ID should be numeric, reject non-numeric input before ever sending them to the database. Parameterization will handle the low-level safety, but validation adds an extra layer of defense and can prevent logically invalid or malicious data from reaching your queries (Preventing SQL Injection Attacks in Postgres | Crunchy Data Blog).

For example, you might enforce that usernames contain only alphanumeric characters or use an allowlist (whitelist) of acceptable values for certain inputs. As a second layer, you can also filter out or disallow obviously dangerous substrings like — or ; in inputs that should never contain them, though relying solely on blacklisting bad patterns is not foolproof. The primary defense should remain using placeholders or ORM query builders that do so automatically.

Avoiding Dynamic SQL Injection

Most SQL injection in applications comes from improperly concatenating strings in application code but be aware that similar risks exist inside the database if you construct dynamic SQL in server-side functions. PostgreSQL functions in PL/pgSQL or other languages can execute dynamic commands (via EXECUTE in PL/pgSQL).

If these commands incorporate user input, you must escape or quote it properly using functions like quote_literal() for values and quote_ident() for identifiers or use PL/pgSQL’s USING clause to bind variables in EXECUTE.

The PostgreSQL documentation warns: “to prevent SQL injection attacks, SQL identifiers must be escaped when they are received from an untrustworthy source.” (PostgreSQL: Documentation: 17: 32.3. Command Execution Functions) This is the server-side equivalent of parameterizing queries. In short, whether in application code or in a PostgreSQL function, never directly interpolate user-provided strings into SQL commands.

Key Takeaways to Prevent Injection

By diligently coding with safe query practices and input validation, you can practically eliminate SQL injection risk in PostgreSQL. The next step is ensuring that the database itself is configured securely, so that even a misconfigured server does not undermine the best application code.

Hardening PostgreSQL Configuration (Avoiding Misconfiguration)

Misconfiguration of PostgreSQL can create security holes even if your application is written securely. “Misconfigurations in PostgreSQL can serve as an entry point for attackers” (Securing PostgreSQL from Cryptojacking Campaigns in Kubernetes), especially if they allow unauthorized connections or weak authentication.

In this section we discuss common PostgreSQL configuration pitfalls – particularly in the postgresql.conf and pg_hba.conf files – and how to fix any mistakes you may have made. The focus in this section is on authentication methods, network exposure, and other default settings that need tightening.

Secure Authentication & pg_hba.conf

PostgreSQL uses the pg_hba.conf (host-based authentication) file to control how clients can connect – which IPs, which users, and what authentication method is required. A common misconfiguration is leaving this too permissive. By default, PostgreSQL (especially older versions) may allow connections using the trust method on certain interfaces, meaning no password is required at all (PostgreSQL Security Hardening: Best Practices to Protect Your Database).

While convenient for initial setup or local testing, trust authentication is dangerously insecure if enabled on any interface that others can access. Using trust is “the worst thing you can do” on a production server (PostgreSQL Security: 12 rules for database hardening). An attacker who can reach the database port can instantly log in without credentials if a trust rule covers their connection.

Disable Trust

You should never use trust for remote connections, and even for local connections it’s better to use more secure methods (like peer for local sockets or a password method). Instead, configure pg_hba.conf to use strong password-based authentication (preferably SCRAM-SHA-256) or other methods like certificate authentication for all users. For example, a secure pg_hba.conf entry might look like:

This ensures a password (hashed with SCRAM) is always needed. Avoid catch-all lines like host all all 0.0.0.0/0 trust – that would allow anyone from anywhere in the world to connect with no password! In fact, avoid using 0.0.0.0/0 (all IPs) in pg_hba.conf whenever possible. It’s much safer to restrict access to specific IP ranges or trusted networks that your application servers reside in (PostgreSQL Security Hardening: Best Practices to Protect Your Database). Similarly, avoid overly broad user or database specifications; lock it down to the minimum needed (e.g., if only one database and a couple of roles are used by the app, specify those rather than “all”). Each pg_hba.conf line should be as specific as practical about the connection it permits.

Enforce adequate authentication to servers

Enforce strong passwords and authentication methods. PostgreSQL supports multiple auth methods: the older MD5-based challenge and the newer SCRAM-SHA-256 (as of PostgreSQL 10+). MD5 is considered weak by today’s standards – it’s vulnerable to hash cracking if an attacker intercepts the hash, especially since many common MD5-hashed passwords can be brute-forced or looked up.

SCRAM-SHA-256 is a much stronger mechanism and is recommended for password authentication (PostgreSQL Security Hardening: Best Practices to Protect Your Database). Ensure your PostgreSQL is configured to use SCRAM (set password_encryption = scram-sha-256 in postgresql.conf so new passwords use SCRAM by default) and update any old user passwords still stored with MD5. In pg_hba.conf, use scram-sha-256 as the method (or md5 if you must support older clients, but note it will allow MD5 or SCRAM depending on user password type).

The bottom line: do not leave weak or no-auth pathways open. Every connection should require proper credentials or certificates.

Network Exposure – listen_addresses and Firewalls:

Another common misconfiguration is exposing the database to the entire network (or Internet) when unnecessary. The listen_addresses parameter in postgresql.conf controls which network interfaces PostgreSQL listens on. This is often localhost only by default, but sometimes admins set it to * (all interfaces) for convenience.

Listening on all interfaces is acceptable only if you have properly restricted pg_hba.conf and external firewall rules; otherwise, it enlarges your attack surface. As a security hardening rule: don’t open PostgreSQL to more networks than needed.

If your application runs on the same machine or a small set of hosts, restrict listen_addresses to those (e.g. listen_addresses = 'localhost,10.0.0.5' for a specific app server) (PostgreSQL Security: 12 rules for database hardening). If you listen on * and have a misconfigured pg_hba line, a bad actor could easily spam you with authentication requests – disaster is just one pg_hba.conf entry away. In essence, limiting the network exposure gives you an extra safety net: PostgreSQL won’t even see connection attempts from unauthorized sources.

Bottom Line: Always use a firewall to block the PostgreSQL port (5432 by default) from any IPs that don’t need to reach it.

Require SSL/TLS for Connections

By default, PostgreSQL allows unencrypted connections, which could let an attacker snoop on data in transit (especially on a public network). Enabling SSL/TLS in PostgreSQL ensures encrypted traffic between the client and server. This prevents eavesdropping or man-in-the-middle attacks that could steal credentials or data. On the server, set ssl = on (and configure the certificate files), and in pg_hba.conf, you can use the hostssl directive to require SSL for certain connections. For example:

This entry means non-SSL connection attempts will be rejected for that rule (the client must use TLS). If you’re in an environment where clients should always use encryption (which is, in most cases, in production), you might consider setting sslmode=require or sslmode=verify-full on the client side as well.

Unencrypted connections in a local trusted network might be acceptable, but as a rule, encrypt data in transit, especially if any part of the connection goes over unsecured networks (PostgreSQL Security Hardening: Best Practices to Protect Your Database).

Other Configuration Best Practices

There are a few more settings and practices to keep in mind:

Hardening authentication and network settings drastically reduces the chance of unauthorized access. But even authorized users (or attackers who get in) should be limited in what they can do.

Preventing Privilege Escalation in PostgreSQL

Privilege escalation in PostgreSQL refers to a scenario where a user gains privileges beyond what was intended – for example, a low-privileged user managing to obtain superuser rights or using a misconfiguration to perform actions as a more privileged role. PostgreSQL’s role-based access control is very flexible, but it can be abused with missteps. We will look at risks around the SUPERUSER role, role grants, and the use of functions that can act with elevated privileges.

The SUPERUSER Role – Handle with Extreme Care

In PostgreSQL, a superuser is all-powerful – it “bypasses all permission checks” (PostgreSQL: Documentation: 17: 21.2. Role Attributes). This is analogous to a root user in Linux.

Suppose an attacker can make themselves a superuser. In that case, the game is over: they can drop or alter any data, create functions to read/write the server’s filesystem, or even execute shell commands via extensions or COPY commands. Therefore, keeping the superuser role closely guarded is the number one rule.

According to PostgreSQL’s documentation, superuser is a “dangerous privilege and should not be used carelessly; it is best to do most of your work as a role that is not a superuser.” (PostgreSQL: Documentation: 17: 21.2. Role Attributes) In practice, this means not running your application or routine queries as a superuser.

The application should connect with a lesser role that has only the rights it needs (e.g., read/write to specific tables). The superuser account (like postgres) should be reserved for administrative tasks and seldom used, especially not exposed to end-users or applications. By limiting the usage of superuser, you greatly reduce the risk that an SQL injection (or other bug) in the app can perform irreparable harm. For instance, if an app runs as a role that cannot DROP TABLE or cannot create new roles, then even if compromised, those actions are off-limits.

Role Privileges and Grants

Beyond the superuser, PostgreSQL roles can have other powerful attributes like CREATEROLE (to create different users), CREATEDB (to create databases), or membership in other roles. Mismanagement here can lead to privilege escalation. A classic mistake is granting a role more rights than necessary or misusing the PUBLIC role. By default, every PostgreSQL role is a member of a pseudo-role PUBLIC, which automatically grants some rights to everyone (for example, CONNECT on all databases and USAGE on the public schema).

In older PostgreSQL versions (before 15), a newly created user could connect to any database and even create objects in the public schema of that database by default (PostgreSQL Security: 12 rules for database hardening). This means if you create a user for one database, they might still poke around in another database (though they can’t read data they don’t have privileges for, they could create temp objects or attempt certain operations).

More worryingly, if a malicious user can create objects in a schema that some higher-privileged user later uses, it opens the door to sneaky attacks. It has been observed that most of the privilege escalation attack vectors in PostgreSQL over the years work by creating malicious objects in a database. For example, an attacker with the ability to create a function in a schema might create a Trojan-horse function (or operator) that a superuser inadvertently calls, thereby executing arbitrary SQL as the superuser.

Principle of Least Privilege

The principle of least privilege says each role should only have the permissions it requires. Do not grant broad privileges or make every role a member of an admin role. Start by revoking unnecessary default grants.

It’s a good practice to revoke CONNECT on databases and CREATE on a public schema from PUBLIC and then explicitly grant access to roles that need it (PostgreSQL Security: 12 rules for database hardening). For instance, if you have a database appdb that only the role appuser and admins should use, you can do:

The above ensures that random roles can’t connect to appdb or create objects in its public schema. Next, avoid giving out roles like CREATEROLE or CREATEDB to users that don’t absolutely need them. A user with CREATEROLE could create new roles (though they cannot make them superusers without being superusers, they could create roles with wider grants or passwords and potentially phish an admin to use those). A user with CREATEDB could create a new database and potentially attempt to escalate by creating extensions or configuration objects (note that creating most extensions or setting certain parameters still requires a superuser, but it’s best not to give that chance unnecessarily).

Regularly review role memberships as well. If you grant roles to other roles (group roles), ensure no chain unintentionally gives a regular user an indirect path to higher privileges. For example, if role A is an admin and role B is a member of A, then anyone who can assume role B effectively gets A’s privileges (SET ROLE in SQL).

Keep role inheritance and membership simple and tightly controlled.

Security Definer Functions and Privilege Elevation

PostgreSQL allows you to create functions that run with the privileges of the function’s owner rather than the caller. These are declared with SECURITY DEFINER. They are useful for allowing lower-privileged users to perform specific higher-privileged actions carefully. However, if not written safely, they can be abused to escalate privileges.

The danger is that a malicious user might trick a SECURITY DEFINER function (owned by a superuser) into executing something unintended on their behalf. The PostgreSQL manual warns: “care is needed to ensure that the [security definer] function cannot be misused. For security, search_path should be set to exclude any schemas writable by untrusted users.

This prevents malicious users from creating objects (e.g., tables, functions, operators) that mask objects intended to be used by the function.” (Abusing SECURITY DEFINER functions in PostgreSQL). In simpler terms, if you create a SECURITY DEFINER function, you should hard-code the search_path (or qualify object names) so that an attacker cannot create a fake table or function in a schema that comes earlier in the path and have your definer function call that instead.

Additionally, carefully validate any inputs to security-definer functions – do not pass user input to EXECUTE without thorough sanitization. Ideally, keep the logic simple and avoid dynamic SQL in such functions. And of course, restrict who can execute the function: use GRANT EXECUTE ON FUNCTION to limit it to the roles that truly need it.

If you follow the above practices, the need for SECURITY DEFINER functions can be minimized. But when you do use them, they should be short, audited, and safe. Another tip: from PostgreSQL v15 onward, new databases no longer grant CREATE on the public schema to PUBLIC by default (closing one avenue of attack with malicious objects).

But if you’re on older versions or upgraded from them, you may need to revoke those privileges manually, as shown earlier. Keep an eye on PostgreSQL release notes for security-related changes like this.

Practical Tips to Prevent Privilege Escalation

This is a big concern, and all of these tips definitely should be in your thought when you are setting up and securing your PostgreSQL servers.

Conclusion

PostgreSQL is a secure database by design, but it relies on us engineers and DBAs to configure and use it securely. By following the best practices outlined – from writing safe SQL in our applications to hardening config files and managing roles wisely – we significantly reduce the risk of a security incident. Remember, security is not one time activity it’s an ongoing process, keep your PostgreSQL server patched with the latest security update, regularly review configurations as your infrastructure evolves, and stay informed about new vulnerabilities or recommendations from the PostgreSQL community. With a vigilant approach, you can enjoy PostgreSQL’s powerful features while keeping your data safe from injection attacks, misconfiguration leaks, and privilege abuse.