PostGIS – How do you Change a User to be a Super User?

By David Crowther

Question:

Using my PostGIS user credentials I cannot create the extension PostGIS within my Postgres Database. It says that I need to be a Super User to run the Create PostGIS extension. How do you make a PostGIS User a Super User?

Answer: 

If you login to PGAdmin using a User that is currently a Super User, you can then edit the rights of another User to change their privileges. When that User then logs back into PGAdmin they should then be able to run the Create PostGIS extension command. 

However if the user doesn’t currently have these privileges they won’t be able to perform Super User activities i.e. they can’t Create New Roles and or Create New Databases.

Unfortunately, if you have forgotten the password for your Super User login you will find it impossible to edit the privileges of any existing users. However, it is possible to put the Postgres Database into a Single User State and then update your User to have Super User privileges.

The steps to achieve this are detailed below:

1 – Temporarily edit the security on the /DATA/ folder within your Postgres installation location, as you will need to edit a file within this folder later.

If you do not make the above folder editable, when you come to run the command to put the Postgres Database into Single User Mode,.. an error will be returned saying that the Postmaster.PID file cannot be edited.

2 – Access the SERVICES on the machine where Postgres is running and Stop the Postgres Service.

3 – Open the CMD shell: 

• in the command shell change the folder location and navigate to your postgres install folder e.g. at C:\Program Files\PostgreSQL\9.6\bin
• then type the line - postgres.exe --single -D "C:\Program Files/PostgreSQL/9.6/data" TryMapThat
• where TryMapThat is your database name
• this puts your postgres database into Single User mode and the prompt will now read - PostgreSQL stand-alone backend 9.6.3
• now using the new backend> prompt type the line - alter user postgres with superuser;
• where postgres is your User name that you wish to change to be a Super User

The postgres user has now been updated to be a Super User. 

4 – Use the SERVICES to Restart PostgreSQL. 

5 - Open PGAdmin and connect to your Database Instance using the Postgres User 

• in a SQL query window type: ‘CREATE EXTENSION postgis;’
• this will now run successfully and updates the database to be a spatial database.

In addition, if you check the User details, the postgres User will now have the required Super User Privileges.