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;
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

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?