How do we access PostgreSQL?

Connecting to the database is most people's first experience of PostgreSQL, so we want to make it a good one. Let's do it now and fix any problems we have along the way. Remember that a connection needs to be made securely, so there may be some hoops for us to jump through to ensure that the data we wish to access is secure.

Before we can execute commands against the database, we need to connect to the database server, giving us a session.

Sessions are designed to be long-lived, so you connect once, perform many requests, and eventually disconnect. There is a small overhead during connection. It may become noticeable if you connect and disconnect repeatedly, so you may wish to investigate the use of connection pools. Connection pools allow pre-connected sessions to be served quickly to you when you wish to reconnect.

First, catch your database. If you don't know where it is, you'll probably have difficulty accessing it. There may be more than one database, and you'll need to know the right one to access and have the authority to connect to it.

You need to specify the following parameters to connect to PostgreSQL:

• Host or host address
• Port
• Database name
• User
• Password (or other means of authentication, if any)

To connect, there must be a PostgreSQL server running on host, listening to port number port. On that server, a database named dbname and a user named user must also exist. The host must explicitly allow connections from your client (this is explained in the next recipe), and you must also pass authentication using the method the server specifies; for example, specifying a password won't work if the server has requested a different form of authentication.

Almost all PostgreSQL interfaces use the libpq interface library. When using libpq, most of the connection parameter handling is identical, so we can discuss that just once.

If you don't specify the preceding parameters, PostgreSQL looks for values set through environment variables, which are as follows:

• PGHOST or PGHOSTADDR
• PGPORT (set this to 5432 if it is not set already)
• PGDATABASE
• PGUSER
• PGPASSWORD (this is definitely not recommended)

If you specify the first four parameters somehow but not the password, then PostgreSQL looks for a password file, discussed in the Avoiding hardcoding your password recipe.

Some PostgreSQL interfaces use the client-server protocol directly, so the way the defaults are handled may differ. The information we need to supply won't vary significantly, so check the exact syntax for that interface.

Starting from PostgreSQL 9.2, connection details can also be specified using a URI format, as follows:

psql postgresql://myuser:mypasswd@myhost:5432/mydb

This specifies that we will connect to PostgreSQL using the myhost host, 5432 port, mydb database name, myuser user, and mypasswd password.

PostgreSQL is a client-server database. The system it runs on is known as the host. We can access the PostgreSQL server remotely through the network. However, we must specify host, which is a hostname, or hostaddr, which is an IP address. We can specify a host as localhost if we wish to make a TCP/IP connection to the same system. It is often better to use a Unix socket connection, which is attempted if the host begins with a slash (/) and the name is presumed to be a directory name (default is /tmp).

On any system, there can be more than one database server. Each database server listens to exactly one well-known network port, which cannot be shared between the servers on the same system. The default port number for PostgreSQL is 5432, which has been registered with IANA and is uniquely assigned to PostgreSQL (you can see it used in the /etc/services file on most *nix servers). The port number can be used to uniquely identify a specific database server if many exist. The Internet Assigned Numbers Authority (IANA) (http://www.iana.org), is the organization that coordinates the allocation of available numbers for various Internet protocols.

A database server is also sometimes known as a database cluster, because the PostgreSQL server allows you to define one or more databases on each server. Each connection request must identify exactly one database identified by its dbname. When you connect, you will be able to see only the database objects created within that database.

A database user is used to identify the connection. By default, there is no limit on the number of connections for a particular user; in a later recipe, we will cover how to restrict that. In the more recent versions of PostgreSQL, users are referred to as login roles, though many clues remind us of the earlier nomenclature, and that still makes sense in many ways. A login role is a role that has been assigned the CONNECT privilege.

Each connection will typically be authenticated in some way. This is defined at the server level: client authentication will not be optional at connection time, if the administrator has configured the server to require it.

Once you've connected, each connection can have one active transaction at a time and one fully active statement at any time.

The server will have a defined limit on the number of connections it can serve, so a connection request can be refused if the server is oversubscribed.

If you are already connected to a database server with psql and you want to confirm that you've connected to the right place and in the right way, you can execute some, or all, of the following commands. Here is the command that shows the current_database:

SELECT current_database();

The following command shows the current_user ID:

SELECT current_user;

The next command shows the IP address and port of the current connection, unless you are using Unix sockets, in which case both values are NULL:

SELECT inet_server_addr(), inet_server_port();

A user's password is not accessible using general SQL for obvious reasons.

You may also need the following:

SELECT version();

From PostgreSQL version 9.1 onwards, you can also use psql's new meta-command, \conninfo. It displays most of the preceding information in a single line:

postgres=# \conninfo

You are connected to database postgres as user postgres via socket in /var/run/postgresql at port 5432.

There are many other snippets of information required to understand connections. Some of them are mentioned in this chapter, and others are discussed in Chapter 6, Security. For further details, refer to the PostgreSQL server documentation.