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)?