SSL and Security

Relevant source files

The following files were used as context for generating this wiki page:

• README.md
• src/meltano_state_backend_postgresql/backend.py
• pyproject.toml
• tests/test_conninfo.py
• tests/test_meltano.py
• fixtures/project/meltano.yml

SSL and security within the meltano-state-backend-postgresql extension focus on protecting the transmission and storage of Meltano state data. This is achieved through configurable Secure Sockets Layer (SSL) modes for database connections and secure credential management using environment variables and Meltano's settings framework. Sources: README.md:65-71, src/meltano_state_backend_postgresql/backend.py:92-99

The security architecture relies on the underlying psycopg library to enforce transport-layer security while providing granular control over how certificates and connection attempts are validated against the PostgreSQL server. Sources: pyproject.toml:28, src/meltano_state_backend_postgresql/backend.py:112-132

SSL Configuration

The backend supports standard PostgreSQL sslmode parameters to define the level of security required for the connection. By default, the system uses prefer, which attempts an SSL connection but falls back to unencrypted communication if the server does not support it. Sources: src/meltano_state_backend_postgresql/backend.py:98, src/meltano_state_backend_postgresql/backend.py:132

SSL Modes Table

The following table describes the supported SSL modes and their behavior as defined in the project settings and configuration.

Mode	Description	Security Level
disable	No SSL connection attempt is made.	Low (Plaintext)
allow	Try non-SSL first; then try SSL if failed.	Low
prefer	Try SSL first (default); fall back to non-SSL.	Medium
require	Only allow SSL connections.	High
verify-ca	SSL required; verify server CA certificate.	Very High
verify-full	SSL required; verify CA and server hostname.	Maximum

Sources: README.md:58, src/meltano_state_backend_postgresql/backend.py:92-99

Connection Flow and SSL Validation

When a PostgreSQLStateStoreManager is initialized, it processes the connection URI and individual settings to form a conninfo string. This string includes the specified sslmode which is then passed to the database driver.

Credential Management

Security for the PostgreSQL state backend is heavily dependent on the protection of authentication credentials. The system distinguishes between public settings (like host and port) and sensitive settings (like passwords).

Sensitive Data Handling

The POSTGRESQL_PASSWORD setting is explicitly marked as sensitive=True. This metadata ensures that Meltano handles the value securely, often preventing accidental exposure in logs or UI displays. Sources: src/meltano_state_backend_postgresql/backend.py:75-82

Security Recommendations

1. Environment Variables: Sensitive values like MELTANO_STATE_BACKEND_POSTGRESQL_PASSWORD should be set via environment variables rather than hardcoded in meltano.yml. Sources: README.md:65-71, tests/test_meltano.py:53-57
2. Privilege Isolation: The PostgreSQL user should only be granted the minimum necessary privileges: CREATE TABLE, INSERT, UPDATE, DELETE, and SELECT on the specific schema used for state storage. Sources: README.md:63-64

Data Integrity and Isolation

Security also encompasses the isolation of state data between different environments or projects using PostgreSQL schemas and table-level identifiers.

Schema Isolation

Users can specify a specific schema via the schema setting or the search_path option in the URI. This prevents state collisions and provides a layer of logical separation. Sources: src/meltano_state_backend_postgresql/backend.py:84-90, README.md:30-35

Advisory Locking Security

To prevent concurrent modifications and race conditions that could lead to state corruption, the backend utilizes PostgreSQL advisory locks. The state_id is converted into a deterministic 64-bit integer using an MD5 hash to create a unique lock key. Sources: src/meltano_state_backend_postgresql/backend.py:352-370

# src/meltano_state_backend_postgresql/backend.py:352-363
@staticmethod
def _state_id_to_lock_key(state_id: str) -> int:
    """Convert a state_id to a PostgreSQL advisory lock key."""
    # Use MD5 for deterministic hashing, take first 8 bytes as signed int64
    hash_bytes = hashlib.md5(state_id.encode()).digest()[:8]
    return int.from_bytes(hash_bytes, byteorder="big", signed=True)

Sources: src/meltano_state_backend_postgresql/backend.py:352-363

Summary of Security Configurations

Setting	Environment Variable	Purpose
state_backend.postgresql.user	..._USER	Identity for authentication.
state_backend.postgresql.password	..._PASSWORD	Secret for authentication (Sensitive).
state_backend.postgresql.sslmode	..._SSLMODE	Transport layer encryption level.
state_backend.postgresql.schema	..._SCHEMA	Logical namespace isolation.

Sources: src/meltano_state_backend_postgresql/backend.py:51-110, tests/test_meltano.py:38-76