Australian cloud for business software — built and run in Sydney since 2002

NetLeverage
NetLeverage

PostgreSQL ERROR: permission denied for schema public (CREATE TABLE fails since PostgreSQL 15)

Technical article
Troubleshooting

About this article: This article is reviewed against the cited sources. This is source research, not lab validation. Check the stated versions and your own requirements before making changes.

If a migration, an installer or a developer's CREATE TABLE fails with ERROR: permission denied for schema public, the role you connected as does not hold the privilege it needs on the public schema. The usual cause on a newer server is the PostgreSQL 15 change that removed the CREATE privilege on public from ordinary users in new databases. Check the privileges, then either give the role a schema of its own or grant CREATE on public to that role.

What the error means

A schema is a namespace inside a database. Two privileges matter on it. The manual says CREATE "allows new objects to be created within the schema" and USAGE "allows access to objects contained in the schema". The message text comes from PostgreSQL's source code (file src/backend/catalog/aclchk.c, release REL_17_12), because the manual does not print it. The source has the format permission denied for schema %s, where the %s part varies and is the name of the schema, so a failure on the schema named public prints permission denied for schema public.

Until PostgreSQL 14, every user could create objects in public. The PostgreSQL 15 release notes list this change: "Remove PUBLIC creation permission on the public schema". They say it "applies to new database clusters and to newly-created databases in existing clusters", and that "Upgrading a cluster or restoring a database dump will preserve public's existing permissions."

The documentation for PostgreSQL 17 describes the result. "By default, everyone has" the USAGE privilege on the schema public. The CREATE privilege on it is no longer given to everyone by default: "In databases upgraded from PostgreSQL 14 or earlier, everyone has that privilege on the schema public." The same release also changed the owner of public to the new pg_database_owner role, which the manual describes as having no access of its own, with "Membership consists, implicitly, of the current database owner." So in a new database, the database owner can create objects in public and an ordinary role cannot.

This explains a common surprise. An application works on a server that was upgraded from version 14, because its public schema kept the old permissions. The same application fails on a fresh PostgreSQL 15, 16 or 17 database that was created with CREATE DATABASE, because the new default applies there.

Likely causes

Cause What you see How to confirm
New database on PostgreSQL 15 or later; role is not the database owner CREATE TABLE in public fails; reads may work has_schema_privilege for CREATE returns f; the role is not the database owner in \l
USAGE on public was revoked Even SELECT or a function call by name fails has_schema_privilege for USAGE returns f
CREATE was revoked from PUBLIC on an upgraded database to harden it Worked before a hardening change, fails now \dn+ public no longer shows C for the empty grantee
App connects as a different role than expected The error names the right schema but the wrong user is connected SELECT current_user, session_user; prints a role other than the one you granted

Prove the cause before changing anything

All of these are read-only. Run them in the failing database, as a superuser or the database owner.

1. Which role is actually connected? current_user returns "the user name of the current execution context" and session_user returns "the session user's name".

SELECT current_user, session_user;

2. What version is the server? The change applies to PostgreSQL 15 and later.

SHOW server_version;

3. Who owns the database and the schema? In psql, \l lists databases with "names, owners, character set encodings, and access privileges", and \dn+ lists schemas "with its associated permissions and description".

\l
\dn+ public

The PostgreSQL 15 release notes say that in a new database the owner of public is the pg_database_owner role. The access privileges column uses the manual's format: grantee=privilege-abbreviation/grantor, where "An empty grantee field in an aclitem stands for PUBLIC." The letters are U for USAGE and C for CREATE. A line for the empty grantee that shows U and not C means everyone may use the schema but not create objects in it.

4. Ask the server directly. has_schema_privilege takes a role, a schema and a privilege, and the manual says "Allowable privilege types are CREATE and USAGE".

SELECT has_schema_privilege('app_user', 'public', 'CREATE');
SELECT has_schema_privilege('app_user', 'public', 'USAGE');

Good result for an application that creates tables: both return t. Bad result: CREATE returns f. The check reflects the sum of what is granted to the role directly, through role membership and to PUBLIC, which is how the manual says privileges combine.

5. Check the search path. The manual says the default search path is "$user", public, that "The first schema in the search path that exists is the default location for creating new objects", and that the first element is ignored when no schema has the user's name.

SHOW search_path;

If a schema named like the role exists and the role can create in it, new objects go there rather than into public.

Fix

Choose one of the options. The first follows the pattern the manual recommends. The second restores the pre-15 behaviour for one role.

Option 1: give the role a schema of its own

The manual describes this as a secure schema usage pattern: "for every user needing to create non-temporary objects, create a schema with the same name as that user, for example CREATE SCHEMA alice AUTHORIZATION alice." Because the default search path starts with $user, the role then creates and finds its objects in its own schema without any other change.

First save the current schema list and privileges:

\dn+

Keep that output. If app_user already appears in that list, stop: the schema exists and the undo below would drop it.

Risk: the next statement creates a new schema owned by the role. Objects the application creates afterwards go into that schema and not into public, so code that expects its new tables in public will not find them. The statement needs the CREATE privilege for the current database and, to give the schema to another role, the ability to SET ROLE to that role; superusers bypass both checks.

CREATE SCHEMA app_user AUTHORIZATION app_user;

Reconnect as app_user and run SHOW search_path;. The default "$user", public now resolves its first element to the new schema, so new objects are created there. This works only for unqualified names: a statement that names public. explicitly still fails with the same error.

To undo it, only if the CREATE SCHEMA statement reported success: drop the schema, naming RESTRICT explicitly so that the command refuses if anything was created in it. The manual says RESTRICT will "Refuse to drop the schema if it contains any objects. This is the default."

DROP SCHEMA app_user RESTRICT;

If the command refuses, objects exist in the schema, and you should decide what to do with them before dropping anything. Do not switch to CASCADE, which the manual says automatically drops contained objects and everything that depends on them.

Option 2: grant CREATE on public to one role

For a database with one application and trusted users, grant the privilege to the single role. The PostgreSQL 15 release notes say that "For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases."

First save the current state:

\dn+ public
SELECT has_schema_privilege('app_user', 'public', 'CREATE');

Keep both outputs. The back-out below is correct only if the second query printed f.

Risk: the next statement lets app_user create objects in the public schema. The manual says that keeping the default search path and granting create privileges in public "is never a secure pattern" and is "acceptable only when the database has a single user or a few mutually-trusting users". Run it as the database owner or a superuser; GRANT and REVOKE can be done by the owner of the object or by a role that is a member of the owning role, and in a new database the database owner holds the schema through pg_database_owner.

GRANT CREATE ON SCHEMA public TO app_user;

Check with the same has_schema_privilege query (it should now print t) and rerun the failing statement.

To back out: revoke the same privilege from the same role, then run the check again.

REVOKE CREATE ON SCHEMA public FROM app_user;
SELECT has_schema_privilege('app_user', 'public', 'CREATE');

It should print the value you saved (f). If it still prints t, the role gains CREATE through another route. The manual says a role has "the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC", so a grant to PUBLIC or to a group role also counts.

Option 3: restore USAGE where it was revoked

If the USAGE check showed f, the role cannot use the schema at all. First save the current state:

\dn+ public
SELECT has_schema_privilege('app_user', 'public', 'USAGE');

Keep both outputs. The back-out below is correct only if the second query printed f.

Risk: the next statement allows app_user to look up objects in public. The role can still only use objects whose own privileges allow it.

GRANT USAGE ON SCHEMA public TO app_user;

To back out: revoke the same privilege from the same role, then run the USAGE check again, which should print the saved value.

REVOKE USAGE ON SCHEMA public FROM app_user;
SELECT has_schema_privilege('app_user', 'public', 'USAGE');

The manual warns that after revoking USAGE, "existing sessions might have statements that have previously performed this lookup", so reconnect sessions to see the effect.

Example (illustrative): db01 runs PostgreSQL 16. A deployment script creates the database shopdb and runs the application's schema migration as the role app_user, which is not the database owner. The first statement, CREATE TABLE orders (...), fails with ERROR: permission denied for schema public. On the older server, which was upgraded from 14, the same migration passed. \dn+ public on db01 shows no C for the empty grantee, and has_schema_privilege('app_user','public','CREATE') returns f. The team creates the schema with CREATE SCHEMA app_user AUTHORIZATION app_user;. The migration now creates orders in the new app_user schema, found first on the default search path, and the application runs unchanged.

Confirm the fix

  1. Run the failing statement again as the same role. It should succeed.
  2. has_schema_privilege returns t for the privilege you added, or SHOW search_path; shows a schema that the role owns.
  3. Run the application's migration or installer end to end, then a plain SELECT as a second ordinary role to check the permissions you intended are the only ones changed.

What to watch afterwards

  • Add the schema or grant to the provisioning script for new databases, so a rebuilt server does not repeat the error.
  • Dumps and restores keep the privileges of public from the source database, as the PostgreSQL 15 notes say, so a restored database can differ from a freshly created one.
  • Audit grants after a major upgrade. A database created before the upgrade and one created after it can have different defaults on the same server.
  • Keep CREATE on public away from roles that do not need it.

Risks and limits

  • These commands have not been run or lab-tested.
  • Granting CREATE on public to an ordinary role is not a secure pattern for a database with untrusted users. The schema pattern in Option 1 is the one the manual recommends.
  • Roles that gain a privilege through group membership or PUBLIC are not changed by a revoke aimed at one role.
  • The same message text can name other schemas; the grants above apply to the schema named in the message.

Sources