diesel migration list
Failed with: no schema has been selected to create in
This is the error I'm trying to solve. I recently made some changes to roles and permissions in Postgres, and now my diesel migrations won't run.
cat .env
The result shows that indexer1 is the pg user.
But I've looked at https://dba.stackexchange.com/a/106067/18098 and https://stackoverflow.com/questions/41207259/no-schema-has-been-selected-to-create-in-error and even opened up the permissions as much as I know how (temporarily sacrificing security).
postgres=# show search_path ;
search_path
-----------------
"$user", public
(1 row)
\dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | indexer1=UC/postgres+|
| | =UC/postgres |
(1 row)
\du
List of roles
Role name | Attributes | Member of
--------------------+------------------------------------------------------------+------------
editor | Cannot login | {}
indexer1 | Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readonly | Cannot login | {}
(I've omitted some additional roles that I think are irrelevant.)
UPDATE
I temporarily ran:
ALTER USER indexer1 WITH SUPERUSER;
\du
and now diesel migration list works.
But my question remains because this indexer1 user didn't need to be a superuser before.
What else should I check and adjust?
RESPONSE to first comment
What does the following result tell us?
\connect mainnet_node;
You are now connected to database "mainnet_node" as user "postgres".
mainnet_node=# show search_path ;
search_path
-------------
public
(1 row)
mainnet_node=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | editor=UC/postgres +|
| | readonly=U/postgres |
(1 row)
mainnet_node=# grant usage on schema public to indexer1;
GRANT
mainnet_node=# grant create on schema public to indexer1;
GRANT
mainnet_node=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
public | postgres | postgres=UC/postgres+| standard public schema
| | editor=UC/postgres +|
| | readonly=U/postgres +|
| | indexer1=UC/postgres |
(1 row)
If I remove superuser from indexer1 now, will I probably be "back to normal"?
