Connection to a PostgreSQL database can be made either remotely or locally. If you are following up on the previous exercise of ‘Install PostgreSQL’ and installed PostgreSQL on Ubuntu WSL or Ubuntu machine, then you are ready to connect to the database.
Sudo to ‘postgres’ user from Linux terminal and enter the password chosen earlier.
$ sudo su - postgres
If you didn’t start the PostgreSQL cluster earlier, then start now. Start might fail in certain situations. In such a case, please refer to the solution below.
$ pg_ctlcluster 13 main start
If start fails with the below error, create the directory, change the directory ownership to ‘postgres’. Then, try starting the cluster again.
install: cannot change owner and permissions of ‘/var/run/postgresql’: No such file or directory Error: Could not create /var/run/postgresql/13-main.pg_stat_tmp: No such file or directory
A PostgreSQL Cluster is a postmaster (main background process) and a group of auxiliary processes, all managing a shared data directory that contains one or more databases. PostgreSQL Cluster has multiple databases and all those databases are stopped and started together when the Cluster is stopped or started.
In stricter terms, a PostgreSQL Database is one of the databases of the Cluster. But, PostgreSQL Cluster and PostgreSQL Database terms can be used interchangeably sometimes.
Note that pg_ctlcluster is an Ubuntu-specific wrapper command. There are few other commands too. For example, pg_lsclusters – lists the PostgreSQL clusters running on the server which have been created using another wrapper command pg_createcluster. Similarly, the command pg_dropcluster is used to drop a PostgreSQL cluster. These commands will be covered in detail later.
List the cluster which you started earlier.
pg_lsclusters
Connect to the cluster running on default port of 5432 using psql command.
psql
To connect to the cluster running on non-default port, use psql command with -p option.
psql -p 5433
Check the details of your database connection using commands \c and \conninfo. When you connect to the cluster using ‘postgres‘ OS user without giving any database name, it connects to ‘postgres‘ database by default.
psql\c
\conninfo
Here, you are able to connect to the cluster just using ‘psql’ command because the cluster is running on the localhost on default port ‘5432’ and you are on the logged into the terminal using operating system user ‘postgres’ which has a default corresponding user ‘postgres in the default database ‘postgres’ inside the PostgreSQL cluster.
To view the connection options with ‘psql‘ command, run ‘psql –help‘ and check the last section in the output which will look like below:
psql --help
In the real world, the applications would be connecting using a given user and password to one of the databases of the PostgreSQL cluster running on some remote server. In such a case, a typical command to connect to the database would be as shown below.
-p 5433 -> 5433 is the port on which PostgreSQL is listening for the client requests
-d appdb -> appdb is the database to which client needs to connect
-U hr_user -> hr_user is the userid inside the database to be used for the connection
-h 127.0.0.1 -> 127.0.0.1 is the host or server where PostgreSQL cluster is running
-W -> prompt for the password
We need to make some changes on the PostgreSQL cluster server to enforce the ask of passwords and to enable connections from remote servers. The steps are described below.
Enabling Password Authenticated Connections from Local Clients
By default, all operating system users can connect to PostgreSQL running on the same server without needing to input the password even if the user was created with a password. It is so because in file pg_hba.conf (host-based authentication configuration file), the initial setup allows that kind of behavior. To ask for password for all users except ‘postgres’, edit the file pg_hba.conf as described below:
Locate the file pg_hba.conf using show command on ‘psql’ prompt. Alternatively, this file will either be in cluster data directory or /etc/postgresql/<version>/<cluster-name>
For our case, the file is /etc/postgresql/13/mars/pg_hba.conf. In this file, comment the existing ‘local…..all…..all….trust’ line and add 2 additional lines, one to allow ‘postgres’ user to login without password, another to enforce password for all other users.
Content before the change:
Content after the change:
Reload the cluster configuration to activate the changes.
psqlSELECT pg_reload_conf();
OR
pg_ctl reload
Enabling Password Authenticated Connections from Remote Clients
By default, the remote connections are allowed only from the local server. To enable the remote connections from remote servers, we need:
to add an entry of ‘0.0.0.0/0’ for IPv4 and ::/0 for IPv6 host type of connections and reload the configuration (pg_reload_conf())
to set ‘listen_addresses‘ to ‘*‘ and restart the PostgreSQL cluster.
Content before the change:
Content after the change:
Reload the cluster configuration to activate the changes.
psqlSELECT pg_reload_conf();
OR
pg_ctl reload
Reloading the cluster configuration is not enough as we have changed the ‘listen_addresses‘ parameter too. So, restart the cluster.
pg_ctlcluster 13 main stop
pg_ctlcluster 13 main start
admin
Share post:
Connection to a PostgreSQL database can be made either remotely or locally. If you are following up on the previous exercise of ‘Install PostgreSQL’ and installed PostgreSQL on Ubuntu WSL or Ubuntu machine, then you are ready to connect to the database.
If start fails with the below error, create the directory, change the directory ownership to ‘postgres’. Then, try starting the cluster again.
install: cannot change owner and permissions of ‘/var/run/postgresql’: No such file or directory
Error: Could not create /var/run/postgresql/13-main.pg_stat_tmp: No such file or directory
Note that pg_ctlcluster is an Ubuntu-specific wrapper command. There are few other commands too. For example, pg_lsclusters – lists the PostgreSQL clusters running on the server which have been created using another wrapper command pg_createcluster. Similarly, the command pg_dropcluster is used to drop a PostgreSQL cluster. These commands will be covered in detail later.
Here, you are able to connect to the cluster just using ‘psql’ command because the cluster is running on the localhost on default port ‘5432’ and you are on the logged into the terminal using operating system user ‘postgres’ which has a default corresponding user ‘postgres in the default database ‘postgres’ inside the PostgreSQL cluster.
To view the connection options with ‘psql‘ command, run ‘psql –help‘ and check the last section in the output which will look like below:
In the real world, the applications would be connecting using a given user and password to one of the databases of the PostgreSQL cluster running on some remote server. In such a case, a typical command to connect to the database would be as shown below.
We need to make some changes on the PostgreSQL cluster server to enforce the ask of passwords and to enable connections from remote servers. The steps are described below.
Enabling Password Authenticated Connections from Local Clients
By default, all operating system users can connect to PostgreSQL running on the same server without needing to input the password even if the user was created with a password. It is so because in file pg_hba.conf (host-based authentication configuration file), the initial setup allows that kind of behavior. To ask for password for all users except ‘postgres’, edit the file pg_hba.conf as described below:
Content before the change:
Content after the change:
Reload the cluster configuration to activate the changes.
Enabling Password Authenticated Connections from Remote Clients
By default, the remote connections are allowed only from the local server. To enable the remote connections from remote servers, we need:
pg_reload_conf())listen_addresses‘ to ‘*‘ and restart the PostgreSQL cluster.Content before the change:
Content after the change:
Reload the cluster configuration to activate the changes.
Reloading the cluster configuration is not enough as we have changed the ‘
listen_addresses‘ parameter too. So, restart the cluster.