working with postgres and django apps

how i set up

Working with a Postgresql database in Django is a well documented setup with a lot of good resources available online and in the docs. Here is how I prefer to setup.

Note: Basic security setup can be set as per preferences and is not discussed in detail.

1. Login as the default database superuser and create a user with superuser privileges. I typically name the user the same name as my Django project. In the example below my Django project is called dashboard. Remember the password needs to be the same as the one you use for database password credentials in Django.

In your database settings in Django you might have something similar to the one below; Note that the intended schemas are indicated under "OPTIONS" key.

6. Since I use postgis extension, it is installed by default in the dashboardschema since this is where the default database objects are stored. But for postgis_topology, I will install it first in its default schema, topology then move it later. CREATE EXTENSION postgis;

7. Install the postgis_topology extension then move it to the new schema as explained here

ALTER EXTENSION postgis_topology SET SCHEMA dashboardschema;

-- try to 'dummy' upgrade ALTER EXTENSION postgis_topology UPDATE TO "ANY";

-- then look for any updates ALTER EXTENSION postgis_topology UPDATE;

Move any new extensions to the new default schema as above if they are not created there by default. Please note some extensions with interrelated functionality may need to be installed in the same schema for them to work correctly. Eg some geospatial extensions as explained here must always be installed in the same location/schema as postgis.

8. Since we created the dashboardschema as user dashboard, by default, we have all privileges granted on this schema. There is an implied public role in postgres which all new users inherit and it has some default access to some objects in a database. If you need to revoke this access of this implied public role from accessing the new schema at all, you can run the following command. REVOKE ALL ON SCHEMA dashboardschema FROM public; But I typically just revoke the ability of the implied public role from creating anything on the schema. This would allow another superuser to access the objects in the schema using the SELECT command. REVOKE CREATE ON SCHEMA dashboardschema FROM public;

I also typically prevent the implied public role from creating objects in the default public schema; REVOKE CREATE ON SCHEMA public FROM public;

For other Django projects, rinse and repeat.