By default, when a database is created, it is cloned from a template database called template1. The template database contains a set of tables, views, and functions, which is used to model the relation between the user defined database objects. These tables, views, and functions are a part of the system catalog schema called pg_catalog.

The PostgreSQL server has two template databases:

One can have as many databases as one wants in a database cluster. A client connection to the PostgreSQL server can access only the data in a single database that is specified in the connection string. That means that data is not shared between the databases, unless the postgres foreign data wrapper or dblink extensions are used.

Every database in the database cluster has an owner and a set of associated permissions to control the actions allowed for a particular role. The privileges on PostgreSQL objects, which include databases, views, tables, and sequences, are represented in the psql client as follows:

<user>=<privileges>/granted by

If the user part of the privileges is not present, it means that the privileges are applied to the PostgreSQL special PUBLIC role.

The psql meta-command l is used to list all the databases in the database cluster with the associated attributes:

postgres=# x
Expanded display is on.
postgres=# l
List of databases
-[ RECORD 1 ]-----+----------------------------
Name              | postgres
Owner             | postgres
Encoding          | UTF8
Collate           | English_United Kingdom.1252
Ctype             | English_United Kingdom.1252
Access privileges |
-[ RECORD 2 ]-----+----------------------------
Name              | template0
Owner             | postgres
Encoding          | UTF8
Collate           | English_United Kingdom.1252
Ctype             | English_United Kingdom.1252
Access privileges | =c/postgres
                  | postgres=CTc/postgres
-[ RECORD 3 ]-----+----------------------------
Name              | template1
Owner             | postgres
Encoding          | UTF8
Collate           | English_United Kingdom.1252
Ctype             | English_United Kingdom.1252
Access privileges | =c/postgres
                  | postgres=CTc/postgres

The database access privileges are the following:

In the preceding example, the postgres database has no explicit privileges assigned. Also notice that, the PUBLIC role is allowed to connect to the template1 database by default.

Encoding allows you to store text in a variety of character sets, including one byte character sets such as SQL_ASCII or multiple byte character sets such as UTF-8. PostgreSQL supports a rich set of character encodings. For the full list of character encodings, please visit http://www.postgresql.org/docs/current/static/multibyte.html.

In addition to these attributes, PostgreSQL has several other attributes for various purposes, including the following:

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

The catalog tables are very useful for automating some tasks, such as checking for tables with the same name, duplicate indexes, missing constraints, and so on. One should not change the catalog manually except in rare cases; there are SQL commands to perform the same task. The following example shows how one can alter the connection limit database property by using the ALTER database command, or by using the UPDATE statement, which is not recommended. The following example changes the datconnlimit value from -1 to 1, and then reset the value to -1 again:

postgres=# SELECT datconnlimit FROM pg_database WHERE datname= 'postgres';
 datconnlimit
--------------
           -1
(1 row)

postgres=# ALTER DATABASE postgres CONNECTION LIMIT 1;
ALTER DATABASE
postgres=# SELECT datconnlimit FROM pg_database WHERE datname= 'postgres';
 datconnlimit
--------------
            1
(1 row)

postgres=# UPDATE pg_database SET datconnlimit=-1 WHERE datname='postgres';
UPDATE 1
postgres=# SELECT datconnlimit FROM pg_database WHERE datname= 'postgres';
 datconnlimit
--------------
           -1
(1 row)

Roles belong to the PostgreSQL server cluster and not to a certain database. A role can either be a database user or a database group. The role concept subsumes the concepts of users and groups in the old PostgreSQL versions. For compatibility reasons, with PostgreSQL version 8.1, the CREATE USER and CREATE GROUP SQL commands are still supported:

ER representation of role entity in PostgreSQL

The roles have several attributes, which are as follows:

When a database cluster is created, the postgres super user role is created by default.

A role can be a member of another role to simplify accessing and managing the database permissions; for example, one can create a role with no login, also known as group, and grant its permissions to access the database objects. If a new role needs to access the same database objects with the same permissions as the group, the new role could be assigned a membership to this group. This is achieved by the SQL commands GRANT and REVOKE, which are discussed in detail in Chapter 8, PostgreSQL Security.

Tablespace is a defined storage location for a database or database objects. Tablespaces are used by administrators to achieve the following:

The SQL statement to create tablespace is CREATE TABLESPACE.

Template procedural language is used to register a new language in a convenient way. There are two ways to create a programing language; the first way is by specifying only the name of the programing language. In this method, PostgreSQL consults the programing language template and determines the parameters. The second way is to specify the name as well as the parameters. The SQL command to create a language is CREATE LANGUAGE.

The PostgreSQL settings control different aspects of the PostgreSQL server, including replication, write-ahead logs, resource consumption, query planning, logging, authentication, statistic collection, garbage collection, client connections, lock management, error handling, and debug options.

The following SQL command shows the number of PostgreSQL settings. Note that this number might differ slightly between different installations as well as customized settings:

postgres=# SELECT count(*) FROM pg_settings;
 count
-------
   239
(1 row)

postgres=# SET default_transaction_read_only to on;
SET
postgres=# CREATE TABLE test_readonly AS SELECT 1;
ERROR:  cannot execute SELECT INTO in a read-only transaction