Public Schema Unrestricted Access

Scenario

A security review finds that all database users can CREATE tables and functions in the public schema (pre-PostgreSQL 15 default behavior). A low-privilege analytics user exploited this to create a function named now() in the public schema. Since public is first in the default search_path, all subsequent calls to now() in that session execute the malicious function instead of pg_catalog.now() — a classic search-path injection attack.

How to Identify

Conditions:

• pg_namespace.nspacl for public schema includes =UC/postgres (CREATE for PUBLIC)
• Any user can run CREATE TABLE in public without special grants
• search_path includes public before pg_catalog (allows function shadowing)
• PostgreSQL version < 15 (PG15 removed default CREATE on public schema for PUBLIC)

Analysis Steps

-- Check public schema ACL
SELECT nspname, nspacl
FROM pg_namespace
WHERE nspname = 'public';
-- nspacl containing '=UC/postgres' = all users have USAGE + CREATE on public schema

-- Check if any user can create in public
SELECT has_schema_privilege('some_low_priv_user', 'public', 'CREATE') AS can_create;

-- Check for suspicious objects in public schema
SELECT schemaname, objectname, objecttype, owner
FROM pg_catalog.pg_depend d
JOIN pg_catalog.pg_class c ON d.objid = c.oid
WHERE schemaname = 'public'
ORDER BY owner;

-- Check for function overrides in public schema
SELECT n.nspname, p.proname, p.proowner::regrole AS owner,
       pg_get_function_identity_arguments(p.oid) AS args
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE n.nspname = 'public'
  AND p.proname IN ('now', 'current_timestamp', 'pg_sleep', 'md5', 'encode');
-- Any of these = potential function shadowing attack

Pitfalls

• In PostgreSQL 14 and earlier, PUBLIC has CREATE privilege on the public schema by default. PostgreSQL 15 changed this — new installs no longer grant CREATE to PUBLIC on public schema.
• Removing CREATE from public schema doesn’t remove existing objects — it only prevents new ones.
• search_path order matters: if public comes before pg_catalog, user-created functions in public can shadow built-in functions. Always set search_path = "$user", public and never include untrusted schemas first.
• The fix REVOKE CREATE ON SCHEMA public FROM PUBLIC only affects the schema — tables already in public are still accessible to anyone with USAGE on the schema.
• For new databases: also REVOKE ALL ON DATABASE mydb FROM PUBLIC to prevent unauthorized connections.

Resolution Approach

Revoke CREATE on the public schema from PUBLIC. Audit for any objects created by non-owners. For new databases: apply the PostgreSQL 15 default behavior explicitly. Set search_path to "$user", public to prevent schema injection.