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?

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.

Answer 2

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.

Answer 3

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;

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

Answer 5

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.