How to Install and Configure PostgreSQL on Ubuntu for Remote Access

In this guide, we will walk you through the steps to install PostgreSQL on an Ubuntu server, create a new database and user, change the default postgres user password, and configure the server to allow remote access. This setup is essential for those looking to manage their databases remotely and securely.

Step 1: Update Your System

Before installing anything, it's a good idea to update your system's package list. This ensures that you get the latest versions of the software from the Ubuntu repositories.

sudo apt-get update

Step 2: Install PostgreSQL

PostgreSQL is available in Ubuntu’s default repositories, so you can install it using the apt-get package manager.

sudo apt-get install postgresql postgresql-contrib

Description:

  • postgresql is the core database system.
  • postgresql-contrib includes additional utilities and features that are useful for managing PostgreSQL.

Step 3: Start and Enable PostgreSQL Service

After installation, the PostgreSQL service should start automatically. However, you can ensure it's running and set it to start on boot.

sudo systemctl start postgresql
sudo systemctl enable postgresql

Description:

  • start: Starts the PostgreSQL service immediately.
  • enable: Ensures that PostgreSQL starts on boot.

Step 4: Access the PostgreSQL Command Line

PostgreSQL installs with a default user called postgres. You need to switch to this user to perform administrative tasks like creating a new database or user.

sudo -i -u postgres

This switches you to the postgres user. Now, access the PostgreSQL command line interface (CLI).

psql

Step 5: Change the Default postgres User Password

It's a good security practice to set a password for the default postgres user.

ALTER USER postgres WITH PASSWORD 'new_password';

Description: This command changes the password for the postgres user to new_password. Replace 'new_password' with a strong password of your choice.

Step 6: Create a New Database and User

Now, let’s create a new database and user. This is useful if you want a separate user to manage a specific database.

CREATE DATABASE mydatabase;
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser;

Description:

  • CREATE DATABASE mydatabase; creates a new database named mydatabase.
  • CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword'; creates a new user named myuser with the password mypassword.
  • GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myuser; gives myuser full control over mydatabase.

Step 7: Grant Superuser Privileges to the New User (Optional)

If you want the new user to have the same level of control as the postgres user, you can make them a superuser.

ALTER USER myuser WITH SUPERUSER;

Description: This command gives myuser the same privileges as the default postgres user, allowing them to perform all administrative tasks.

Step 8: Allow Remote Connections to PostgreSQL

By default, PostgreSQL only allows connections from the local machine. To access it remotely, you need to modify some configuration files.

Edit the PostgreSQL Configuration File:

sudo nano /etc/postgresql/14/main/postgresql.conf

Find the line that says listen_addresses and change it to:

listen_addresses = '0.0.0.0'

Description: This change allows PostgreSQL to accept connections from any IP address.

Configure Client Authentication:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Add the following line at the end of the file:

host    all             all             0.0.0.0/0               md5

Description: This line allows all users to connect to all databases using a password from any IP address.

Restart PostgreSQL to Apply Changes:

sudo systemctl restart postgresql

Description: Restarting the service applies the changes you made to the configuration files.

Step 9: Configure Firewall to Allow Remote Access

If your Ubuntu server has a firewall enabled, you need to allow connections on PostgreSQL’s default port, which is 5432.

sudo ufw allow 5432/tcp
sudo ufw enable

Description:

  • allow 5432/tcp: Opens port 5432 for TCP connections, which PostgreSQL uses.
  • enable: Ensures that the firewall is active.

Step 10: Verify Remote Access

To make sure everything is set up correctly, try connecting to your PostgreSQL database from a remote machine.

psql -h your_server_ip -U myuser -d mydatabase

Description:

  • -h your_server_ip: Specifies the IP address of your PostgreSQL server.
  • -U myuser: Specifies the username to connect with.
  • -d mydatabase: Specifies the database to connect to.

Conclusion

By following these steps, you have successfully installed PostgreSQL on your Ubuntu server, created a new database and user, changed the default user password, and configured the server to allow remote access. This setup is now ready for use in development or production environments.

Comments

Leave a Reply