DEV Community

Cover image for How to expose PostgreSQL connection in ubuntu
AMANI Eric
AMANI Eric

Posted on

5

How to expose PostgreSQL connection in ubuntu

If you have a PostgreSQL database instance running on an Ubuntu server, you might want 
to access it from outside the server.
In this article, I'm going to show you how you can do it.

The default Postgres connection port is usually 5432. We are going to expose our connection through this port.
You can use your other port if not using the default one.

Allow Postgres port in the firewall (5432 in our case)



sudo ufw allow 5432/tcp


Enter fullscreen mode Exit fullscreen mode

Image description

Open your Postgres config file



sudo nano /etc/postgresql/<postgres_version>/main/pg_hba.conf


Enter fullscreen mode Exit fullscreen mode

Notice that I used ../14/.. . That's because I have PostgreSQL version 14 installed. Change it to your installed version

Image description

It will open your PostgreSQL config file.

Image description

By default, the allowed address to access the Postgres connection is 127.0.0.1/32.
That means you can only be connected if you are on the same server.

We have to change that since we want the connection to be public.

Change the IP address from 127.0.0.1:32 => 0.0.0.0/0

Image description

What we did is allow any IP address at any port to access ur connection.

Now you can connect to the database from anywhere.

N.B: Changing from 127.0.0.1/32 in your configs to 0.0.0.0/0 means you are allowing any device from any.
This is insecure. If you want to be more secure, you can change it from 127.0.0.1/32 to your_trusted_ip/your_trusted_ip_port

Construct a connection string like this and connect it to your database.

👉🏽 postgresql://[user[:password]@][our_server_ip][:our_postgres_server_ip][/dbname]

You can also test the connection with PGAdmin which is a powerful Postgres client.

You should be able to access your database 🎉

Sentry image

Hands-on debugging session: instrument, monitor, and fix

Join Lazar for a hands-on session where you’ll build it, break it, debug it, and fix it. You’ll set up Sentry, track errors, use Session Replay and Tracing, and leverage some good ol’ AI to find and fix issues fast.

RSVP here →

Top comments (0)

Cloudinary image

Optimize, customize, deliver, manage and analyze your images.

Remove background in all your web images at the same time, use outpainting to expand images with matching content, remove objects via open-set object detection and fill, recolor, crop, resize... Discover these and hundreds more ways to manage your web images and videos on a scale.

Learn more

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay