No schema has been selected to create in ... error

Question

I am working on a Linux server that is hosted on Amazon's server and I have completely set up the server. The final thing I am trying to do is host one of my old projects that I created on the server which is in the Flask framework.

I am trying to run the Python file that sets up my database that is required to run my project.

I am using a virtual machine inside the server that will run my project and every time I run the command I get the following error:

(venv) grader@ip-10-20-6-95:/var/www/catalog/catalog$ python setup_database.py
Traceback (most recent call last):
  File "setup_database.py", line 63, in <module>
    Base.metadata.create_all(engine)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/schema.py", line 2848, in create_all
    tables=tables)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1479, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1122, in _run_visitor
    **kwargs).traverse_single(element)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
    return meth(obj, **kw)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 70, in visit_metadata
    self.traverse_single(table, create_ok=True)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 122, in traverse_single
    return meth(obj, **kw)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/ddl.py", line 89, in visit_table
    self.connection.execute(schema.CreateTable(table))
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 662, in execute
    params)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 720, in _execute_ddl
    compiled
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 874, in _execute_context
    context)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1024, in _handle_dbapi_exception
    exc_info
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 196, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 867, in _execute_context
    context)
  File "/var/www/catalog/venv/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) no schema has been selected to create in
 '\nCREATE TABLE users (\n\tid SERIAL NOT NULL, \n\tusername VARCHAR(100), \n\temail VARCHAR(225) NOT NULL, \n\tprofile_pic VARCHAR(225) NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n' {}

I am not sure why I get this error.
The commands I ran to set up PostgreSQL (if that should matter):

$ sudo apt-get install libpq-dev python-dev
$ sudo apt-get install postgresql postgresql-contrib
$ sudo su - postgres
$ psql
# CREATE USER catalog WITH PASSWORD 'sillypassword';
# ALTER USER catalog CREATEDB;
# CREATE DATABASE catalog WITH OWNER catalog;
# \c catalog
# REVOKE ALL ON SCHEMA public FROM public;
# GRANT ALL ON SCHEMA public TO catalog;
# \q
$ exit

How could I fix this problem?

Cyclostome

Since this is still the first search engine result for this error message: If you have a custom search path set via the DATABASES.OPTIONS setting and get this error while running tests, see #31347155 for a solution – Eulau

Answer 1

no schema has been selected to create in

You get this error when your search_path setting has no valid first entry. Either your search path is empty, or the first entry is invalid (after renaming or dropping an existing schema?) or the current role lacks the necessary privileges to use the schema. Postgres does not know in which schema to create the table.

Fix your search_path setting, or grant the necessary privileges to the role, or schema-qualify object names (like: public.users). But fix your search_path in any case. See:

• How does the search_path influence identifier resolution and the "current schema"

Since Postgres 15, the tightened default privileges for the schema public are a common cause for this error message. Quoting the release notes:

• Remove PUBLIC creation permission on the public schema (Noah Misch)

The new default is one of the secure schema usage patterns that Section 5.9.6 has recommended since the security release for CVE-2018-1058. The change applies to new database clusters and to newly-created databases in existing clusters. Upgrading a cluster or restoring a database dump will preserve public's existing permissions.

For existing databases, especially those having multiple users, consider revoking CREATE permission on the public schema to adopt this new default. For new databases having no need to defend against insider threats, granting CREATE permission will yield the behavior of prior releases.

The simple and quick fix is to grant CREATE for the role in question - while being logged into the right database as the role owning the schema (by default the DB owner) or as a superuser.
You'll also want to grant USAGE if that's missing, but USAGE is still granted by default.

GRANT CREATE ON SCHEMA public TO my_role;
-- GRANT CREATE, USAGE ON SCHEMA public TO my_role;  -- ?

Some other options include:

• Open door policy, if there are no security considerations whatsoever. (Typically not advisable in a multi-user environment!)

  GRANT ALL ON SCHEMA public TO my_role;
  

• Make the role member in the owning role. That's also a very liberal approach. my_role inherits (almost) all privileges from owning_role:

  GRANT owning_role TO my_role;
  

• Grant CREATE to PUBLIC. Another liberal option. That's re-establishing the default of Postgres 14 or older:

  GRANT CREATE ON SCHEMA public TO PUBLIC;
  

Or you could make the role owner of the schema or superuser, ...
Or maybe you really want to operate with a different role, or create the object in a different schema, where the original role has privileges.

Bigener

i read in the comments that someone just entered RESET search_path. would something liek that work in my case – Cyclostome

so basically after reading the page that was linked. i can basucally just alter the permissions using ALTER DATABASE test SET search_path = blarg,public; and replace blarg with catalog. ot should i run the following code.. SET search_path = blarg,public; and replace blarg catalog.. – Cyclostome

@Omar_Jandali: That depends on your environment. The default is to set a default search_path in postgresql.conf for the whole DB cluster (and reload). But if you want individual settings per user / db / combination of user & db / session etc. you need settings accordingly. Your problem seems to be that you currently have no valid search_path at all. – Bigener

With that. i create a user named catalog who i set all the postgres setting with. so can i simply run the following command and it would run SET search_path = catalog,public or how can i fix the problem of having no valid search_path – Cyclostome

@Omar_Jandali: Yes, that sets the search_path for the current session. The linked answer has detailed instructions. – Bigener

I ran SET search_path = catalog,public in the venv environment in the posgres user with psql and then i went back and tried to run the database file, but i still get the same exact error – Cyclostome

It only affects the same session this way. – Bigener

Since I didn't have any security concerns this command GRANT ALL ON SCHEMA public TO my_role; did it for me – Bonacci

Answer 2

This issue was answered already: https://dba.stackexchange.com/a/275116/114247

The fix is:

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

Flita

This one worked for me. Apparently I had recreated the public schema and forgot to grant access. – Puttyroot

Answer 3

I found the file created by pg_dump (under postgres 10.7) had

SELECT pg_catalog.set_config('search_path', '', false);

near the top of it. So when importing the file, it manipulated the search path, which persisted throughout the current session.

Commenting that line out (and starting a new session) fixed the problem.

Bidwell

Thanks. This was my issue. – Maddiemadding

Answer 4

You do not have a schema created. Create a schema using CREATE SCHEMA public; then you can execute grant usage on schema public to public; and grant create on schema public to public

Gilpin

Answer 5

I was stuck across same problem and after doing a lot of research, I got the below solution, assuming you are using flask models to create database using sqlalchemy:

while creating models use the below command before defining columns of models:

__table_args__ = {"schema":"schema_name"}

Hope this helps you out.

Infarct