pgspot

Some PostgreSQL functions take a string argument and execute it as SQL. By default, pgspot might not see the SQL inside these strings. You can use the --sql-accepting flag to tell pgspot that a specific function (e.g., execute_sql) accepts SQL. pgspot will then attempt to unpack and evaluate the arguments passed to that function as SQL code to ensure they are scanned.

# If 'execute_sql' is a function that runs SQL strings:
pgspot --sql-accepting=execute_sql your_script.sql

Use the pgspot CLI to scan PostgreSQL extension scripts, SECURITY DEFINER functions, or any other SQL code for security vulnerabilities. It specifically checks for:

• search_path-based attacks
• Unsafe object creation

To run a scan, provide the file(s) as positional arguments.

pgspot path/to/your_script.sql

You can install pgspot using pip. Ensure your environment meets the requirements: Python version 3.10 or higher, and the pglast package (which includes libpg_query).

pip install pgspot

Using CREATE OR REPLACE VIEW in a schema that is not owned by the extension is insecure. An attacker can pre-create the view to become its owner, enabling malicious code execution.

To mitigate this, you should either:

1. Use CREATE OR REPLACE VIEW within an extension-owned schema.
2. Use CREATE VIEW (without OR REPLACE) if you are working in a public schema.

-- Insecure
CREATE OR REPLACE VIEW public.test_view AS SELECT pg_catalog.now();

-- Secure Option A: Use an extension-owned schema
CREATE SCHEMA extension_schema;
CREATE OR REPLACE VIEW extension_schema.test_view AS SELECT pg_catalog.now();

-- Secure Option B: Use CREATE VIEW without OR REPLACE
CREATE VIEW public.test_view AS SELECT pg_catalog.now();

The PS006 warning is issued when CREATE OR REPLACE TRANSFORM is used. While currently difficult to exploit without superuser privileges, it is flagged for consistency with other security practices.

Mitigation: Use CREATE TRANSFORM without the OR REPLACE clause.

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

The PS001 vulnerability occurs when an operator is used without a fully schema-qualified name or a secure search_path. This allows attackers to hijack execution by creating custom operators or modifying the search_path.

To mitigate, you can either explicitly set the search_path for the session or use the OPERATOR syntax to fully qualify the operator.

-- Option A: Explicitly set the search path
SET search_path = pg_catalog, pg_temp;
SELECT foo + bar;

-- Option B: Fully schema-qualify the operator
SELECT foo OPERATOR(pg_catalog.+) bar;

Referencing objects (like tables or relations) without a fully qualified schema name or an explicitly set search_path is unsafe. An attacker could create a temporary relation or modify the search_path to redirect the query to an attacker-controlled relation.

To mitigate this, either explicitly set the search_path or fully-qualify the object.

-- Insecure
SELECT * FROM foo;

-- Secure Option A: Explicitly set the search path
SET search_path = extension_schema;
SELECT * FROM foo;

-- Secure Option B: Fully-qualify the object
SELECT * FROM extension_schema.foo;

When using SET search_path, do not enclose the list of schemas in single quotes. Enclosing them in quotes causes PostgreSQL to treat the entire list as a single schema name, which can allow shadowing of catalog relations.

To mitigate this, either provide the schema list without quotes or use pg_catalog.set_config.

-- Insecure
SET search_path TO 'pg_catalog, temp';

-- Secure Option A: Do not use single quotes
SET search_path TO pg_catalog, pg_temp;

-- Secure Option B: Use pg_catalog.set_config
SELECT pg_catalog.set_config('search_path','pg_catalog, pg_temp', false);

The PS007 vulnerability occurs when CREATE OR REPLACE is used for objects like AGGREGATE in an insecure schema. This allows an attacker to pre-create the object and gain ownership, leading to malicious code execution.

Mitigation: Use CREATE OR REPLACE in an extension-owned schema, or use CREATE without OR REPLACE in public schemas.

-- Option A: Use an extension-owned schema
CREATE SCHEMA extension_schema;
CREATE OR REPLACE AGGREGATE extension_schema.aggregate(BASETYPE=my_type,SFUNC=agg_sfunc,STYPE=internal);

-- Option B: Use CREATE without OR REPLACE
CREATE AGGREGATE public.aggregate(SFUNC=agg_sfunc,STYPE=internal);

Calling a function without a fully qualified schema name or an explicitly set search_path is unsafe. An attacker could create a custom function with matching types to hijack the execution.

To mitigate this, either explicitly set the search_path or fully-qualify the function name.

-- Insecure
SELECT my_function(foo);

-- Secure Option A: Explicitly set the search path
SET search_path = pg_catalog, pg_temp;
SELECT my_function(foo);

-- Secure Option B: Fully-qualify the function
SELECT extension_schema.my_function(foo);

Functions with SECURITY DEFINER execute as a different user, making them targets for privilege escalation via search_path attacks.

• PS003/PS004: Occurs when a SECURITY DEFINER function is created without a fixed search_path or with an insecure one (e.g., including public).
• PS005: A warning for functions created without an explicit search_path. While often ignorable for inlined functions, it is best practice to define one.

Mitigation: Always set the search_path to pg_catalog, pg_temp for these functions.

-- Secure implementation for SECURITY DEFINER and standard functions
CREATE FUNCTION my_extension.security_definer_function()
    RETURNS VOID
    SECURITY DEFINER
    SET search_path = pg_catalog, pg_temp
    LANGUAGE SQL
AS $$
    -- function body
$$