skip to content

Many relational databases have a built-in PUBLIC pseudo-role. What is it, what does it typically hold by default, and what would you do about it when hardening a database?

level: middleimportance: should knowfreq 36%

answer

  1. PUBLIC = implicit everyone, no membership to revoke
  2. grants to PUBLIC are a floor under every role
  3. pre-PG15: CREATE on public schema to PUBLIC
  4. revoke CONNECT on database from PUBLIC
  5. per-role review never shows PUBLIC grants

basics

~20 s

PUBLIC is an implicit group that every role belongs to and cannot be removed from, so anything granted to it is granted to everyone. Engines ship default PUBLIC grants - historically schema creation and access, connect rights, execute on many functions - so hardening starts by revoking those and granting explicitly instead.

solid answer

~60 s

PUBLIC is not a real role you can drop or remove members from - it is the implicit 'everyone' in the privilege system, including future roles you haven't created yet. Any grant to PUBLIC is a floor under every account's privileges. Defaults matter because they are grants nobody wrote. Historically PostgreSQL granted `CREATE` and `USAGE` on the `public` schema to PUBLIC, so any role could create objects there - a real risk, since a user-created function or table shadowing a name on someone's search path is an escalation path. PostgreSQL 15 changed this: the `public` schema is now owned by a system role and PUBLIC no longer has `CREATE` on it. `CONNECT` on new databases and `EXECUTE` on functions are also commonly granted to PUBLIC by default in various engines. Hardening: revoke `CREATE` and unnecessary `USAGE` from PUBLIC, revoke `CONNECT` on databases from PUBLIC and grant it to named roles, and check default-privilege settings so new objects do not silently acquire PUBLIC grants. Then audit for PUBLIC grants periodically - they are the ones that never show up in a per-role review.

code

sql · 6 lines
sql
REVOKE CREATE ON SCHEMA public FROM PUBLIC;   -- default already removed in PG15+
REVOKE ALL ON DATABASE appdb FROM PUBLIC;
GRANT CONNECT ON DATABASE appdb TO app_rw, reporting;

REVOKE EXECUTE ON FUNCTION app.rebuild_cache() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.rebuild_cache() TO app_rw;

go deeper

for a junior

Say PUBLIC means everyone, that anything granted to it applies to all roles, and that hardening means revoking what it holds by default.

for a middle

Name concrete defaults - CREATE/USAGE on the public schema in older PostgreSQL, CONNECT on new databases, EXECUTE on functions - and the revokes that address them, with the version caveat.

for a senior

Explain the search-path shadowing escalation that motivated the change, cover default privileges reintroducing PUBLIC grants, and make PUBLIC an explicit item in periodic access review and post-upgrade checks.

for a principal

Treat it as a baseline-configuration problem: codify the revokes in provisioning, detect drift after upgrades and restores, and define who may ever grant to PUBLIC.

## What PUBLIC is PUBLIC is a pseudo-role meaning 'every role in this database, present and future'. You cannot grant membership in it, revoke membership from it, or drop it. Its only interface is that you can `GRANT ... TO PUBLIC` and `REVOKE ... FROM PUBLIC`. The consequence is that PUBLIC grants set a floor. Whatever careful per-role design you build sits *on top of* whatever PUBLIC already holds. A role you create as 'read-only on two tables' actually has those two tables plus everything PUBLIC has, and you will not see the difference by listing that role's grants. ## Why it exists It is a usability default. Databases historically wanted new users to be able to do something immediately - connect, use built-in functions, create scratch objects. That convenience made sense in a world of trusted internal users and is a poor fit for multi-tenant or internet-facing systems. ## Typical defaults These vary by engine and version, so state the version when you answer: - **PostgreSQL (before 15):** the `public` schema had `CREATE` and `USAGE` granted to PUBLIC, so *any* role could create objects in it. In **PostgreSQL 15+**, the `public` schema is owned by the `pg_database_owner` role and PUBLIC no longer holds `CREATE` there - a significant hardening of the default. - **PostgreSQL, generally:** `CONNECT` on a newly created database and `EXECUTE` on newly created functions are granted to PUBLIC by default, as is `USAGE` on procedural languages and data types. - **Oracle:** the PUBLIC role holds a large set of grants on built-in packages, and public synonyms resolve for everyone; long-standing hardening guides focus on revoking execute on the more dangerous packages. - **MySQL:** there is no PUBLIC role as such, but anonymous accounts and overly broad `*.*` grants historically played a similar role; the equivalent hygiene is removing anonymous users and broad global grants. ## Why the CREATE-on-schema default was dangerous If every role can create objects in a schema that appears on everyone's search path, then a low-privileged role can define a function or table whose name shadows one that other sessions resolve unqualified. A privileged session - or worse, a definer's-rights function with an unpinned search path - may then resolve to the attacker's object and execute their code with elevated privileges. This is the reason the PostgreSQL default changed and the reason security guidance insists on pinning `search_path` in definer's-rights functions. ## Hardening steps 1. **Revoke `CREATE` from PUBLIC** on every schema (on older PostgreSQL, explicitly on `public`). Grant it only to the owner/migration role. 2. **Revoke `CONNECT` on databases from PUBLIC** and grant it to the named roles that should connect. Without this, any role in the cluster can open a session to any database. 3. **Review `EXECUTE` grants to PUBLIC** on functions, especially any function that touches the filesystem, network, or privileged operations, and on Oracle the dangerous built-in packages. 4. **Check default privileges.** If defaults are configured to grant to PUBLIC on newly created objects, every migration silently widens access - fix the default rather than revoking object by object. 5. **Include PUBLIC in access reviews.** The whole failure mode of PUBLIC is invisibility: reviewing per-role grants will never surface it. Query the catalog specifically for grantee = PUBLIC. 6. **Prefer explicit grants to named roles** for everything, so 'who can do this?' always has an enumerable answer. ## A useful mental check When someone claims a role is limited to certain tables, the follow-up question is 'and what does PUBLIC have?'. If nobody knows, the claim about that role is unverified. The same applies after an engine upgrade or a restore from an older dump, since defaults may come back with the older behaviour.

  • Why was letting PUBLIC create objects in a schema on everyone's search path considered a security problem?
    Because a low-privileged role could create a function or table whose name shadows one that other sessions resolve without a schema qualifier. A privileged session - especially a definer's-rights function with an unpinned search path - could then resolve to the attacker's object and run their code with elevated privileges. Pinning search_path in such functions and revoking CREATE from PUBLIC both close this.
  • You audit every role's grants and find nothing unexpected. Why might the database still be over-permissive?
    Because grants to PUBLIC do not appear when you enumerate an individual role's privileges, yet they apply to every role including ones created later. You have to query the catalog specifically for grants whose grantee is PUBLIC, and re-check after upgrades or restores from older dumps that may reintroduce older defaults.

saying these in an interview costs you the question

  • Trying to remove a role from PUBLIC or drop PUBLIC - neither is possible; only grants to it can be revoked.
  • Auditing only per-role grants and concluding the database is tightly scoped while PUBLIC grants remain.
  • Assuming PUBLIC affects only existing roles, when it also applies to every role created in future.
  • Stating pre-PostgreSQL-15 defaults as current without noting that the public schema's CREATE grant to PUBLIC was removed in 15.

context

resolving links…