# PostgreSQL Security: Best Practices and Tools *Published on 2023-10-20* *By Rajkumar Venkatasamy, Dan Draper — CEO and Founder* PostgreSQL security best practices for access control, password management, logging, and encryption — including the modern CipherStash Stack and Proxy paths for adding searchable, application-level encryption to Postgres. ## Content A data breach or unauthorized access can lead to severe consequences, including financial losses, reputational damage, and legal liabilities. With an increasing amount of sensitive information being stored in databases, protecting that data from unauthorized access, theft, or tampering is critical. [PostgreSQL](https://www.postgresql.org/) is one of the most popular open source relational database management systems, and it’s known for its robust security features. However, even with its built-in security mechanisms, mistakes can still happen: [82% of data breaches involve human error](https://www.grcelearning.com/blog/human-error-is-responsible-for-85-of-data-breaches). Misconfiguration, weak passwords, and inadequate access controls are all common pitfalls that can expose a database to potential risks. It’s crucial to implement additional best practices and use suitable tools to ensure a comprehensive and effective PostgreSQL security posture. This article will introduce you to some PostgreSQL security best practices you can implement for access and storage security. Additionally, you’ll learn about specific tools you can use, such as [pgcrypto](https://www.postgresql.org/docs/current/pgcrypto.html), [CipherStash Stack](https://github.com/cipherstash/stack), [PGAudit](https://www.pgaudit.org/), and [ModSecurity](https://github.com/SpiderLabs/ModSecurity), to enhance the security of your PostgreSQL environment. ## Best Practices for PostgreSQL Security Now that you know how crucial data security is, let's dive into some best practices that you can implement to safeguard the integrity and confidentiality of PostgreSQL databases. ### Ensure Proper Access Control Proper access control (who can access the database and what they can do with the data) is fundamental to the security of any database. Thankfully, PostgreSQL provides robust access control mechanisms, such as [roles](https://www.postgresql.org/docs/current/user-manag.html) and [privileges](https://www.postgresql.org/docs/current/ddl-priv.html). By carefully defining roles and granting only necessary privileges to users, you can restrict unauthorized access to sensitive data. For instance, with the help of PostgreSQL commands, you can create a specific role for your organization’s sales team, granting them precise privileges only to the tables they need: ```sql -- Create a new role CREATE ROLE sales_team; -- Allow users to login with the new role ALTER ROLE sales_team LOGIN; -- Grant SELECT privilege on the products and orders table to the sales_team role GRANT SELECT ON products TO sales_team; GRANT SELECT ON orders TO sales_team; ``` Additionally, you'll need to regularly review and audit roles and their privileges to ensure that only the necessary access is granted. This can help prevent unnecessary access to confidential data. To check what privileges are granted to a specific role or user in PostgreSQL, you can use the following SQL command: ```sql -- Replace 'sales_team' with the actual name of the role or user you want to check SELECT grantee, privilege_type, table_catalog, table_schema, table_name, is_grantable FROM information_schema.role_table_grants WHERE grantee = 'sales_team'; ``` Make sure you remove unnecessary or outdated permissions from the database to minimize the attack surface, like this: ```sql -- Revoke SELECT privilege on an imaginary table named sensitive_data from a role REVOKE SELECT ON sensitive_data FROM temporary_contractors; ``` You can also enhance PostgreSQL security by restricting access to specific IP addresses or ranges. This prevents unauthorized access to the database from external or unexpected sources. To do so, you can modify the PostgreSQL server's `pg_hba.conf` configuration file. More information on how to restrict access to IP addresses or ranges is available in [this official documentation](https://www.postgresql.org/docs/current/auth-pg-hba-conf.html). ### Use Robust Password Management Features For database administrators (DBAs), safeguarding user credentials within PostgreSQL databases is paramount. Weak or compromised passwords can serve as a gateway for unauthorized access, potentially leading to data breaches and security vulnerabilities. PostgreSQL provides [robust password management features](https://www.postgresql.org/docs/current/auth-password.html) that, when properly utilized, can significantly enhance the overall security posture of your database environment. Next, we’ll explore a few important PostgreSQL password management best practices: #### Enforce Strong Password Policies Implementing stringent password policies is your first line of defense. Encourage users to create complex passwords that combine uppercase and lowercase letters, numbers, and special characters, totaling more than twelve characters. One secure way to set a password is by using the [PSQL Tool](https://www.pgadmin.org/docs/pgadmin4/development/psql_tool.html) or a GUI-based client tool such as [pgAdmin](https://www.pgadmin.org/), where the typed passwords are masked and unavailable to read in plaintext format. You can achieve this via the command line using the PSQL Tool: ```sql SET password_encryption = 'scram-sha-256'; \password ``` You are prompted to enter the password. Enter it, and the PSQL Tool passes the encrypted password in a SQL statement to the PostgreSQL server, keeping it secure. #### Use Password Expiration and Rotation Regularly refreshing user passwords reduces the window of opportunity for potential attackers. Make sure you implement password expiration and require users to change their passwords periodically: ```sql -- Set password expiration for a user ALTER USER user1 VALID UNTIL '2023-12-31'; ``` > **Note:** PostgreSQL does not update the date provided in the `VALID UNTIL` statement automatically. ### Master the Art of PostgreSQL Logs PostgreSQL logging offers insights into database activities, aiding troubleshooting and performance optimization. However, these logs can inadvertently expose sensitive data if not configured correctly, potentially becoming an Achilles' heel for PostgreSQL security. For instance, incorrect log configurations like the following one can lead to sensitive data showing up in PostgreSQL logs: ``` log_statement = 'all' ``` Setting `log_statement` to `'all'` logs all SQL statements, including sensitive data, such as passwords or personal information, leading to the exposure of confidential data. Mastering the art of PostgreSQL logging is crucial in maintaining a delicate equilibrium between transparency and safeguarding confidential information. Following are a few essential practices for effective PostgreSQL logging while mitigating the risk of sensitive data exposure: ### Configure Log Settings Fine-tuning log settings is your initial line of defense. By customizing what gets logged and how it's presented, you can maximize insights while minimizing exposure: ``` # Set log format and level in postgresql.conf log_statement = 'none' log_line_prefix = 'time=%t, pid=%p %q db=%d, usr=%u, client=%h , app=%a, line=%l ' ``` In this code, `log_statement` is set to `'none'`, which means that no SQL statements are included in the logs. This is useful for preventing sensitive information, such as passwords or specific query details, from appearing in the logs. `log_statement` enhances PostgreSQL security by minimizing the exposure of potentially confidential data. For more information on PostgreSQL logging and the use of config tokens used in the `log_line_prefix, configuration, check out [the official documentation](https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LINE-PREFIX). ### Store Logs Securely Another vital step when working with PostgreSQL logs is to make sure you choose a secure location with appropriate access control to store your log files. Regularly rotate and archive logs to prevent unauthorized access and maintain compliance: ``` # Specify secure log location and rotation settings log_directory = '/var/log/postgresql/' log_filename = 'postgresql.log' log_rotation_age = 1d log_rotation_size = 0 ``` In this code block, `log_rotation_age = 1d` specifies the maximum age of a log file before it's rotated. This means that a new log file is created to continue logging. The `1d` value indicates a rotation interval of one day. `log_rotation_size = 0` disables size-based log rotation. When `log_rotation_size` is set to `0`, log rotation based on file size is effectively turned off. Log files are not rotated automatically once their size reaches a certain threshold. Instead, log files continue to grow without being split into smaller segments due to size limitations. By strategically configuring log settings and ensuring secure log storage, you can optimize PostgreSQL logging for operations while fortifying data security. ### Encrypt Sensitive/Confidential Data PostgreSQL encryption involves transforming plain data into ciphertext, ensuring that even if unauthorized parties gain access to data, they can't decipher its contents without the necessary decryption key. The [PostgreSQL pgcrypto extension](https://www.postgresql.org/docs/current/pgcrypto.html) offers cryptographic functions for data encryption and decryption within PostgreSQL databases. It provides a range of algorithms and methods, including symmetric encryption for data-at-rest protection. While pgcrypto is convenient, it's [considered to have poor security for critical use](https://www.percona.com/sites/default/files/presentations/percona-tech-day-2020-04.pdf), in part due to its use of server-side encryption. #### Challenges with pgcrypto and server-side encryption pgcrypto performs PostgreSQL encryption and decryption on the server side, meaning that the encryption keys reside on the server. This presents a major risk: if the server is compromised, the keys could be exposed, allowing attackers to decrypt encrypted data. For instance, in the following example, the pgcrypto extension encrypts and decrypts Social Security number (SSN) data using the `pgp_sym_encrypt` and `pgp_sym_decrypt` functions. However, the encryption key is used and managed on the server, which exposes the key to potential compromise if the server is breached: ```sql -- Create a table with encrypted column CREATE EXTENSION IF NOT EXISTS pgcrypto; CREATE TABLE sensitive_data ( id serial PRIMARY KEY, name text, ssn bytea -- Use 'bytea' for binary data); -- Insert data with server-side encryption INSERT INTO sensitive_data (name, ssn) VALUES ('Alice', pgp_sym_encrypt('123-45-6789', 'encryption_key')); -- Select data from sensitive_data table to view the encrypted ssn value SELECT * from sensitive_data ; ``` Although the SSN information of the user Alice is encrypted in the table, if an unauthorized user were to gain access to the server-side key they could reveal the password in plaintext: ```sql -- Decrypt data (happens on the server) SELECT name, pgp_sym_decrypt(ssn, 'encryption_key') AS decrypted_ssn FROM sensitive_data; ``` While encryption as explained above does improve PostgreSQL security to certain extent, on the other hand, it means that to perform operations like searching or ordering on encrypted data, you would need to use decryption functions like `pgp_sym_decrypt` in your SQL queries. However, this can be computationally expensive and might result in slower query performance, especially if you have a large amount of data to decrypt and process. ### CipherStash Stack: searchable, application-side encryption To overcome `pgcrypto`'s limitations, [CipherStash Stack](https://github.com/cipherstash/stack) takes a different approach to encryption with three properties pgcrypto cannot offer at once: **searchable**, **keys outside the database**, and **plaintext never on the server**. Data is encrypted in your application before it reaches Postgres. The values stored in the database are ciphertext plus encrypted index structures — bloom filters for free-text search, HMACs for equality, Order-Revealing-Encryption blocks for ranges — that Postgres' planner uses the way it uses any other index. The plaintext key never leaves your application's process boundary; per-value keys are derived on demand from a root key held in your own AWS KMS account, brokered by [ZeroKMS](https://cipherstash.com/docs/stack/cipherstash/kms). A minimal end-to-end setup looks like this: ```sql -- 1. Encrypted columns use the eql_v2_encrypted type CREATE TABLE users ( id SERIAL PRIMARY KEY, email eql_v2_encrypted NOT NULL, ssn eql_v2_encrypted NOT NULL ); -- 2. An index per query pattern you actually use CREATE INDEX idx_users_email_eq ON users USING HASH (eql_v2.hmac_256(email)); CREATE INDEX idx_users_ssn_eq ON users USING HASH (eql_v2.hmac_256(ssn)); ``` ```ts // 3. Describe what's encrypted in TypeScript — the source of truth import { encryptedTable, encryptedColumn } from '@cipherstash/stack/schema' import { Encryption } from '@cipherstash/stack' const users = encryptedTable('users', { email: encryptedColumn('email').equality(), ssn: encryptedColumn('ssn').equality(), }) // 4. Initialise the client — keys come from your AWS KMS via ZeroKMS, // not from the database server. const encryption = await Encryption({ schemas: [users] }) // 5. Insert + query — plaintext never enters Postgres const encrypted = await encryption.encrypt('alice@example.com', { table: users, column: users.email, }) await db.query('INSERT INTO users (email) VALUES ($1::jsonb)', [encrypted.data]) const term = await encryption.encryptQuery('alice@example.com', { table: users, column: users.email, queryType: 'equality', }) const result = await db.query( 'SELECT * FROM users WHERE email = $1::eql_v2_encrypted', [term.data], ) const decrypted = await encryption.bulkDecryptModels(result.rows) ``` A `pg_dump` of this table contains only ciphertext and opaque encrypted-index structures. There are no plaintext values, no shared deterministic ciphertext between users, and no key material — a stolen snapshot, an over-privileged role, or a leaked SQL log all see the same thing. The full walkthrough — including free-text search and range queries — is in [Searchable encryption in Postgres: a working guide with CipherStash Stack and EQL](/blog/searchable-encryption-in-postgres). > **A note on previous versions.** If you've seen older guides referring to `@cipherstash/protectjs` or `@cipherstash/jseql`, those are predecessor libraries that Stack supersedes — the surface area above (`@cipherstash/stack`, `encryptedTable`, `Encryption`) is the recommended one for new code. ### CipherStash Proxy: encryption for an existing Postgres without code changes The Stack SDK above is the right path when you control the application. For a different situation — an existing Postgres database whose application you can't easily change, an analytics tool, or a third-party BI client connecting over the wire — [CipherStash Proxy](https://github.com/cipherstash/proxy) sits in front of Postgres and transparently encrypts and decrypts at the connection layer. It speaks the Postgres wire protocol on both sides, so a client connects to it the same way it would connect to Postgres directly. Encrypted columns are configured in the Proxy rather than the application; queries with predicates against those columns are rewritten to run over the encrypted indexes before they reach Postgres, and results are decrypted on the way back. The same `eql_v2_encrypted` column types and indexes are used either way — the choice between Stack and Proxy is about *where* the encryption happens (in your app, or in front of your database), not what ends up stored. ### Regularly Backup Your PostgreSQL Database Regularly back up your PostgreSQL database to ensure data availability and disaster recovery in case of data loss or corruption. Additionally, secure your backups to prevent unauthorized access to sensitive data. This can be achieved by limiting access to backup files only to authorized personnel. Set strict file permissions and ownership to ensure that only designated individuals can read or modify backup data: ```sh # Restrict file permissions to owner only chmod 600 backup_file.dump ``` In this example, the `chmod` command sets the file permissions for `backup_file.dump` to allow read and write access for the file owner and denies access to all other users. For improved PostgreSQL security, the backups should be encrypted immediately. For example: ```sh export BACKUP_PASSWORD="your_password_here" pg_dump -d -h localhost | openssl enc -aes-256-cbc -salt -pass env:BACKUP_PASSWORD -out dbdump.sql.encrypted ``` In addition, make sure you store encrypted backup files in a storage service that supports client-side encryption. This ensures that even if you store your backup in the cloud, the cloud provider cannot access your backup data without the decryption key (which resides outside the cloud provider's systems).  ### Stay Up-to-Date with PostgreSQL Releases Lastly, stay up-to-date with [new PostgreSQL releases](https://www.postgresql.org/download/). Database updates often include security patches and bug fixes that protect against known vulnerabilities. Neglecting updates can leave your database vulnerable to known exploits and security risks. ## Enhancing PostgreSQL Security with Searchable Encryption [CipherStash Stack](https://github.com/cipherstash/stack) brings searchable encryption to Postgres without giving up the queries you already write. Data is encrypted by the application before it reaches the database; encrypted indexes let Postgres' planner answer equality, free-text and range predicates over ciphertext; the plaintext key never enters the server. Searchable encryption — also known as encryption-in-use — is the property that data can be searched and processed while it remains encrypted. Search terms are encrypted in the application alongside the values themselves, then matched against encrypted indexes by the database. Sensitive data is stored and queried without ever revealing it in plaintext to Postgres, its logs, its backups, or anyone with a read replica. Combined with [CipherStash Proxy](https://github.com/cipherstash/proxy) at the connection layer and [ZeroKMS](https://cipherstash.com/docs/stack/cipherstash/kms) holding keys outside the database, this gives you a defence-in-depth posture that doesn't trade away query performance: data at rest is encrypted, data in transit is encrypted, the encryption key never reaches the server, and an attacker reading the database files sees only ciphertext. ### PGAudit [PGAudit](https://www.pgaudit.org/) is an open-source tool extension that further improves PostgreSQL security with comprehensive auditing features that operate at both the database session level and for specific, identified groups of database objects. It allows administrators to track user actions and identify anomalies or unauthorized activities in real time. The audit information is available in the PostgreSQL server logs, and with the help of [PGAudit Analyze](https://github.com/pgaudit/pgaudit_analyze), the audited information can be loaded into a database for analysis. ## Conclusion PostgreSQL is a robust and secure database management system, but it requires proper configuration to ensure data security. Neglecting PostgreSQL security can have severe consequences, including data breaches and financial losses. By following best practices, such as access control, strong passwords, and regular audits, you can significantly reduce the risk of data breaches. Additionally, encryption is a vital aspect of securing data in PostgreSQL. `pgcrypto` is available out of the box for basic at-rest needs; for data you also need to *search*, [CipherStash Stack](https://github.com/cipherstash/stack) (in your application) and [CipherStash Proxy](https://github.com/cipherstash/proxy) (in front of your database) provide searchable, application-side encryption with keys held outside the database in ZeroKMS. Either path adds a layer of defence that survives a compromised server. Explore [CipherStash Stack](https://github.com/cipherstash/stack) today to add searchable, application-side encryption to your Postgres database — or run [CipherStash Proxy](https://github.com/cipherstash/proxy) in front of an existing Postgres with no application-code changes. ## Related blog posts - [Introducing @cipherstash/stack](https://cipherstash.com/blog/introducing-cipherstash-stack.md) — Building blocks for Data Level Access Control in TypeScript - [Searchable encryption in Postgres: a working guide with CipherStash Stack and EQL](https://cipherstash.com/blog/searchable-encryption-in-postgres.md) — Encrypt your data, keep the queries. A practical, indexable approach to searchable encryption in Postgres — 410,000× faster than fully homomorphic encryption — with a working code example using @cipherstash/stack and EQL. - [Encryption in use with PostgreSQL](https://cipherstash.com/blog/encryption-in-use-with-postgresql.md) — Don't just rely on encryption at rest and in transit to protect your sensitive data. Use searchable encryption to enable encryption in use to harden data privacy in Postgres.