In Postgres when an administrator creates a database in the background Postgres is copying a template database.
As default the new database is a copy of a database that is created at install called template1. This database is usually hidden and even if you are logged in as the Postgres superuser this database is not listed when you search on the server for all available database.
Normally an administrator that is limited to dbcreator rights does NOT have rights to install extensions. This is particularly an issue when dealing with geography administrators that need to create databases with PostGIS and pgrouting extensions but you don’t want to elevate these administrators to superusers.
Superuser administrators can however install these extensions on the template database which means that when an administrator with dbcreator rights logins in and runs the create database command the new database created will inherit the extensions postgis and pgrouting (and any other extensions you add to template1).
Steps to Alter template1:
Connect to the PostgreSQL Instance as a Superuser:
Use a superuser account (e.g., postgres) to connect to the PostgreSQL instance.
Allow template1 to be modified:
By default, the template databases are marked as “databases not allowed for connection.” You need to change this setting temporarily.
ALTER DATABASE template1 IS_TEMPLATE = false;
Connect to template1:
Now, connect to the template1 database.
\c template1
Install the PostGIS and pgRouting Extensions in template1:
Once connected to template1, install the extensions:
CREATE EXTENSION postgis; CREATE EXTENSION pgrouting;
Restore template1 to be a template database:
After installing the extensions, you need to restore template1 as a template:
ALTER DATABASE template1 IS_TEMPLATE = true;
Prevent Modifications to template1 (Optional):
To prevent accidental changes to template1 in the future, you can revoke connection permissions:
REVOKE CONNECT ON DATABASE template1 FROM public;
Future Databases Will Inherit PostGIS and pgRouting:
Now, whenever a new database is created using the template1 template, it will automatically inherit the PostGIS and pgRouting extensions:
CREATE DATABASE new_database_name;
— This new database will have PostGIS and pgRouting installed.
Caution:
Modifying the default template1 means all databases created in the future will have the extensions, which might not be ideal if you don’t want PostGIS or pgRouting in every database. You could alternatively create a custom template and specify it when creating spatial databases.
Example of creating a custom template:
CREATE DATABASE template_postgis TEMPLATE template1; \c template_postgis CREATE EXTENSION postgis; CREATE EXTENSION pgrouting; ALTER DATABASE template_postgis IS_TEMPLATE = true;
This way, you can create new spatial databases by specifying this custom template:
CREATE DATABASE spatial_db TEMPLATE template_postgis;
This approach provides more control over which databases include the extensions.