Database User Privileges

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_integration.py
• tests/test_state.py
• tests/test_backwards_compat.py

Database user privileges in the meltano-state-backend-postgresql project define the level of access required by the configured PostgreSQL user to manage Meltano state data. These privileges ensure the backend can initialize the necessary schema objects, perform CRUD operations on state records, and handle concurrency via locking mechanisms.

The system utilizes a dedicated PostgreSQL connection to interact with a state table (defaulting to state). The user must have sufficient permissions not only for data manipulation but also for DDL operations to support automatic table creation and maintenance.

Sources: README.md:58-60, src/meltano_state_backend_postgresql/backend.py:175-186

Required Privileges for Operations

The PostgreSQLStateStoreManager performs various database operations that map to specific PostgreSQL privileges. The following table summarizes the required permissions based on the methods implemented in the backend.

Operation Category	Specific Privileges	Backend Methods
Schema Management	CREATE on Schema	_ensure_tables
Table Management	CREATE TABLE	_ensure_tables
Data Manipulation	SELECT, INSERT, UPDATE, DELETE	get, set, delete, get_state_ids
Administrative	TRUNCATE	clear_all
Concurrency Control	Execution of pg_try_advisory_lock, pg_advisory_unlock	acquire_lock

Sources: README.md:60, src/meltano_state_backend_postgresql/backend.py:177-270, src/meltano_state_backend_postgresql/backend.py:328-360

Initialization and DDL Privileges

Upon initialization, the backend calls _ensure_tables(), which executes a CREATE TABLE IF NOT EXISTS statement. This requires the database user to have CREATE privileges on the target schema (either the default search_path or a specific schema provided via configuration).

Data Access and Manipulation

The backend requires standard Data Manipulation Language (DML) privileges to manage MeltanoState objects. The set method utilizes ON CONFLICT (state_id) DO UPDATE, requiring both INSERT and UPDATE permissions. The clear_all method uses the TRUNCATE command, which is more performant than a bulk delete but requires higher privileges than simple row deletion.

Sources: src/meltano_state_backend_postgresql/backend.py:202-215, src/meltano_state_backend_postgresql/backend.py:255-270

Connection and Security Configuration

Privileges are exercised over a connection established using the psycopg library. Security-related settings like sslmode influence how these credentials and commands are transmitted.

Configuration Parameters

Users provide credentials and connection details through specific settings or a unified URI.

Setting	Environment Variable	Description
user	MELTANO_STATE_BACKEND_POSTGRESQL_USER	Username for authentication.
password	MELTANO_STATE_BACKEND_POSTGRESQL_PASSWORD	Password for authentication (sensitive).
sslmode	MELTANO_STATE_BACKEND_POSTGRESQL_SSLMODE	SSL security level (e.g., require, verify-full).
schema	MELTANO_STATE_BACKEND_POSTGRESQL_SCHEMA	Target schema for state tables.

Sources: src/meltano_state_backend_postgresql/backend.py:61-103, pyproject.toml:29-37

Concurrency and Advisory Locks

The backend implements a locking mechanism to prevent simultaneous state updates. This uses PostgreSQL advisory locks via the acquire_lock method. The user must have permission to call the following built-in functions:

• pg_try_advisory_lock(key)
• pg_advisory_unlock(key)

The lock key is derived from a 64-bit signed integer hash of the state_id.

Summary of Permissions

To ensure full functionality of the meltano-state-backend-postgresql extension, the database user should be granted the following on the specific schema and state table:

1. Usage/Create: Permission to use the schema and create tables within it.
2. CRUD: Permission to SELECT, INSERT, UPDATE, and DELETE records in the state table.
3. Truncate: Permission to TRUNCATE the table for total state clearing.
4. Advisory Locks: Permission to use session-level advisory lock functions.

Sources: README.md:60, src/meltano_state_backend_postgresql/backend.py:175-360