ERROR: no schema has been selected to create in
This happens when not having USAGE
privilege on any of the schemas of search_path
. By default the pseudo-role public
(all users) has this privilege on the public
schema, so this error happens only after revoking it explicitly with:
revoke usage on schema public from public;
This is required when it's not desirable that people peek into other people schemas, even without selecting data from tables (which is granted through different privileges).
If this REVOKE
hasn't been done in that database, it may have happened in the template database by which new databases are modelled (see CREATE DATABASE
).
When a user has USAGE
privilege, but lacks CREATE
privilege on the schema, it's a different error when trying to create an object: permission denied for schema public.
To check the privileges inside psql, use \dn+ public
.
By default (shown with extended display \x
for readability):
# \dn+ public List of schemas -[ RECORD 1 ]-----+----------------------- Name | public Owner | postgres Access privileges | postgres=UC/postgres | =UC/postgres Description | standard public schema
lack of a rolename before =
means it's for all roles (=public)
Without public USAGE privilege
Name | public Owner | postgres Access privileges | postgres=UC/postgres | =C/postgres Description | standard public schema
Without public USAGE or CREATE privileges
Name | public Owner | postgres Access privileges | postgres=UC/postgres Description | standard public schema
I had pgdump file with creating functions in custom schema and I wanted to switch custom schema name to general public and replaced all occurences with old schema to empty (e.g. myschema.tablename to tablename) And started to receive error
ERROR: no schema has been selected to create in
For my case error happens when at the beginning of dump present line
SELECT pg_catalog.set_config('search_path', '', false);
I have changed second arg to "public"
SELECT pg_catalog.set_config('search_path', 'public', false);
And problem gone
I've tried multiple solutions, and keep getting the same error over and over.
Worse to mention, the error appears to me when I needed to restart migration of my DB by deleting public schema and after creating it again.
The fix was:
grant usage on schema public to public;
grant create on schema public to public;