PostgreSQL

Wrapping psql with shellfirm protects your PostgreSQL sessions from accidental destructive queries. Safe queries like SELECT execute normally, while dangerous operations like DROP TABLE or TRUNCATE are intercepted.

Usage

shellfirm wrap psql -h prod.db.com -U analyst -d myapp

This starts a normal psql session with shellfirm checking every SQL statement before it executes.

Example session

psql session (wrapped)

-- Safe queries work normally:

myapp=> SELECT count(*) FROM users;

count

-------

14523

(1 row)

-- Dangerous query is intercepted:

myapp=> DROP TABLE users;

============ RISKY COMMAND DETECTED ============

Severity: CRITICAL

Description: DROP TABLE permanently deletes the table and all its data

Alternative: Use SELECT before DROP to verify the table

(Lets you confirm you are targeting the right table before dropping it.)

? Solve the challenge:: 8 + 4 = ? Esc to cancel ›

-- If you cancel (Esc), the query is NOT executed.

-- If you solve the challenge, the query proceeds.

What gets intercepted

shellfirm checks these PostgreSQL-specific patterns (from the psql and database check groups):

Pattern	Severity	Description
`DROP TABLE`	Critical	Permanently deletes a table and all data
`DROP DATABASE`	Critical	Deletes the entire database
`TRUNCATE`	High	Removes all rows from a table
`DELETE FROM` (without WHERE)	High	Deletes all rows when no WHERE clause
`ALTER TABLE ... DROP`	Medium	Drops a column from a table
`DROP INDEX`	Medium	Removes an index
`DROP SCHEMA`	Critical	Drops an entire schema
`GRANT ALL`	Medium	Grants broad permissions

Configuration

Recommended settings for psql

# ~/.shellfirm/settings.yaml
wrappers:
  tools:
    psql:
      delimiter: ";"
      check_groups:
        - psql
        - database

Using specific check groups

By default, shellfirm uses all enabled check groups. For psql sessions, you may want to limit checks to database-related patterns only:

wrappers:
  tools:
    psql:
      check_groups:
        - psql
        - database

This avoids false positives from filesystem or git patterns matching SQL strings.

Multi-line queries

shellfirm buffers input until the statement delimiter (;) is found, so multi-line queries are fully supported:

Multi-line Query Interception

myapp=> DROP TABLE

myapp-> IF EXISTS

myapp-> users

myapp-> CASCADE;

============ RISKY COMMAND DETECTED ============

-- shellfirm intercepts the complete statement

Connecting with environment variables

All standard PostgreSQL connection methods work:

# Using connection string
shellfirm wrap psql "postgresql://user:pass@prod.db.com/myapp"

# Using environment variables
export PGHOST=prod.db.com
export PGUSER=analyst
export PGDATABASE=myapp
shellfirm wrap psql

# Using .pgpass
shellfirm wrap psql -h prod.db.com -U analyst

Team policy example

For a team that wants strict database protection:

# .shellfirm.yaml
version: 1
deny:
  - "psql:drop_database"
  - "database:drop_database"

overrides:
  - id: "psql:drop_table"
    challenge: Yes
  - id: "psql:truncate"
    challenge: Yes