The Ultimate DevOps Guide to PostgreSQL: Setup, Security, and User Management on Linux

Introduction

Setting up a database rarely feels like the fun part of a project. You just want to write your application code, deploy it, and watch the users roll in. But a hastily configured database is a major security risk waiting to happen.

Many engineers make the mistake of running their applications using the default master superuser, leaving their entire infrastructure vulnerable to attacks. If you want to build production-ready systems, you cannot rely on default settings or basic tutorials that skip permissions.

Whether you are deploying your first full-stack application or managing complex infrastructure, learning how to set up and secure PostgreSQL on Linux is an essential skill that separates beginners from professionals.

In this comprehensive guide, we will walk through the complete process from scratch. You will learn how to properly install the database, isolate roles with strict user management, configure remote connections safely, and master the essential troubleshooting queries you need when things go wrong. Let's dive in.

Step 1: Installing PostgreSQL on Linux

For this guide, we are assuming you are using an Ubuntu or Debian-based system, which is the industry standard for most modern web servers.

First, we always want to make sure our server’s package manager is up to date before installing new software. Run this in your terminal:

sudo apt update
sudo apt upgrade -y

Pro Tip: If you receive a "429 Too Many Requests" error while running the update command, it means the Ubuntu package servers are overloaded. Check out our quick guide on how to fix the Ubuntu APT update error to resolve it instantly.

Now, let's install PostgreSQL along with a package called postgresql-contrib.

sudo apt install postgresql postgresql-contrib -y

Wait, what is postgresql-contrib?
Great question. The core Postgres installation is very lightweight. The contrib package adds a bunch of highly recommended, official extensions (like advanced cryptography tools, uuid generators, and performance monitoring extensions). You might not need them today, but you will almost certainly need them tomorrow. Always install it.

Once the installation finishes, the PostgreSQL service should start automatically. You can verify it is running with:

sudo systemctl status postgresql

If it says "active (running)", you are good to go!

Step 2: The "Postgres" User Concept (Don't Skip This!)

Here is the number one thing that confuses newcomers to PostgreSQL: By default, Postgres uses a concept called "peer authentication."

When you install Postgres, Linux automatically creates a system user account named postgres. By default, the only way to log into the master database account is to switch to this Linux user.

Let’s do that now:

sudo -i -u postgres

Notice how your terminal prompt changed? You are now acting as the postgres system user. From here, you can access the interactive PostgreSQL terminal, known as psql:

psql

Boom. You are now inside the database. Your prompt should look like this: postgres=#.

Before we do anything else, let’s give this master postgres database user a password. By default, it doesn't have one, which makes remote management impossible. Type this into the psql prompt:

\password postgres

Enter a strong password when prompted.

Step 3: Creating a Database and a Specific User

In a real-world DevOps environment, you never use the master postgres user to run your application. If a hacker finds a vulnerability in your app, you don't want them having master keys to your entire database server.

Instead, the best practice is: One App = One Database = One User.

Let's say we are building a new analytics application. We want an isolated database, and a user that can only access that database.

Still inside the psql prompt, let's create the database:

CREATE DATABASE analytics_db;

Next, let's create the user (in Postgres, users are formally called "roles"):

CREATE USER analytics_worker WITH ENCRYPTED PASSWORD 'SuperSecretPassword123!';

Note: Always use WITH ENCRYPTED PASSWORD. It ensures the password is saved securely in the system catalogs. For production environments, we highly recommend generating a complex, secure string using a tool like the LastPass Password Generator rather than using a memorable phrase.

Step 4: Locking Down Permissions

We have our database (analytics_db) and our user (analytics_worker). Now we need to introduce them to each other.

First, we grant the user the ability to connect to the database and do whatever they want inside it:

GRANT ALL PRIVILEGES ON DATABASE analytics_db TO analytics_worker;

The Missing Step Everyone Forgets
If you stop right here, your application will connect to the database, but it will throw errors the moment it tries to create a table. Why? Because in Postgres 15 and newer, creating a database doesn't automatically give the user permission to write to the public schema inside that database.

To fix this, you need to connect directly to the new database:

\c analytics_db

And then grant usage on the schema:

GRANT ALL ON SCHEMA public TO analytics_worker;

Congratulations! You now have a perfectly isolated database environment. You can type \q to exit the psql prompt, and then type exit to leave the postgres Linux user.

Step 5: Configuring Remote Access

Right now, your database only listens to connections coming from localhost. If your app is hosted on the same server, you are completely done.

But what if your app is on Server A, and your database is on Server B? Or what if you want to use a visual tool like pgAdmin or DBeaver from your laptop? You need to configure Postgres to listen to the outside world.

You need to edit two configuration files.

1. Edit postgresql.conf

First, find out where your config files are stored. Usually, they are in /etc/postgresql/14/main/ (replace 14 with your version number).

Open the main config file:

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

Find the line that says #listen_addresses = 'localhost' and change it to:

listen_addresses = '*'

(Don't forget to remove the # symbol to uncomment the line!) This tells Postgres to listen for traffic on all network interfaces. Save and exit.

2. Edit pg_hba.conf

Next, we need to tell Postgres who is allowed to connect. Open the Client Authentication file:

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

Scroll to the very bottom and add this line:

host    all             all             0.0.0.0/0               md5

Security Warning: 0.0.0.0/0 means "allow connections from ANY IP address in the world." For a production environment, you should replace 0.0.0.0/0 with the specific IP address of your application server.

Restart Postgres to apply the changes:

sudo systemctl restart postgresql

Step 6: The DevOps Arsenal (Essential Queries)

As a DevOps engineer, setting up the database is only 10% of the job. The other 90% is figuring out why the database is suddenly acting up.

Here are three essential queries you should keep in your back pocket. Run these inside the psql prompt when things go wrong.

1. "Why is the database so slow?" (Checking Active Connections)

If your app is timing out, it might be because the database has run out of connection limits, or there are queries hanging in the background. Run this to see exactly what is running right now:

SELECT pid, user, datname, state, query 
FROM pg_stat_activity 
WHERE state = 'active';

This shows you the Process ID (pid), who is running it, and the exact SQL query currently executing.

2. "How do I kill a stuck query?"

If the previous command showed a massive, poorly optimized JOIN that has been running for 20 minutes, you need to kill it before it crashes the server. Grab the pid from the query above, and run:

SELECT pg_terminate_backend(pid_goes_here);

(Example: SELECT pg_terminate_backend(12345);)

3. "Where did all my disk space go?"

Databases grow, and sometimes a single table balloons out of control. This query tells you the size of your tables, starting with the largest:

SELECT relname AS "Table",
pg_size_pretty(pg_total_relation_size(relid)) AS "Size",
pg_size_pretty(pg_relation_size(relid)) AS "Data Size",
pg_size_pretty(pg_indexes_size(relid)) AS "Index Size"
FROM pg_catalog.pg_statio_user_tables 
ORDER BY pg_total_relation_size(relid) DESC;

Conclusion

PostgreSQL is a beast, but it is a beautiful, highly logical beast.

By following the steps above, you haven't just "installed a database." You have set up a secure, production-ready environment using DevOps best practices. You’ve isolated your application data, locked down permissions, and armed yourself with the diagnostic queries needed to troubleshoot future bottlenecks.

Remember, infrastructure is a living thing. Keep an eye on your logs, back up your data regularly (look into pg_dump), and never stop exploring the incredible features PostgreSQL has to offer.

Happy querying!