Postgresql Database Template Restriction

You may want to make sure that nobody changes your new template database. One way to do this is to not allow any non-superusers to connect to the database by setting the user limit to zero. This can be done either at creation time, or afterwards, like so:

--check the database which one you choose as template

postgres=# CREATE DATABASE my_template TEMPLATE account CONNECTION LIMIT 0;
CREATE DATABASE

 --above  account is existing template database

-- if you not set connection_limit you can set use "alter database command"

ALTER DATABASE my_template CONNECTION LIMIT 0;

--connect as u1 non superuser

[postgres@r1 ~]$ psql -U u1 -d postgres
Password for user u1: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.

postgres=> \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 clone     | Create DB                                      | {}
 john      | Superuser                                      | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 u1        |                                                | {}
 u10       |                                                | {}
 u11       |                                                | {}
 u2        | Superuser                                      | {}
 u3        |                                                | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u7        |                                                | {}
 u8        |    

--If you try to connect my_template database as non_superuser it will throw errors becouse connection_limit is "0"

postgres=> \c my_template                                                  
FATAL:  too many connections for database "my_template"
Previous connection kept

You may want to go further by granting the database official "template" status by adjusting the datistemplate column in the pg_database table:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'my_template';

This will allow anyone to use the database as a template, as long as they have the CREATEDB privilege. You can also restrict *all* connections to the database, even superusers, by adjusting the datallowconn column:

postgres=# UPDATE pg_database SET datallowconn = FALSE WHERE datname = 'my_template';
UPDATE 1

--connect as superuser but you cannot connect to my_template database becouse datallowconn = FALSE

postgres=# \c my_template                                                       
FATAL:  database "my_template" is not currently accepting connections
Previous connection kept