Connecting to the PostgreSQL server

How do we access PostgreSQL?

Connecting to the database is the first experience of PostgreSQL for most people, 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 secure, 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 to give 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 the 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 quickly served to you when you wish to reconnect. We will discuss them in Chapter 4, Server Control.

Getting ready

First, cache 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:

• A host or host address
• A port
• A database name
• A user
• A password (or other means of authentication; but only if requested)

To connect, there must be a PostgreSQL server running on that host and listening to the port with that number. On that server, a database and a user with the specified names must also exist. Furthermore, the host must explicitly allow connections from your client (as explained in the Enabling access for network/remote users recipe), and you must also pass the authentication step using the method the server specifies – for example, specifying a password won’t work if the server has requested a different form of authentication. Note that you might not need to provide a password at all if PostgreSQL can recognize that your user is already authenticated by the OS; this is called peer authentication. After showing an example in this recipe, we will discuss it fully in the next recipe: Enabling access for network/remote users (despite not being a network/remote connection method).

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 by us, nor by the PostgreSQL documentation, even if it still exists)

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

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

Connection details can also be specified using a connection string, as in this example:

psql "user=myuser host=myhost port=5432 dbname=mydb password=mypasswd"

or alternatively using a Uniform Resource Identifier (URI) format, as follows:

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

Both examples specify that we will connect the psql client application to the PostgreSQL server at the myhost host, on port 5432, with the database name mydb, user myuser and password mypasswd.

How to do it...

In this example, Afroditi is a database administrator who needs to connect to PostgreSQL to perform some maintenance activities. She can SSH to the database server using her own username afroditi, and DBAs are given sudo privileges to become the postgres user, so she can simply launch psql as the postgres user:

afroditi@dbserver1:~$ sudo -u postgres psql
psql (16.0 (Debian 16.0-1.pgdg120+1))
Type "help" for help.
postgres=#

Note that psql was launched as the postgres user, so it used the postgres user for the database connection, and that psql on Linux attempts a Unix socket connection by default. Hence, this matches peer authentication.

How it works…

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. Rather than using TCP/IP to localhost, it is usually 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 (the 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 servers on the same system. The default port number for PostgreSQL is 5432, which has been registered with the Internet Assigned Numbers Authority (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 any exist. 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 only be able to see 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 the Enabling access for network/remote users recipe, we will cover how to restrict that. In more recent versions of PostgreSQL, users are referred to as login roles, although 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.

There’s more…

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

This is just one of several ways to check the database software version; please refer to the What version is the server? recipe in Chapter 2, Exploring the Database. You can also use the new psql meta-command, \conninfo. This 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.

See also

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, which we provided a link to earlier in this chapter.