PG :: InsufficientPrivilege: ERROR: permission denied for schema_migrations rake db: create relationship

Question

I am trying to create a database in Rails. In postgres, I see a development and testing database, however I get a permission error. I tried to follow this link, did not work for me.

Error: PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations : SELECT "schema_migrations".* FROM "schema_migrations"

Rails: permission denied for schema_migrations relation

 default: &default adapter: postgresql encoding: unicode pool: 5 host: localhost username: root password: development: <<: *default database: svp-chicago_development 

I go into postgres and execute these commands.

 psql postgres CREATE USER root CREATE DATABASE svp-chicago_development GRANT ALL PRIVILEGES ON DATABASE svp-chicago_development to root ALTER DATABASE svp-chicago_development OWNER TO root 

When I do \list , I see the database.

Answer 1

I think you skipped creating a password for your user . Try to create a password as follows:

 CREATE USER root WITH PASSWORD 'your_new_password'; CREATE DATABASE svp-chicago_development; GRANT ALL PRIVILEGES ON DATABASE svp-chicago_development to root; ALTER DATABASE svp-chicago_development OWNER TO root; 

Answer 2

I had the same problem and I solved it by adding "Superuser" to the role.

List the users and their privileges first. If you run the commands above, the root user does not have the "Superuser" attributes.

 postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- other | Superuser, Create role, Create DB, Replication, Bypass RLS | {} root | | {} 

Then upgrade root to the Superuser level.

 postgres=# ALTER USER root WITH SUPERUSER; ALTER ROLE 

Again, a list of users and their privileges. Root now has "Superuser."

 postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- other | Superuser, Create role, Create DB, Replication, Bypass RLS | {} root | Superuser | {} 

Hope it helps.

Answer 3

In case someone else comes here with the same problem, I tried many other solutions, and the most suitable for me was the following: Change OWNER for all tables simultaneously in PostgreSQL.

• This works with my user (for example, root or postgres ) had Superuser privilege so trying to reassign OWNED gives an error when trying to assign system objects
• ALTER DATABASE does not work, because the problem is with the ownership of the table object, and not with the database owner. Change of ownership in the database does not apply to another object in this database schema