Rails: PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations

Question

I'm trying to create the database in Rails. In Postgres I see the development and test database, however, I'm getting a permissions error. I've tried to follow this link, didn't work for me.

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

Rails: permission denied for relation schema_migrations

default: &default
  adapter: postgresql
  encoding: unicode
  pool: 5
  host: localhost
  username: root
  password:

development:
  <<: *default
  database: svp-chicago_development

I log into postgres and did 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 is there.

Might be a password thing; try creating the `root` user with a password, and include it in the `config/database.yml` file. — Prakash Murthy, Jul 08 '16 at 16:45
Documenting my recent brain fart here in case it helps someone else :) I was setting up a Rails app on Render.com and got "PG::InsufficientPrivilege" trying to run "bin/rails db:create" in their shell. Then realised I had already created the DB via their UI, and it was "bin/rake db:schema:load" I wanted. — Henrik N, Feb 02 '20 at 19:19

score 66 · Answer 1 · edited May 15 '19 at 15:28

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

First, list users and their privileges. If you followed above commands, root user does not have "Superuser" Attributes.

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

Next, upgrade root to be a "Superuser".

postgres=# ALTER USER root WITH SUPERUSER;
ALTER ROLE

Again, list users and their privileges. Now root 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.

Note: This workaround is [dangerous](https://www.postgresql.org/docs/10/role-attributes.html) and should be used with caution. — Kevin Cooper, Mar 18 '20 at 22:31

score 13 · Accepted Answer · answered Jul 08 '16 at 16:53

13

I guess you missed create password for your user. Try to create password as following:

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;

answered Jul 08 '16 at 16:53

Khanh Pham

2,848
2
28
36

1

I did this and my postgres keeps on giving this same error. Could you please take a look at [link](http://stackoverflow.com/questions/41424654/postgres-permission-denied-for-relation-schema-migrations) – Code-MonKy Jan 04 '17 at 09:25
The following answer worked for me: [Modify OWNER on all tables simultaneously in PostgreSQL](https://stackoverflow.com/questions/1348126/modify-owner-on-all-tables-simultaneously-in-postgresql/13535184#13535184) – ddreliv Sep 07 '18 at 21:11

score 12 · Answer 3 · edited Mar 16 '22 at 22:51

I had this issue when working on a Rails 6 application with PostgreSQL.

The first check is to ensure that you've granted all privileges for a database to the particular user that you want using:

GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myusername;

But in my own case, the cause of the issue was that I created a Database and then granted all privileges to a particular user. After some time, I granted another user the privileges using GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myusername;

So the second user even though I granted all privileges to it, didn't have permissions to perform actions on the database tables.

Here's how I fixed it:

Log into the PostgreSQL console where the database is stored:

sudo -u postgres psql

List all databases in that PostgreSQL database server:

\l

OR

\list

Connect to the database that you want to fix it's permissions:

\c mydatabase

OR

\connect mydatabase

List all tables in the current database using your search_path:

\dt

OR

List all tables in the current database regardless of your search_path:

\dt *.

You will notice that the tables still reference the initial user or role as the owner.

Now you will have to modify the tables to reference the new user or role as the owner.

You can modify each table individually using this:

ALTER TABLE table_name OWNER TO new_owner;

This does not require specifing the old_owner. It is essential when the user is postgres (the default database user) and you want to modify the owner to a new user.

OR modify all the tables simultaneously using this:

REASSIGN OWNED BY old_owner TO new_owner;

This requires specifing the old_owner. It is essential when you have already modified the user from postgres (the default database user) to another user and you want to modify the owner to a new user.

Note: Ensure that you are connected to the database that you want to modify privileges/permissions for, else you might run into errors.

This is exactly what I was facing, I first created the database with my OS user on Mac and then I created another user and it could not do anything to that database, so I had to go over all these steps to `REASSIGN` the owner user and that worked correctly. Thanks! — alexventuraio, Feb 02 '22 at 01:16

score 9 · Answer 4 · answered Jan 16 '20 at 19:41

Also, if you're using a service like Heroku, it's worth checking to see if you have overrun your row limit and write access has been revoked in the database.

You can do that by going to the dashboard, click on the app, click on the postgres service icon, then check the row limit.

score 2 · Answer 5 · answered Sep 07 '18 at 21:17

Just in case someone else comes here with the same issue, I did try many other solutions and the one that worked for me the best was the following: Modify OWNER on all tables simultaneously in PostgreSQL

This worked since my user (e.g. root or postgres) had Superuser privileges so trying REASSIGN OWNED gives error when trying to assign system objects
ALTER DATABASE didn't work since the issue is on a table object ownership and not in the DB ownership. Altering the owner on the DB doesn't propagate to the other object on that DB schema

score 1 · Answer 6 · answered Mar 18 '20 at 23:34

Try listing your tables to see who the owner is. In my case, I had imported data via psql < dump.sql and all the imported tables were owned by postgres instead of my user.

To check this, start psql and enter the command \dt within your database. Look for the following line:

public | schema_migrations | table | postgres

Not good! It's owned by postgres and you'll need to fix that:

You can use @ddreliv's solution here to reassign the owner of every table, or
if you have a dump, simply drop the database and re-import it with the proper user. For example, use sudo -u my_user psql < dump.sql instead of sudo -u postgres psql < dump.sql.

score 1 · Answer 7 · answered Jun 20 '22 at 17:26

Here's how I fixed it:

Log into the PostgreSQL console where the database is stored:

sudo -u postgres psql List all databases in that PostgreSQL database server:

\l

OR

\list Connect to the database that you want to fix it's permissions:

\c mydatabase

OR

\connect mydatabase

List all tables in the current database using your search_path:

\dt

OR

List all tables in the current database regardless of your search_path:

\dt *.

Then giving privilleages to the table :-

One Table

GRANT ALL PRIVILEGES ON TABLE side_adzone TO jerry;

All Tables of schema

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO jerry;

score 0 · Answer 8 · answered Feb 24 '22 at 07:24

for our case (with docker-compose)

remove postgres data

$ docker-compose down # CAUTION: may cause data loss
$ docker volume rm XXX_DB_VOLUME # CAUTION: will cause data loss

then downgrade postgres from 11.11 to 11.8 in docker-compose.yml

xxx_db:
  image: "postgres:11.8"

finally start postgres docker again

$ docker-compose up -d

Problem solved.

score 0 · Answer 9 · answered Nov 15 '22 at 14:20

Adding "Superuser" to the role is a security issue, superusers can drop databases not only the svp-chicago_development pointed out by the OP but any other database existent.

I believe the issue that OP have is related to tables owner as pointed out by @kevin-cooper.

Even if the OP did the commands that he said granting all privileges and owner to the database, if the OP still runs any table creation or restore dump with the postgres credential the tables on the database svp-chicago_development it will have postgres as owner and if the application in running with other user/role it will throw the error mentioned by the OP.

You can check if that's the case in psql by:

\c svp-chicago_development

then listing the tables:

\dt

It will show something like:

 Schema |    Name    | Type  |  Owner   
--------+------------+-------+----------
 public | table_name | table | postgres

If you have a few rows you can manually change the owner of each row by running:

ALTER TABLE table_name OWNER TO new_owner;

If you are running a pg_restore make sure to pass --role so objects will be owned by the role specified.

Jose Paez · Answer 10 · 2023-04-11T22:13:57.320

Solution A

I ran into a similar issue because I was restoring some databases automatically and it didn't get set properly (in terms of rights, it got created with the wrong user)

My solution was to delete the databases and creating each one properly with the right user with

createdb mydb -U username (or just logging into psql with that user and creating them there)

and then doing the

pg_restore --dbname=mydb -d mydb mydb.pgr

and it worked like a charm

Solution B

If that doesn't work then your user might in fact just need more permissions. Upgrade the desired username to be a "superuser".

postgres=# ALTER USER username WITH SUPERUSER;

Confirm that the change worked by getting the list of users and their privileges

postgres=# \du

Rails: PG::InsufficientPrivilege: ERROR: permission denied for relation schema_migrations

10 Answers10

One Table

All Tables of schema

Solution A

Solution B

Linked