| 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 |
|
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
(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 |
|
Revoke the usage privilege |
| 2 |
|
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 .
| Name | Description |
|---|---|
| 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 . |
Categories
This query is classified under the following categories:
Security
Queries of this category provide information about the security measures.
| Name | Description |
|---|---|
| Security | Queries of this category provide information about the security measures. |