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
- Run the failing statement again as the same role. It should succeed.
has_schema_privilegereturnstfor the privilege you added, orSHOW search_path;shows a schema that the role owns.- Run the application's migration or installer end to end, then a plain
SELECTas 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
publicfrom 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
CREATEonpublicaway from roles that do not need it.
Read next
Related articles you may want next.
- TroubleshootingPostgreSQL "password authentication failed for user": scram-sha-256, md5 and the stored passwordPostgreSQL 'password authentication failed for user'? The server log gives the real reason: wrong password, no role, expired, or md5 versus scram.Read article
- Backup & RecoveryA PostgreSQL Dump Restored with Missing Roles or Extensions: Check the Recovery SetInventory database content, cluster-wide objects and extension packages needed for a usable logical restore.Read article
- TroubleshootingPostgreSQL "no pg_hba.conf entry for host": write the rule that matches, then reloadPostgreSQL 'no pg_hba.conf entry for host'? Read the host, user, database and encryption in the message, add a matching record, check it, then reload.Read article
Risks and limits
- These commands have not been run or lab-tested.
- Granting
CREATEonpublicto 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
PUBLICare 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
- PostgreSQL 15 release notes: Migration to Version 15 — PostgreSQL Global Development Group, release date 2022-10-13 shown for 15.0, accessed 22 November 2026
- PostgreSQL 17 documentation: 5.10. Schemas — PostgreSQL Global Development Group, no date shown (page shows version 17.12), accessed 22 November 2026
- PostgreSQL 17 documentation: 5.8. Privileges — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: GRANT — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: REVOKE — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: CREATE SCHEMA — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: DROP SCHEMA — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: 21.5. Predefined Roles (pg_database_owner) — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: psql — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL 17 documentation: 9.27. System Information Functions (has_schema_privilege, current_user) — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
- PostgreSQL source code, release REL_17_12: src/backend/catalog/aclchk.c — PostgreSQL Global Development Group, no date shown, accessed 22 November 2026
This article was originally published on .