No schema has been selected to create in … error

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.