Goal This query checks whether the public schema has CREATE or USAGE privileges granted to the PUBLIC pseudo-role (all database users). While CREATE is a critical vulnerability that allows unprivileged users to create rogue objects in a shared namespace, leaving the USAGE privilege open to PUBLIC is also redundant and violates the principle of least privilege. In a hardened environment, all default ambient access to the public schema should be removed, ensuring that schema traversal and object creation are restricted to explicitly authorized roles.
Type Problem detection Each row in the result could represent a flaw in the design
Reliability High Few or no false-positive results
License MIT (opens in new tab)
Data Source system catalog only
SQL Query
SELECT 
    n.nspname AS schema_name,
    'PUBLIC' AS grantee_role,
    acl.privilege_type
FROM 
    pg_namespace n
CROSS JOIN 
    aclexplode(COALESCE(n.nspacl, acldefault('n', n.nspowner))) AS acl
WHERE 
    n.nspname = 'public'
    AND acl.grantee = 0 
    AND acl.privilege_type IN ('USAGE', 'CREATE');

Fixing Suggestion

To properly harden database security, revoke both CREATE and USAGE privileges from the PUBLIC role. Retaining USAGE for all users is completely redundant, as secure architectures should rely on dedicated schemas or explicit, role-based grants rather than ambient global access. Execute the following command to completely close default public access: REVOKE ALL ON SCHEMA public FROM PUBLIC;
(or explicitly: REVOKE CREATE, USAGE ON SCHEMA public FROM PUBLIC;)
Once all global privileges are revoked, explicitly grant USAGE (and CREATE, if strictly necessary) only to the specific, designated application roles that genuinely need access to this schema

Fixing Actions

SQL statements that help generate fixes for the identified problem.

Fix Action #1
SELECT 'REVOKE USAGE ON SCHEMA public FROM PUBLIC;' AS statements
FROM 
    pg_namespace n
CROSS JOIN 
    aclexplode(COALESCE(n.nspacl, acldefault('n', n.nspowner))) AS acl
WHERE 
    n.nspname = 'public'
    AND acl.grantee = 0 
    AND acl.privilege_type='USAGE';

Revoke the usage privilege

Fix Action #2
SELECT 'REVOKE CREATE ON SCHEMA public FROM PUBLIC;' AS statements
FROM 
    pg_namespace n
CROSS JOIN 
    aclexplode(COALESCE(n.nspacl, acldefault('n', n.nspowner))) AS acl
WHERE 
    n.nspname = 'public'
    AND acl.grantee = 0 
    AND acl.privilege_type='CREATE';

Revoke the create privilege

# SQL Query to Generate Fix Description
1
SELECT 'REVOKE USAGE ON SCHEMA public FROM PUBLIC;' AS statements
FROM 
    pg_namespace n
CROSS JOIN 
    aclexplode(COALESCE(n.nspacl, acldefault('n', n.nspowner))) AS acl
WHERE 
    n.nspname = 'public'
    AND acl.grantee = 0 
    AND acl.privilege_type='USAGE';
Revoke the usage privilege
2
SELECT 'REVOKE CREATE ON SCHEMA public FROM PUBLIC;' AS statements
FROM 
    pg_namespace n
CROSS JOIN 
    aclexplode(COALESCE(n.nspacl, acldefault('n', n.nspowner))) AS acl
WHERE 
    n.nspname = 'public'
    AND acl.grantee = 0 
    AND acl.privilege_type='CREATE';
Revoke the create privilege

Collections

This query belongs to the following collections:

Find problems automatically

Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .

Categories

This query is classified under the following categories:

Security

Queries of this category provide information about the security measures.

NameDescription
SecurityQueries of this category provide information about the security measures.