User 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 PostgreSQL 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 client tool \l meta-command is used to list all the databases in the database cluster with the associated attributes:

postgres=# \l 
                                        List of databases
      Name | Owner | Encoding | Collate | Ctype | Access privileges 
-----------------+----------------+----------+-------------+-------------+-----------------------
 car_portal | car_portal_app | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
                 | | | | | postgres=CTc/postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
                 | | | | | postgres=CTc/postgres

The database access privileges are the following:

• Create (-C): The create access privilege allows the specified role to create new schemas in the database.
• Connect (-c): When a role tries to connect to a database, the connect permissions are checked.
• Temporary (-T): The temporary access privilege allows the specified role to create temporary tables. Temporary tables are very similar to tables, but they are not persistent, and they are destroyed after the user session is terminated.

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:

• Maintenance: The attribute datfrozenxid is used to determine if a database vacuum is required.
• Storage management: The dattablespace attribute is used to determine the database tablespace.
• Concurrency: The datconnlimit attribute is used to determine the number of allowed connections (-1 means no limits).
• Protection: The datallowconn attribute disables the connection to a database. This is used mainly to protect template0 from being altered.

The psql \c meta-command establishes a new connection to a database and closes the current one. It also accepts a connection string such as username and password.

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

pg_catalog tables are regular tables, thus one can use the SELECT , UPDATE, and DELETE operations to manipulate them. Doing so is not recommended, and needs the utmost attention.

The catalog tables are very useful for automating some tasks; Chapter 12, The PostgreSQL Catalog, is dedicated to pg_catalog. The following example shows how one can alter the connection limit database property by using the ALTER database command. The following example changes the datconnlimit value from -1 to 1:

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)