ERROR: no schema has been selected to create in

Question

I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not solved. Here is a sample session with a newly created empty database:

mydb=> CREATE TABLE distributors (
mydb(>     did     integer,
mydb(>     name    varchar(40) UNIQUE
mydb(> );
ERROR:  no schema has been selected to create in
mydb=> show search_path;
  search_path   
----------------
 "$user",public
(1 row)

mydb=> create schema public;
ERROR:  schema "public" already exists

Any hint? What should I look for?

Solved. Thanks to the answer of Daniel Vérité I've solved with the following:

grant usage on schema public to public;
grant create on schema public to public;

Are these the default permissions on the public schema?

I have a single user who can access the database, so I think this anyway cannot raise security risks...

I think I should make the same modification on template1. Is it correct? How can I check if the permissions in template1 are correct (say, default values)?

Answer 1

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 publicschema, 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

Answer 2

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;

Answer 3

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

Answer 4

In addition to @Kostanos solution,

I had to put double quotation marks for the schema name (in my case it was a DB migration into a custom schema - not public) in the properties file:

pg.datasource.jdbc-url=jdbc:postgresql://localhost:5432/postgres?currentSchema="MY_NEW_CUSTOM_SCHEMA"

Using this property without the quotation marks kept resulting in the error mentioned in the title.

Answer 5

The main problem is that Postgresql poorly designed its defaults and permissions here: each new database has a public schema, which is by default what every user has access to read AND create objects in when connected to that database, and each new user's search-path defaults to include public.

Being a bit security conscious, one solution for the DBA is to modify template1 as follows:

 REVOKE CREATE ON SCHEMA public FROM public ;

and for the even more paranoid:

 REVOKE USAGE ON SCHEMA public FROM public ;

But in Postgresql's InfiniteWisdom™, revoking these also blocks permissions to the owner of the database.

The owner can always create a new schema, and alter the database so that connecting users will use that schema. But only the owner of the database will be able to use the schema:

 CREATE SCHEMA default ;

 ALTER DATABASE this_database_name SET SEARCH_PATH to default ; 

Alternatively, you can treat public as most of us do: the default schema for a database. In this case, revoke the permissions as the paranoid would, and change its ownership to the owner of the database.

 CREATE USER some_joe ; 

 CREATE DATABASE new_database OWNER some_joe ;

 REVOKE ALL ON SCHEMA public from public ; 

 ALTER SCHEMA public OWNER TO some_joe ;

This latter recipe (especially the last two lines) is useful if the database objects already exist in the schema. Otherwise, the superuser could drop the public schema, and let the non-privileged owner of the database recreate it.

Answer 6

This was happening to me after I tried connecting to a newly-imported DB dump with the user I had created previously. What solved it was granting the permissions to the user: GRANT ALL ON SCHEMA public TO custom_user;

Answer 7

This works for me

grant usage on schema <schema-name> to <user>;
grant create on schema <schema-name> to <user>;