Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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;
 --above  account is existing template database
-- if you not set connection_limit you can set use "alter database command"
--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';
--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


Popular posts from this blog

rman interview and scenario based questions and answer and also rman(backup and recovery) discussions panel

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL pgBadger