- How to set up a remote PostgreSQL connection
- Choose a remote PostgreSQL connection method
- Method #1: Set up an SSH tunnel
- Method #2: Set up a direct connection
- Setting up a remote Postgres database server on Ubuntu 18.04
- Installing Postgres
- Create user
- Allow remote access
- Connect to Postgres remotely
- Connecting to PostgreSQL databases
How to set up a remote PostgreSQL connection
This article describes how to use a local computer to connect to your PostgreSQL databases stored remotely on A2 Hosting servers.
Choose a remote PostgreSQL connection method
You can use either of the following methods to access your PostgreSQL databases remotely:
- SSH tunnel: This is the more secure method. You set up an SSH tunnel that forwards a port on your local computer to the remote PostgreSQL server. The PostgreSQL traffic is encrypted by the SSH tunnel.
- Direct connection: You can set up a direct connection between your local computer and the remote PostgreSQL server on port 5432.
After you have set up a remote PostgreSQL connection, you can use a client application such as psql to work with your databases.
Method #1: Set up an SSH tunnel
The procedure you follow to set up an SSH tunnel between your local computer and the A2 Hosting server depends on the local computer’s operating system.
In order to use an SSH tunnel on a computer running Microsoft Windows, you need an SSH client program. A2 Hosting recommends using PuTTY, which you can download here. After you have downloaded the PuTTY executable to your local computer, you can set up an SSH tunnel.
To set up an SSH tunnel on a computer running Microsoft Windows:
- Start PuTTY.
- In the Category pane, expand Connection , expand SSH , and then click Tunnels .
- In the Source port text box of the Port Forwarding section, type 5432 . This is the local port to forward.
Mac OS X and Linux
To set up an SSH tunnel on a computer running Mac OS X or Linux:
- Open a terminal window and type the following command at the command line. Replace username with your A2 Hosting username, and replace example.com with your site’s domain name:
Type your password, and then press Enter. When the remote server’s command line prompt appears, the SSH tunnel is established and you can use your PostgreSQL client applications on the local computer.
For example, if the psql client application is installed on your local computer, you can type the following command to access a database on the A2 Hosting server. Replace username with the database user, and dbname with the name of the database:
Method #2: Set up a direct connection
To set up a direct connection between your local site and the PostgreSQL server, you must configure a client application. There are several PostgreSQL client applications available, but for all of them, you must provide the following information to establish a remote connection:
Setting up a remote Postgres database server on Ubuntu 18.04
April 10, 2020 3 min read 953
Postgres is a powerful relational database management system, it can handle large workloads from a single machine to that of a data center. It is highly scalable and widely popular. In this article, we will be learning how to set up a remote Postgres database server to use for your projects. This article setup will allow Postgres connection from any IP address and will not cover specific/authorized IP connection.
- Familiarity with the command line interface
- An Ubuntu server, you can quickly provision one from DigitalOcean or any cloud provider
- A lot of patience
- Postgres installed on a local machine
In this step, you will be installing Postgres on your server. The first thing to do is SSH into your server by running:
server_user is your server user you would like to log in with
server_ip is the IP address of your server
Then input your relevant user password or SSH key password if any. Next, update your server packages and dependencies by running:
When that is done, install Postgres by running:
This will install Postgres along with its associated dependencies. When the process is complete, switch the user to postgres to be able to execute Postgres commands with Postgres default user by running:
The server user will be switched from root to postgres . You can access the Postgres shell by running:
You will be shown something similar to this:
In this step, you will be creating a new user that will be used to access your Postgres database remotely. To create a new user, exit the Postgres shell by executing:
While still being logged in as postgres run the following command to create a new user:
A prompt will be shown to you asking you to input your desired user role, name, password, and if you want the user to be a superuser. Here is an example:
I named my user role cleopatra and I made my user a superuser. A superuser is a user that has all the privileges available on a Postgres instance. Next, we will be assigning cleopatra to a database. To do this, run the following command:
This command above will create a new database named egypt and assign cleopatra to be the database user.
Allow remote access
In this step, we will look at how to configure Postgres to accept external connections. To begin, open the configuration file with your preferred editor:
Look for this line in the file:
Uncomment, and change the value to ‘*’ , this will allow Postgres connections from anyone.
Save and exit the file. Next, modify pg_hba.conf to also allow connections from everyone. Open the file with your preferred editor:
Modify this section:
This file stores the client authentication, each record specifies an IP address range, database name, username, and authentication method. In our case, we are granting all database users access to all databases with any IP address range, thus, letting any IP address connect. Save and exit the file. Next, allow port 5432 through the firewall by executing:
Finally, restart Postgres to apply all the changes you have made to its configuration by running:
Connect to Postgres remotely
In this step, you will be connecting to your server from an external machine. Connect to the remote Postgres database by running:
Create a new table and name it pharaohs by executing the following in the Postgres shell:
Next, add a record to the pharaohs table, you will be adding Tutankhamun as a string to the table by running the following in the Postgres shell:
Next, we will be accessing our database using a GUI (Graphical User Interface) tool like tablePlus which enables you to visualize data away from the command line interface to see if we can find the records we created. Open TablePlus and click on Create a new connection .
Select Postgres from the dropdown
Postgres default port is 5432
Ignore the SSL regions (we won’t cover this topic in this post)
If your credentials are correct, you will be shown a GUI panel to view your database records in which you will find the table created and the record we added to it.
As shown in the image, we can see the pharaohs table we created earlier and the record we added to it. Our remote database is ready!
We have seen how to configure a Postgres database server for remote access. With this knowledge, you can set up a database server for your next project. In production, there are some security measures you will have to keep in mind. For example, only allowing the specified IP address and not allowing root access to your server, you can learn how to set this up in this article.
Connecting to PostgreSQL databases
One of the first things you’ll need to think about when working with a PostgreSQL database is how to connect and interact with the database instance. This requires coordination between the database client — the component you use to interact with the database, and the database server — the actual PostgreSQL instance that stores, organizes, and provides access to your data.
Because of this, you need to understand how to connect as a client by providing the required information to authenticate. In this guide, we’ll cover how to connect to a PostgreSQL database using the native psql command line client — one of the most common and useful ways of interacting with a database instance.
In a companion guide, you can find out how to configure PostgreSQL’s authentication to meet your project’s needs. Consider reading both guides for a more complete picture of how authentication works in PostgreSQL.
If your database client or library requests a connection URI, you may want to look at our guide on understanding PostgreSQL connection URIs instead.
Basic information about the psql client
The psql client, the native command line client for PostgreSQL, can connect to database instances to offer an interactive session or to send commands to the server. It is especially useful when implementing your initial settings and getting the basic configuration in place, prior to interacting with the database through application libraries. In addition, psql is great for interactive exploration or ad-hoc queries while developing the access patterns your programs will use.
The way that you connect depends on the configuration of the PostgreSQL server and the options available for you to authenticate to an account. In the following sections, we’ll go over some of the basic connection options. For clarity’s sake, we’ll differentiate between local and remote connections:
- local connection: a connection where the client and the PostgreSQL instance are located on the same server
- remote connection: where the client is connecting to a network-accessible PostgreSQL instance running on a different computer
Let’s start with connecting to a database from the same computer.
Connecting to a local database with psql
Without any arguments, the psql command attempts to connect to a Unix socket file to access a local database. It uses your operating system username as the PostgreSQL username and database name that you are trying to connect to.
By default, modern versions of PostgreSQL are configured for something called peer authentication. Peer authentication authenticates users automatically if a valid PostgreSQL user exists that matches the user’s operating system username.
So if your current user is a valid PostgreSQL user on your local database, you can connect by typing:
However, it’s unlikely that your normal operating system username already has an associated PostgreSQL username. So usually, you’ll need to log into PostgreSQL using an operating system username that already has an associated PostgreSQL role.
By default, the super user, or administrative account, for PostgreSQL is called postgres . Upon installation, a user called postgres is also created on the operating system. So, to log into PostgreSQL as the postgres user, you need to connect as the postgres operating system user. There are a number of ways to do this.
The easiest way to get a shell as the postgres user on most systems is to use the sudo command. To open a shell session for the postgres user and then log into the database, you can type:
If you don’t need to perform any additional shell commands as the postgres user, you can also just run the psql command directly as the postgres user. This will log you in to a PostgreSQL session immediately instead of taking you to a shell first:
Either of these methods should allow you to log into the postgres PostgreSQL user account.
Connecting to a remote database
For security reasons and because of the reliance on a local socket file, peer authentication cannot be used for remote connections. Instead, users will need to log in using another method.
The available authentication methods vary based on the PostgreSQL instance’s configuration. Most commonly, though, you will be able to authenticate by providing the following pieces of information:
|hostname||The network host name or the IP address of the PostgreSQL server. The -h option is used to specify the hostname.|
|network port||The network port that the PostgreSQL server is running on. By default, this is port 5432. This can be omitted if the default is used. To specify a different port, you can use the -p option.|
|PostgreSQL username||The database username you wish to connect as. If not specified, your operating system username will be used. The -U option is used to override the default and define the username to connect with.|
|PostgreSQL password||The PostgreSQL password associated with the specified username. Since psql will prompt you for a password if it isn’t provided, this can often be omitted.|
|PostgreSQL database||The PostgreSQL database name that you want to access. If not specified, your operating system username will be used as the database name. To specify a different database, use the -d option.|
There are multiple ways of providing your connection information to psql . Here, we’ll cover the two of the most common: by passing options and with a connection string.
Passing connection information to psql with options
So the basic format for connecting to a remote database typically looks something like this:
The remote server will indicate that it requires a password for most accounts, at which point psql will prompt you for the password. If you authenticate successfully, a new interactive PostgreSQL session will be started.
As an example, we can imagine wanting to connect to a database with the following requirements:
- hostname: myhost
- port: 1234
- database: applicationdb
- username: myapplicationuser
- password: mypass
Calling psql with the following options would allow you to authenticate:
Upon pressing enter, you’d be prompted a password where you can authenticate with mypass .
Passing connection information to psql with a connection string
This same information can also be encoded into a PostgreSQL connection string. A connection string provides the same information in a single URI string that uses certain characters as delimiters between the different fields.
Connection strings have the following general format:
Each of the fields can be omitted if they are unneeded or if the default values are valid.
We can optionally use a connection string to connect with psql instead of the using the options that we used in our previous example:
The psql tool can use either of these formats, so use whichever you prefer. Other tools or libraries that you encounter might nudge you into relying on one more than the other.
Adjusting a PostgreSQL server’s authentication configuration
If you want to modify the rules that dictate how users can authenticate to your PostgreSQL instances, you can do so by modifying your server’s configuration. You can find out how to modify PostgreSQL’s authentication configuration in this article.
In this guide, we covered PostgreSQL authentication from the client side. We demonstrated how to use the psql command line client to connect to both local and remote database instances using a variety of methods.
Knowing how to connect to various PostgreSQL instances is vital as you start to work the database system. You may run a local PostgreSQL instance for development that doesn’t need any special authentication, but your databases in staging and production will almost certainly require authentication. Being able to authenticate in either case will allow you to work well in different environments.