13

What my settings.py for DB looks like:

ALLOWED_HOSTS = ['*']

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'fishercoder',
        'USER': 'fishercoderuser',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

I have created a new and empty db named "fishercoder" this way:

psql -U postgres
create database fishercoder; 
ALTER USER postgres with password 'badpassword!'; 
CREATE USER fishercoderuser WITH PASSWORD 'password';
ALTER ROLE fishercoderuser SET client_encoding TO 'utf8';
ALTER ROLE fishercoderuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE fishercoderuser SET timezone TO 'PST8PDT';
GRANT ALL PRIVILEGES ON DATABASE fishercoder TO fishercoderuser;

Then I've imported my other SQL dump into this new DB successfully by running:psql -U postgres fishercoder < fishercoder_dump.sql

Then I tried to run ./manage.py makemigrations on my Django project on this EC2 instance, but got this error:

Traceback (most recent call last):
  File "/home/ubuntu/myprojectdir/myprojectenv/lib/python3.6/site-packages/django/db/backends/utils.py", line 86, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.InsufficientPrivilege: permission denied for relation django_migrations

I found these three related posts on SO: One, two and three

I tried the commands they suggested:

postgres=# GRANT ALL ON ALL TABLES IN SCHEMA public to fishercoderuser;
GRANT
postgres=# GRANT ALL ON ALL SEQUENCES IN SCHEMA public to fishercoderuser;
GRANT
postgres=# GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to fishercoderuser;
GRANT

no luck, I then restarted my postgresql db: sudo service postgresql restart when I tried to run migrations again, still faced w/ the same error.

More debug info below:

ubuntu@ip-xxx-xxx-xx-xx:~$ psql -U postgres
Password for user postgres:
psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

postgres=# \dt django_migrations
Did not find any relation named "django_migrations".
postgres=# \d django_migrations
Did not find any relation named "django_migrations".
postgres=#  \dp django_migrations
                            Access privileges
 Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)

postgres=# SHOW search_path; \dt *.django_migrations
   search_path
-----------------
 "$user", public
(1 row)

Did not find any relation named "*.django_migrations".

postgres=# \dn+ public.
                                     List of schemas
        Name        |  Owner   |  Access privileges   |           Description
--------------------+----------+----------------------+----------------------------------
 information_schema | postgres | postgres=UC/postgres+|
                    |          | =U/postgres          |
 pg_catalog         | postgres | postgres=UC/postgres+| system catalog schema
                    |          | =U/postgres          |
 pg_temp_1          | postgres |                      |
 pg_toast           | postgres |                      | reserved schema for TOAST tables
 pg_toast_temp_1    | postgres |                      |
 public             | postgres | postgres=UC/postgres+| standard public schema
                    |          | =UC/postgres         |
(6 rows)

Any ideas how to fix this?

Fisher Coder
  • 3,278
  • 12
  • 49
  • 84
  • Are you sure you know where django_migrations is and who owns it? In psql what does \dt django_migrations show? Also \dp django_migrations? – Adrian Klaver Jun 19 '20 at 19:02
  • thanks @AdrianKlaver, I ran these two commands and added output into the question just now. Interestingly I had another EC2 instance where my other Django project runs perfectly fine and these two commands show exactly the same result. – Fisher Coder Jun 19 '20 at 19:26
  • Try: SHOW search_path; \dt *.django_migrations – Adrian Klaver Jun 19 '20 at 19:40
  • added output of this command into question just now. @AdrianKlaver – Fisher Coder Jun 19 '20 at 19:45
  • Hmm, I thought it was choking on the table, but the table does not exist. Going back through above I do not see any ```GRANT```s to fishercoderuser on the schema itself. Try \dn+ public. Can you as fishercoderuser do ```CREATE TABLE``` in public e.g public.some_table? What does the Postgres log show Django is trying to do when you get the error? – Adrian Klaver Jun 19 '20 at 20:58
  • I've just added the output of `dn+ public` into the question. what's the second command you want me to try? – Fisher Coder Jun 19 '20 at 21:18
  • As user fishercoderuser try to create a table in the public schema. Also what does the Postgres logs show is being done when you get the permissions error. – Adrian Klaver Jun 19 '20 at 21:21
  • Try connecting to db with your user and performing actions - creating tables etc. User might be missing `login` or `connect on database` privileges. In simple case it is easier to create user with superuser and create db with him as an owner. – Oleg Russkin Jun 20 '20 at 12:14
  • 1
    Were you able to fix this? I am experiencing the same issue trying to import a db to make a project run locally. Also get `django.db.utils.ProgrammingError: permission denied for table django_migrations` as soon as I import the database dump. – MadPhysicist Jul 25 '20 at 09:18
  • i have faced same issue. but after changing the database user to superuser works for me. don't know it will work you or not guys. – sharif_42 Mar 24 '21 at 12:30

3 Answers3

19

I had the following error.

psycopg2.errors.InsufficientPrivilege: permission denied for table django_migrations

Granted privileges of superuser 'postgres' to the one I was working with.

For me, this command worked:

GRANT postgres TO <user>;

U. Bajpai
  • 191
  • 1
  • 4
  • If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. - [From Review](/review/late-answers/30444124) – Beso Nov 27 '21 at 11:26
  • 3
    This is the only thing that worked for me. I wonder why. – MadPhysicist Oct 06 '22 at 16:16
8

After restoring database, all tables will be owned by postgres user.

You need to do this grant:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO <user>;

I got the same problem and that solved.

You'll need also to grant other privileges to Django user:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO <user>;
jrvidotti
  • 520
  • 1
  • 6
  • 12
0

Please try using the below command, this worked for me

GRANT rds_superuser TO username;
nishit chittora
  • 974
  • 13
  • 20
Arpit
  • 1