### CI/CD Integration Example for pgspot

Source: https://context7.com/timescale/pgspot/llms.txt

This bash script provides a template for integrating pgspot into a Continuous Integration/Continuous Deployment pipeline. It sets up the script to scan PostgreSQL migration files for security vulnerabilities.

```bash
#!/bin/bash
# ci-security-check.sh - PostgreSQL extension security scanner

set -e

MIGRATION_DIR="${1:-./migrations}"
EXIT_CODE=0

echo "=== pgspot Security Analysis ==="

```

--------------------------------

### pgspot Error Codes Reference

Source: https://context7.com/timescale/pgspot/llms.txt

This example shows how to programmatically list all available pgspot error and warning codes and their descriptions using Python. It also demonstrates how to get detailed information for a specific code using the command line.

```bash
# View all available codes programmatically
python3 -c "
from pgspot.codes import codes
for code, info in sorted(codes.items()):
    print(f'{code}: {info["title"]}')
"

# Output:
# PS001: Unqualified operator
# PS002: Unsafe function creation
# PS003: SECURITY DEFINER function without explicit search_path
# PS004: SECURITY DEFINER function with insecure search_path
# PS005: Function without explicit search_path
# PS006: Unsafe transform creation
# PS007: Unsafe object creation
# PS008: Unqualified alter sequence
# PS009: Unsafe CASE expression
# PS010: Unsafe schema creation
# PS011: Unsafe sequence creation
# PS012: Unsafe table creation
# PS013: Unsafe foreign server creation
# PS014: Unsafe index creation
# PS015: Unsafe view creation
# PS016: Unqualified function call
# PS017: Unqualified object reference
# PS018: Unsafe SET search_path

# Get detailed info for specific code
pgspot --explain PS009
```

--------------------------------

### Analyze PostgreSQL Extension Script with pgspot

Source: https://context7.com/timescale/pgspot/llms.txt

This example demonstrates a comprehensive analysis of a PostgreSQL extension script using pgspot, highlighting various security checks including function definitions, table creations, and search_path configurations. It also shows how to ignore specific warning codes.

```bash
# Create a sample extension file
cat > myext.sql << 'EOF'
-- Extension setup
CREATE SCHEMA myext;

-- Safe: CREATE OR REPLACE in our own schema
CREATE OR REPLACE FUNCTION myext.helper()
RETURNS TEXT LANGUAGE SQL
SET search_path = pg_catalog, pg_temp
AS $$ SELECT 'helper'; $$

-- Safe: SECURITY DEFINER with proper search_path
CREATE FUNCTION myext.admin_operation()
RETURNS VOID
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
LANGUAGE SQL AS $$
  SELECT pg_catalog.now();
$$

-- Warning: Function without explicit search_path (PS005)
CREATE FUNCTION myext.risky_func()
RETURNS TEXT LANGUAGE SQL AS $$
  SELECT pg_catalog.current_user;
$$

-- Error: Unsafe table in public schema (PS012)
CREATE TABLE IF NOT EXISTS public.myext_data(id int);
EOF

# Analyze the extension
pgspot myext.sql
# Output:
# PS005: Function without explicit search_path: myext.risky_func() at line 18
# PS012: Unsafe table creation: myext_data at line 23
#
# Errors: 1 Warnings: 1 Unknown: 0

# Analyze with specific ignores for known acceptable patterns
pgspot --ignore PS005 myext.sql
# Output:
# PS012: Unsafe table creation: myext_data at line 23
#
# Errors: 1 Warnings: 0 Unknown: 0
```

--------------------------------

### Check SQL for vulnerabilities and ignore specific errors

Source: https://github.com/timescale/pgspot/blob/main/README.md

This example demonstrates how to use pgspot to check a simple SQL statement for vulnerabilities and simultaneously ignore a specific error code (PS017). It shows the output including the detected vulnerability (PS012) and the summary counts.

```bash
> pgspot --ignore PS017 <<<"CREATE TABLE IF NOT EXISTS foo();"
PS012: Unsafe table creation: foo

Errors: 1 Warnings: 0 Unknown: 0

```

--------------------------------

### Detect Unsafe SET search_path (PS018)

Source: https://context7.com/timescale/pgspot/llms.txt

Use pgspot to identify incorrectly quoted search_path settings that can lead to unintended schema resolution. The first example shows an unsafe setting, while the subsequent examples demonstrate correct configurations.

```bash
# PS018 - Misquoted search_path
pgspot <<EOF
SET search_path TO 'pg_catalog, pg_temp';
EOF
# Output: PS018: Unsafe SET search_path at line 1
# The quotes make this a single schema name "pg_catalog, pg_temp" instead of two schemas
```

```bash
# Correct search_path setting
pgspot <<EOF
SET search_path TO pg_catalog, pg_temp;
EOF
# Output: Errors: 0 Warnings: 0 Unknown: 0
```

```bash
# Alternative using set_config
pgspot <<EOF
SELECT pg_catalog.set_config('search_path', 'pg_catalog, pg_temp', false);
EOF
# Output: Errors: 0 Warnings: 0 Unknown: 0
```

--------------------------------

### Python API for pgspot Analysis

Source: https://context7.com/timescale/pgspot/llms.txt

This Python snippet demonstrates how to use the pgspot library programmatically to analyze SQL code. It shows the setup of the State and Counter objects and how to perform analysis on a SQL string, then prints the results.

```python
from pgspot.state import State, Counter
from pgspot.visitors import visit_sql
from argparse import Namespace

# Create argument namespace with default options
args = Namespace(
    summary_only=False,
    ignore=[],
    ignore_lang=[],
    proc_without_search_path=[],
    sql_fn=[],
    plpgsql=True
)

# Create counter and state
counter = Counter(args)
state = State(counter)

# Analyze SQL
sql = """
CREATE OR REPLACE FUNCTION public.unsafe_func()
RETURNS TEXT LANGUAGE SQL AS $$ SELECT now(); $$;
"""

visit_sql(state, sql, toplevel=True)

# Check results
print(f"Errors: {counter.errors}")
print(f"Warnings: {counter.warnings}")
print(f"Is clean: {counter.is_clean()}")

# Output:
# PS002: Unsafe function creation: unsafe_func() at line 1
# PS005: Function without explicit search_path: unsafe_func() at line 1
# PS016: Unqualified function call: now at line 1
# Errors: 0 Warnings: 3 Unknown: 0
# Is clean: False
```

--------------------------------

### Avoid `CREATE SERVER IF NOT EXISTS`

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Using `CREATE SERVER IF NOT EXISTS` is insecure as an attacker may pre-create the server and gain ownership, allowing them to modify its attributes. Use `CREATE SERVER` without `IF NOT EXISTS`.

```sql
CREATE SERVER IF NOT EXISTS s1 FOREIGN DATA WRAPPER postgres_fdw;
```

```sql
CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw;
```

--------------------------------

### Using pg_catalog.set_config for search_path

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Use the set_config function as an alternative method to safely set the search_path.

```sql
SELECT pg_catalog.set_config('search_path','pg_catalog, pg_temp', false);
```

--------------------------------

### Explain error and warning codes

Source: https://context7.com/timescale/pgspot/llms.txt

Use the --explain flag to retrieve detailed documentation and mitigation strategies for specific error codes.

```bash
# Get explanation for unqualified operator error
pgspot --explain PS001

# Get explanation for SECURITY DEFINER without search_path
pgspot --explain PS003
```

--------------------------------

### Analyze SQL files and streams with pgspot

Source: https://context7.com/timescale/pgspot/llms.txt

Basic usage for analyzing SQL files, multiple files, or piped input.

```bash
# Basic usage - analyze a single SQL file
pgspot extension.sql

# Analyze multiple files
pgspot schema.sql functions.sql triggers.sql

# Read SQL from stdin
echo "SELECT * FROM users;" | pgspot

# Analyze with heredoc
pgspot <<< "CREATE TABLE IF NOT EXISTS public.data(id int);"
```

--------------------------------

### Display pgspot help message

Source: https://github.com/timescale/pgspot/blob/main/README.md

Use the -h or --help flag to display the usage instructions and available options for the pgspot command-line tool.

```bash
> pgspot -h
usage: pgspot [-h] [-a] [--proc-without-search-path PROC] [--summary-only] [--plpgsql | --no-plpgsql] [--explain EXPLAIN] [--ignore IGNORE] [--sql-accepting SQL_FN] [FILE ...]

Spot vulnerabilities in PostgreSQL SQL scripts

positional arguments:
  FILE                  file to check for vulnerabilities

options:
  -h, --help            show this help message and exit
  -a, --append          append files before checking
  --proc-without-search-path PROC
                        whitelist functions without explicit search_path
  --summary-only        only print number of errors, warnings and unknowns
  --plpgsql, --no-plpgsql
                        Analyze PLpgSQL code (default: True)
  --explain EXPLAIN     Describe an error/warning code
  --ignore IGNORE       Ignore error or warning code
  --ignore-lang LANG    Ignore unknown procedural language
  --sql-accepting SQL_FN
                        Specify one or more sql-accepting functions

```

--------------------------------

### Analyze SQL-accepting functions

Source: https://context7.com/timescale/pgspot/llms.txt

Use the --sql-accepting flag to inform pgspot about functions that execute SQL strings, allowing for deeper analysis.

```bash
# Specify that execute_sql accepts SQL as argument
pgspot --sql-accepting execute_sql <<EOF
SELECT execute_sql('SELECT * FROM users WHERE id = 1');
EOF

# Multiple sql-accepting functions
pgspot --sql-accepting execute_sql --sql-accepting run_query script.sql
```

--------------------------------

### Correct SET search_path usage

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Provide the schema list without single quotes to ensure each schema is parsed correctly.

```sql
SET search_path TO pg_catalog, pg_temp;
```

--------------------------------

### Avoid `CREATE INDEX IF NOT EXISTS`

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Using `CREATE INDEX IF NOT EXISTS` is insecure because an attacker can pre-create the index, preventing the creation of unique constraints and potentially leading to malicious code execution. Use `CREATE INDEX` without `IF NOT EXISTS`.

```sql
CREATE INDEX IF NOT EXISTS i1 ON t(time);
```

```sql
CREATE INDEX i1 ON t(time);
```

--------------------------------

### Secure Sequence Creation with IF NOT EXISTS

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

To prevent attackers from controlling sequence values, use `CREATE SEQUENCE` without `IF NOT EXISTS` in extension-owned schemas. If `IF NOT EXISTS` must be used, ensure it's within an extension-owned schema.

```sql
CREATE SEQUENCE IF NOT EXISTS public.s1;
```

```sql
CREATE SCHEMA extension_schema;
CREATE SEQUENCE IF NOT EXISTS extension_schema.s1;
```

```sql
CREATE SEQUENCE public.s1;
```

--------------------------------

### Run pgspot on SQL files in CI

Source: https://context7.com/timescale/pgspot/llms.txt

This script iterates through SQL files in a directory, runs pgspot for security checks, and reports errors. It's designed for use in CI/CD pipelines to enforce security standards.

```bash
for sql_file in "$MIGRATION_DIR"/*.sql; do
    [ -f "$sql_file" ] || continue

    echo "Checking: $sql_file"

    # Run pgspot and capture output
    if ! output=$(pgspot --ignore PS005 "$sql_file" 2>&1); then
        echo "$output"
        EXIT_CODE=1
    else
        # Check if there are any errors (not just warnings)
        if echo "$output" | grep -q "Errors: [1-9]"; then
            echo "$output"
            EXIT_CODE=1
        else
            echo "  PASSED"
        fi
    fi
done

if [ $EXIT_CODE -eq 0 ]; then
    echo "=== All security checks passed ==="
else
    echo "=== Security issues found - please review ==="
fi

exit $EXIT_CODE

# Usage: ./ci-security-check.sh ./sql/migrations
```

--------------------------------

### Avoid CREATE OR REPLACE for Transforms

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Use `CREATE ...` without `OR REPLACE` to prevent potential privilege escalation when creating transforms. This ensures consistency and security.

```sql
CREATE OR REPLACE TRANSFORM rxid FOR LANGUAGE plpgsql(from sql with function f1);
```

```sql
CREATE TRANSFORM rxid FOR LANGUAGE plpgsql(from sql with function f1);
```

--------------------------------

### Avoid Unsafe Schema Creation with IF NOT EXISTS

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Use `CREATE SCHEMA` without `IF NOT EXISTS` to prevent attackers from pre-creating schemas and gaining ownership. This ensures that schema creation fails if the schema already exists, which is the desired secure behavior.

```sql
CREATE SCHEMA IF NOT EXISTS my_schema;
```

```sql
CREATE SCHEMA my_schema;
```

--------------------------------

### Secure Aggregate Creation with CREATE OR REPLACE

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

When using `CREATE OR REPLACE` for aggregates, ensure it's done within an extension-owned schema to prevent attackers from pre-creating objects and gaining ownership. Alternatively, use `CREATE` without `OR REPLACE`.

```sql
CREATE OR REPLACE AGGREGATE public.aggregate(BASETYPE=my_type,SFUNC=agg_sfunc,STYPE=internal);
```

```sql
CREATE SCHEMA extension_schema;
CREATE OR REPLACE AGGREGATE extension_schema.aggregate(BASETYPE=my_type,SFUNC=agg_sfunc,STYPE=internal);
```

```sql
CREATE AGGREGATE public.aggregate(SFUNC=agg_sfunc,STYPE=internal);
```

--------------------------------

### Avoid `CREATE TABLE IF NOT EXISTS` in insecure schemas

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Using `CREATE TABLE IF NOT EXISTS` in a schema not owned by the extension can allow an attacker to pre-create the table and gain ownership, leading to potential malicious code execution. Use `CREATE TABLE IF NOT EXISTS` in an extension-owned schema or `CREATE TABLE` without `IF NOT EXISTS`.

```sql
CREATE TABLE IF NOT EXISTS public.test_table(col text);
```

```sql
CREATE SCHEMA extension_schema;
CREATE TABLE IF NOT EXISTS extension_schema.test_table(col text);
```

```sql
CREATE TABLE public.test_table(col text);
```

--------------------------------

### Analyze multiple files in append mode

Source: https://context7.com/timescale/pgspot/llms.txt

Use the -a or --append flag to maintain state across multiple files, which is useful for extension scripts.

```bash
# Without append mode - each file is analyzed independently
pgspot schema.sql functions.sql

# With append mode - state is preserved across files
pgspot -a schema.sql functions.sql
```

--------------------------------

### Detecting unsafe function creation (PS002)

Source: https://context7.com/timescale/pgspot/llms.txt

pgspot flags CREATE OR REPLACE statements in schemas not owned by the extension to prevent function hijacking.

```bash
# Unsafe - CREATE OR REPLACE in public schema
pgspot <<EOF
CREATE OR REPLACE FUNCTION public.my_func() RETURNS INTEGER
LANGUAGE SQL AS $$SELECT 1;$$;
EOF
# Output: PS002: Unsafe function creation: my_func() at line 1

# Safe - CREATE without OR REPLACE
pgspot <<EOF
CREATE FUNCTION public.my_func() RETURNS INTEGER
LANGUAGE SQL AS $$SELECT 1;$$;
EOF
# Only PS005 warning (no search_path), no PS002 error

# Safe - CREATE OR REPLACE in extension-owned schema
pgspot <<EOF
CREATE SCHEMA myext;
CREATE OR REPLACE FUNCTION myext.my_func() RETURNS INTEGER
LANGUAGE SQL AS $$SELECT 1;$$;
EOF
# No PS002 warning - schema was created in this script
```

--------------------------------

### Function with Explicit Search Path

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Prefer defining an explicit `search_path` for all functions to prevent insecure search path attacks. Set it to `pg_catalog, pg_temp` for security.

```sql
CREATE FUNCTION my_extension.function()
    RETURNS VOID
    LANGUAGE SQL
AS $$
    -- function body
$$;
```

```sql
CREATE FUNCTION my_extension.function()
    RETURNS VOID
    SET search_path = pg_catalog, pg_temp
    LANGUAGE SQL
AS $$
    -- function body
$$;
```

--------------------------------

### Unsafe Function Creation

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Use `CREATE OR REPLACE FUNCTION` only in extension-owned schemas. If not in an extension-owned schema, use `CREATE FUNCTION` without `OR REPLACE` to prevent attackers from modifying function bodies.

```sql
CREATE OR REPLACE FUNCTION public.foo() RETURNS INTEGER LANGUAGE SQL AS $$SELECT 1;$$;
```

```sql
CREATE SCHEMA extension_schema;
CREATE OR REPLACE FUNCTION extension_schema.foo() RETURNS INTEGER LANGUAGE SQL AS $$SELECT 1;$$;
```

```sql
CREATE FUNCTION public.foo() RETURNS INTEGER LANGUAGE SQL AS $$SELECT 1;$$;
```

--------------------------------

### Avoid `CREATE OR REPLACE VIEW` in insecure schemas

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Using `CREATE OR REPLACE VIEW` in a schema not owned by the extension is insecure. An attacker can pre-create the view and gain ownership, allowing them to modify it. Use `CREATE OR REPLACE VIEW` in an extension-owned schema or `CREATE VIEW` without `OR REPLACE`.

```sql
CREATE OR REPLACE VIEW public.test_view AS SELECT pg_catalog.now();
```

```sql
CREATE SCHEMA extension_schema;
CREATE OR REPLACE VIEW extension_schema.test_view AS SELECT pg_catalog.now();
```

```sql
CREATE VIEW public.test_view AS SELECT pg_catalog.now();
```

--------------------------------

### SECURITY DEFINER Function with Fixed Search Path

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Mitigate privilege escalation risks with `SECURITY DEFINER` functions by setting a fixed, secure `search_path` to `pg_catalog, pg_temp`.

```sql
CREATE FUNCTION my_extension.security_definer_function()
    RETURNS VOID
    SECURITY DEFINER
    LANGUAGE SQL
AS $$
    -- function body
$$;
```

```sql
CREATE FUNCTION my_extension.security_definer_function()
    RETURNS VOID
    SECURITY DEFINER
    SET search_path = pg_catalog, pg_temp
    LANGUAGE SQL
AS $$
    -- function body
$$;
```

--------------------------------

### Detecting unsafe object creation with IF NOT EXISTS (PS010-PS014)

Source: https://context7.com/timescale/pgspot/llms.txt

These checks identify insecure use of IF NOT EXISTS in public schemas, which can be exploited to pre-create objects.

```bash
# PS010 - Unsafe schema creation
pgspot <<EOF
CREATE SCHEMA IF NOT EXISTS myschema;
EOF
# Output: PS010: Unsafe schema creation: myschema at line 1

# PS011 - Unsafe sequence creation
pgspot <<EOF
CREATE SEQUENCE IF NOT EXISTS public.my_seq;
EOF
# Output: PS011: Unsafe sequence creation: public.my_seq at line 1

# PS012 - Unsafe table creation
pgspot <<EOF
CREATE TABLE IF NOT EXISTS public.config(key text, value text);
EOF
# Output: PS012: Unsafe table creation: config at line 1

# PS014 - Unsafe index creation
pgspot <<EOF
CREATE INDEX IF NOT EXISTS idx_users ON users(email);
EOF
# Output: PS014: Unsafe index creation: idx_users at line 1

# Safe - without IF NOT EXISTS
pgspot <<EOF
CREATE SCHEMA myschema;
CREATE TABLE myschema.config(key text, value text);
EOF
```

--------------------------------

### Unqualified Operator Usage

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Avoid using operators without schema qualification or setting a secure search path to prevent potential redirection to malicious custom operators.

```sql
SELECT foo + bar;
```

```sql
SET search_path = pg_catalog, pg_temp;
SELECT foo + bar;
```

```sql
SELECT foo OPERATOR(pg_catalog.+) bar;
```

--------------------------------

### Detecting unqualified references (PS016, PS017)

Source: https://context7.com/timescale/pgspot/llms.txt

pgspot identifies unqualified function calls and object references that are susceptible to search_path manipulation.

```bash
# PS016 - Unqualified function call
pgspot <<EOF
SELECT my_function(arg1, arg2);
EOF
# Output: PS016: Unqualified function call: my_function at line 1

# PS017 - Unqualified object reference
pgspot <<EOF
SELECT * FROM users;
EOF
# Output: PS017: Unqualified object reference: users at line 1

# Safe - set search_path first
pgspot <<EOF
SET search_path TO pg_catalog, pg_temp;
SELECT now();
SELECT * FROM pg_catalog.pg_class;
EOF
# Output: Errors: 0 Warnings: 0 Unknown: 0

# Safe - fully qualified references
pgspot <<EOF
SELECT pg_catalog.now();
SELECT * FROM myschema.users;
EOF
# Output: Errors: 0 Warnings: 0 Unknown: 0
```

--------------------------------

### Whitelist functions without search_path

Source: https://context7.com/timescale/pgspot/llms.txt

Use the --proc-without-search-path flag to suppress PS005 warnings for specific functions.

```bash
# Whitelist a function that must be inlined (cannot have SET search_path)
pgspot --proc-without-search-path "my_inline_function()" extension.sql

# Whitelist multiple functions
pgspot --proc-without-search-path "func1()" --proc-without-search-path "func2(integer)" script.sql

# Example usage
pgspot --proc-without-search-path "get_current_user()" <<EOF
CREATE FUNCTION get_current_user() RETURNS TEXT LANGUAGE SQL AS $$
  SELECT pg_catalog.current_user;
$$;
EOF
```

--------------------------------

### Secure CASE Expression with Explicit Search Path

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Mitigate risks with 'simple' CASE expressions by explicitly setting the `search_path` to `pg_catalog, pg_temp`. This ensures proper schema qualification for operators.

```sql
SELECT
    CASE a OPERATOR(pg_catalog.=) b
        WHEN true THEN 'true'
        WHEN false THEN 'false'
    END
FROM my_schema.foo;
```

```sql
SET search_path = pg_catalog, pg_temp;
SELECT
    CASE a = b
        WHEN true THEN 'true'
        WHEN false THEN 'false'
    END
FROM my_schema.foo;
```

--------------------------------

### Configuring PL/pgSQL analysis

Source: https://context7.com/timescale/pgspot/llms.txt

Use --no-plpgsql to disable analysis of PL/pgSQL function bodies if parsing issues occur. Analysis is enabled by default.

```bash
# Disable PL/pgSQL analysis
pgspot --no-plpgsql extension.sql

# Enable PL/pgSQL analysis (default)
pgspot --plpgsql extension.sql
```

--------------------------------

### Detecting SECURITY DEFINER vulnerabilities (PS003, PS004)

Source: https://context7.com/timescale/pgspot/llms.txt

These checks ensure that SECURITY DEFINER functions use explicit and secure search_path settings to prevent privilege escalation.

```bash
# PS003 - SECURITY DEFINER without search_path (error)
pgspot <<EOF
CREATE FUNCTION admin_func() RETURNS VOID
SECURITY DEFINER LANGUAGE SQL AS $$
  SELECT set_admin_flag();
$$;
EOF
# Output: PS003: SECURITY DEFINER function without explicit search_path: admin_func()

# PS004 - SECURITY DEFINER with insecure search_path (error)
pgspot <<EOF
CREATE FUNCTION admin_func() RETURNS VOID
SECURITY DEFINER
SET search_path = public, pg_catalog
LANGUAGE SQL AS $$ SELECT 1; $$;
EOF
# Output: PS004: SECURITY DEFINER function with insecure search_path: admin_func()

# Safe - SECURITY DEFINER with secure search_path
pgspot <<EOF
CREATE FUNCTION admin_func() RETURNS VOID
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
LANGUAGE SQL AS $$ SELECT pg_catalog.now(); $$;
EOF
# Output: Errors: 0 Warnings: 0 Unknown: 0
```

--------------------------------

### SECURITY DEFINER Function with Insecure Search Path

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Ensure `SECURITY DEFINER` functions do not use insecure search paths like `public, pg_catalog`. Always set the `search_path` to `pg_catalog, pg_temp` to prevent attacks.

```sql
CREATE FUNCTION my_extension.security_definer_function()
    RETURNS VOID
    SECURITY DEFINER
    SET search_path = public, pg_catalog
    LANGUAGE SQL
AS $$
    -- function body
$$;
```

```sql
CREATE FUNCTION my_extension.security_definer_function()
    RETURNS VOID
    SECURITY DEFINER
    SET search_path = pg_catalog, pg_temp
    LANGUAGE SQL
AS $$
    -- function body
$$;
```

--------------------------------

### Secure CASE Expression with Alternate Form

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Use the alternate form of CASE expression, explicitly qualifying operators, to avoid issues with schema qualification when a secure search_path is not set.

```sql
SELECT
    CASE
        WHEN a OPERATOR(pg_catalog.=) b THEN 'true'
        WHEN a OPERATOR(pg_catalog.!=) b THEN 'false'
    END
FROM my_schema.foo;
```

--------------------------------

### Qualify function calls

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Unqualified function calls can be redirected by an attacker who creates a custom function with the same name. Either set the `search_path` explicitly or fully qualify the function name.

```sql
SELECT my_function(foo);
```

```sql
SET search_path = pg_catalog, pg_temp;
SELECT my_function(foo);
```

```sql
SELECT extension_schema.my_function(foo);
```

--------------------------------

### Qualify object references

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Unqualified object references can be exploited by an attacker who creates a temporary relation or modifies the `search_path`. Explicitly set the `search_path` or fully qualify the object name.

```sql
SELECT * FROM foo;
```

```sql
SET search_path = extension_schema;
SELECT * FROM foo;
```

```sql
SELECT * FROM extension_schema.foo;
```

--------------------------------

### Erroneous SET search_path usage

Source: https://github.com/timescale/pgspot/blob/main/REFERENCE.md

Using single quotes around the schema list causes the entire string to be treated as a single schema name.

```sql
SET search_path TO 'pg_catalog, temp';
```

--------------------------------

### Suppress specific vulnerability codes

Source: https://context7.com/timescale/pgspot/llms.txt

Use the --ignore flag to suppress specific vulnerability codes when they are acceptable.

```bash
# Ignore unqualified object reference warnings (PS017)
pgspot --ignore PS017 <<< "SELECT * FROM mytable;"

# Ignore multiple codes
pgspot --ignore PS017 --ignore PS016 extension.sql

# Ignore unsafe table creation (PS012) when IF NOT EXISTS is intentional
pgspot --ignore PS012 <<< "CREATE TABLE IF NOT EXISTS public.config(key text, value text);"
```

--------------------------------

### Suppressing output with --summary-only

Source: https://context7.com/timescale/pgspot/llms.txt

Use the --summary-only flag to hide individual error messages and display only the final count. This is particularly useful for CI/CD pipeline integration.

```bash
# Get only the summary
pgspot --summary-only extension.sql
# Output: Errors: 3 Warnings: 7 Unknown: 0

# Useful for CI/CD pipelines
if pgspot --summary-only script.sql | grep -q "Errors: 0"; then
  echo "Security check passed"
fi
```

--------------------------------

### Ignoring custom procedural languages

Source: https://context7.com/timescale/pgspot/llms.txt

The --ignore-lang flag prevents warnings for unrecognized custom procedural languages. You can specify multiple languages by repeating the flag.

```bash
# Ignore pltcl language
pgspot --ignore-lang pltcl <<EOF
CREATE FUNCTION tcl_func() RETURNS TEXT LANGUAGE pltcl AS $$
  return "hello"
$$;
EOF
# No "Unknown function language" warning

# Ignore multiple languages
pgspot --ignore-lang pltcl --ignore-lang plperl extension.sql
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.