User databases

You can have as many databases as you want in a database cluster. A client that connects to the PostgreSQL server can access only the data in a single database, which is specified in the connection string. That means data isn't shared between the databases unless the PostgreSQL foreign data wrapper or DB link 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 isn't present, this means that the privileges are applied to PostgreSQL's special PUBLIC role.

The psql client tool's \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 aren't persistent, and they're 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 characters sets, such as UTF-8. PostgreSQL supports a rich set of character encodings. For the full list of character encodings, 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 datfrozenxid attribute is used by autovaccum operations.
• Storage management: The dattablespace attribute is used to determine to which tablespace the database belongs.
• Concurrency: The datconnlimit attribute is used to determine the number of concurrent 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.

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

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 you can alter the connection to limit the 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