Steps to Troubleshoot "django.db.utils.ProgrammingError: permission denied for relation django_migrations"

Question

What are some basic steps for troubleshooting and narrowing down the cause for the "django.db.utils.ProgrammingError: permission denied for relation django_migrations" error from Django?

I'm getting this message after what was initially a stable production server but has since had some changes to several aspects of Django, Postgres, Apache, and a pull from Github. In addition, it has been some time since those changes were made and I don't recall or can't track every change that may be causing the problem.

I get the message when I run python manage.py runserver or any other python manage.py ... command except python manage.py check, which states the system is good.

Are the database settings in your settings.py file correct? Does that user exist in Postgres? Is the password correct? — Resley Rodrigues, Aug 14 '16 at 17:18
@ResleyRodrigues I'm running manage.py under the user `ubuntu` but my virtual environment sets my DATABASE_USER env variable as `dbuser`, which is also used in the DATABASES definition in my production settings file for Django. In Postgres, the `dbuser` exists and has been granted, I believe, the correct privileges. `dbuser` has attribute _Create DB_ as a postgres user and has access privilege for the db of _CTc_. — user3062149, Aug 15 '16 at 00:14
Does [this help](http://stackoverflow.com/questions/32471976/getting-error-while-syncdb-django-db-utils-programmingerror-permission-denied-f)? — Resley Rodrigues, Aug 15 '16 at 07:33
Yes, I looked at the previous question/answer and my previous comment incorporates what I found. Essentially, I didn't see anything that suggested my postgres permissions were set incorrectly for `dbuser`. — user3062149, Aug 17 '16 at 01:44

score 156 · Accepted Answer · edited May 23 '17 at 10:30

156

I was able to solve my issue based on instructions from this question. Basically, postgres privileges needed to be re-granted to the db user. In my case, that was the user I had setup in the virtual environment settings file. Run the following from the commandline (or within postgres) where mydatabase and dbuser should be your own database and user names:

psql mydatabase -c "GRANT ALL ON ALL TABLES IN SCHEMA public to dbuser;"
psql mydatabase -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public to dbuser;"
psql mydatabase -c "GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to dbuser;"

edited May 23 '17 at 10:30

Community

1
1

answered Aug 22 '16 at 02:50

user3062149

4,173
4
17
26

1

wow, thank you so! Been looking everywhere for a solution. I got this problem when I had already a migrated database and then I tried to setup geodjango. – qasimalbaqali Mar 09 '17 at 22:35
2

I have this problem when I created a database with one user and then change the owner of the database. The tables keep the old owner what leads to this error. Do fix I dropped the database, recreated it with posgres user, grant all privileges to the new user and run the migrations. – geckos Mar 24 '18 at 18:20
1

I didn't know how to do it, was easier to recreate the whole database again =) – geckos Apr 02 '18 at 19:57
People following the Saleor official documentation on Installing Saleor on Linux might need this answer. I did. – Rik Schoonbeek Mar 08 '19 at 19:27
@user3062149, would you please also state the obvious fact that one need to be signed in to database as owner to great permissions to other user? for future newbies like myself. – hm6 Dec 12 '20 at 19:10
2

This answer is not valid for PostgreSQL 15. The new, correct answer for 15 onwards is below: https://stackoverflow.com/a/74410091/1033217 – Utkonos Nov 12 '22 at 03:06
For postgres 15 https://stackoverflow.com/a/75428951/7413175 – waranlogesh Feb 12 '23 at 17:44

score 16 · Answer 2 · answered Aug 28 '18 at 15:01

As mentioned by @user3062149, this is likely caused by attempting to migrate a database table for which Django's psycopg2 user is not the table owner. For instance, if you have in your project's settings.py

DATABASES = {
    'default': {
        'USER': 'my_username',
        # ...

You will need to check that the table involved in the Django migration is owned by my_username. To do this in psql, you can use SELECT * FROM pg_tables ORDER BY tableowner;. This uses the view pg_tables, which "provides access to useful information about each table in the database." pg_tables is a part of Postgres' system catalogs, the place where a relational database management system stores schema metadata.

Say that the table in question is owned by other_username (not my_username).

To update the owner, you then need to call psql with --username=other_username, then change the owner:

ALTER TABLE public.<table_name> OWNER TO my_username;

score 6 · Answer 3 · answered Nov 12 '22 at 03:03

The answers that change privileges with relation to public are no longer valid for PostgreSQL 15. If you are using Django with PGSQL version 15, the following is now the correct process for database setup.

su - postgres
psql -c "CREATE DATABASE mydatabase"
psql -c "CREATE USER myuser WITH PASSWORD 'somepassword'"
psql -d mydatabase -c "CREATE SCHEMA myschema AUTHORIZATION myuser"
psql -c "ALTER ROLE myuser SET client_encoding TO 'utf8'"
psql -c "ALTER ROLE myuser SET default_transaction_isolation TO 'read committed'"
psql -c "ALTER ROLE myuser SET timezone TO 'UTC'"

Over on the Django side, in myproject/settings.py there is an additional setting (OPTIONS) needed in the DATABASES section:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
                'options': '-c search_path=myschema'
            },
        'NAME': 'mydatabase',
        'USER': 'myuser',
        'PASSWORD': 'somepassword',
        'HOST': 'localhost'
    }
}

If the above has been followed, the results should look normal and as expected:

$ python manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying auth.0010_alter_group_name_max_length... OK
  Applying auth.0011_update_proxy_permissions... OK
  Applying auth.0012_alter_user_first_name_max_length... OK
  Applying sessions.0001_initial... OK

Pete Dermott · Answer 4 · 2020-07-09T12:48:31.660

5

If you receive this error and are using the Heroku hosting platform its quite possible that you are trying to write to a Hobby level database which has a limited number of rows.

Heroku will allow you to pg:push the database even if you exceed the limits, but it will be read-only so any modifications to content won't be processed and will throw this error.

edited Jul 09 '20 at 12:48

answered Jul 09 '20 at 12:36

Pete Dermott

663
1
9
20

Nick Brady · Answer 5 · 2021-08-18T19:46:13.257

For people still having issues using PostgresQL, I realized I needed to update schema public for each database I was connecting to. I had a dev and a staging database I was trying to use the same user for in my case.

Each database has it's own public schema. It's just a namespace in the database. If you're trying to use the user for another database, then you need to connect to each database you want to grant privileges to, and run your GRANT commands there. When you connect with a session, it's connecting to a particular database, and the GRANT commands only apply to that particular database.

RDS might look like this: The DB session .. say

psql -U postgres -h some.host.com -p 5432 --password db_name
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

will only apply to DB name you connected to (say, db green arrow goes to, not the other). If you want to have the user work with Django for something OTHER than db_name, then you need to connect to that database and grant prileges on the public schema there too.

\c other_db

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

@DrayenDörff The key is that you connect to the database you want modify. If you have multiple databases on a single postgresql server, you can't just run it and assume it'll affect all of your databases without doing something fancier such as roles. E.g. `/c db_name` and then run the commands. — Nick Brady, Nov 01 '21 at 12:43

score 5 · Answer 6 · answered Feb 12 '23 at 17:43

5

For postresql 15 and Django

sudo -u postgres psql database_name
GRANT CREATE ON SCHEMA public TO your_dbuser;

answered Feb 12 '23 at 17:43

waranlogesh

874
9
11

Steps to Troubleshoot "django.db.utils.ProgrammingError: permission denied for relation django_migrations"

6 Answers6

Linked