II. Important: Use a Currently Supported Ubuntu Version<\/h3>\n\n\n\n
If you’re setting this up on Ubuntu 16.04 specifically because an old tutorial recommended it, it’s worth stopping to reconsider. Ubuntu 16.04 no longer receives security updates from Canonical as of April 2024, which means:<\/p>\n\n\n\n
- \n
- The underlying OS carries unpatched vulnerabilities that will only accumulate over time<\/li>\n\n\n\n
- PostgreSQL itself may not offer packages compatible with such an old Ubuntu release going forward<\/li>\n\n\n\n
- Running any production database server on an unsupported OS is a real, compounding security risk, regardless of how well PostgreSQL itself is configured<\/li>\n<\/ul>\n\n\n\n
The commands in this guide work identically on Ubuntu 22.04, 24.04, or any current LTS release \u2014 there’s no reason to specifically target 16.04 today. If you’re maintaining an existing legacy 16.04 system, treat this as a strong signal to plan a migration to a supported release.<\/p>\n\n\n\n
III. Architecture Overview<\/h3>\n\n\n\n
Before installing anything, it helps to understand how PostgreSQL is structured once running:<\/p>\n\n\n\n
- \n
- The PostgreSQL server process<\/strong> (
postgres<\/code>) runs continuously in the background, listening for connections<\/li>\n\n\n\n- Client applications<\/strong> \u2014 whether that’s the
psql<\/code> command-line tool, a web application, or a GUI tool like pgAdmin \u2014 connect to this server process over a Unix socket (for local connections) or TCP\/IP (for network connections)<\/li>\n\n\n\n- Each client connection authenticates against PostgreSQL’s role-based permission system, which determines what that specific connection is allowed to see or modify<\/li>\n\n\n\n
- The server manages all actual reads and writes to the underlying data files on disk \u2014 clients never touch the data files directly<\/li>\n<\/ul>\n\n\n\n
![\"\"](\"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2017\/07\/postgresql_architecture_v2-1024x512.png\")
<\/a><\/figure>\n\n\n\nThis matters practically because PostgreSQL’s default authentication setup \u2014 covered in the next few steps \u2014 relies on this role system tightly, including a close relationship between Linux system users and PostgreSQL database roles that trips up a lot of newcomers.<\/p>\n\n\n\n
IV. Update System Packages<\/h3>\n\n\n\n
Before installing anything new, update your package index:<\/p>\n\n\n\n
sudo apt update\nsudo apt upgrade -y\n<\/pre>\n\n\n\n
V. Install PostgreSQL<\/h3>\n\n\n\n
Install PostgreSQL along with the
postgresql-contrib<\/code> package, which adds useful additional extensions and utilities not included in the base package:<\/p>\n\n\n\nsudo apt install postgresql postgresql-contrib -y\n<\/pre>\n\n\n\n
This installs the PostgreSQL server, the
psql<\/code> command-line client, and creates a dedicated Linux system user namedpostgres<\/code>, which PostgreSQL uses internally for its own administrative operations.<\/p>\n\n\n\nVI. Verify the PostgreSQL Service Is Running<\/h3>\n\n\n\n
Check the service status:<\/p>\n\n\n\n
sudo systemctl status postgresql\n<\/pre>\n\n\n\n
You should see
active (running)<\/code>. If it’s not running, start it manually:<\/p>\n\n\n\nsudo systemctl start postgresql\n<\/pre>\n\n\n\n
VII. Enable PostgreSQL to Start on Boot<\/h3>\n\n\n\n
Ensure PostgreSQL automatically starts whenever the server reboots:<\/p>\n\n\n\n
sudo systemctl enable postgresql\n<\/pre>\n\n\n\n
VIII. Access the PostgreSQL Prompt<\/h3>\n\n\n\n
This is where a small but common mistake shows up in a lot of quick-reference guides. PostgreSQL’s default administrative Linux user is named
postgres<\/code><\/strong> \u2014 notpostgresql<\/code>. Using the wrong username here will fail with a “role does not exist” or “user does not exist” error, since Linux and PostgreSQL both expect the exact system username that was created during installation.<\/p>\n\n\n\nThe correct command is:<\/p>\n\n\n\n
sudo -u postgres psql\n<\/pre>\n\n\n\n
This switches to the
postgres<\/code> system user and opens thepsql<\/code> interactive prompt as PostgreSQL’s default superuser role, also namedpostgres<\/code>.<\/p>\n\n\n\nTo exit the prompt at any time:<\/p>\n\n\n\n
\\q\n<\/pre>\n\n\n\n
IX. Create a New Database and User<\/h3>\n\n\n\n
Rather than using the default
postgres<\/code> superuser role for applications, create a dedicated database and a scoped user \u2014 the same least-privilege principle that applies to any database system.<\/p>\n\n\n\nFrom inside the
psql<\/code> prompt:<\/p>\n\n\n\nCREATE DATABASE example_db;\nCREATE USER app_user WITH ENCRYPTED PASSWORD 'Str0ng-Unique-Passw0rd!';\nGRANT ALL PRIVILEGES ON DATABASE example_db TO app_user;\n<\/pre>\n\n\n\n
\n
Security note:<\/strong> Replace both the username and password with real, strong, unique values. Avoid short or predictable passwords \u2014 PostgreSQL, like any database exposed to an application, is a common target if credentials are weak or reused elsewhere.<\/p>\n<\/blockquote>\n\n\n\n
X. Verify the New User Works<\/h3>\n\n\n\n
Exit the current
psql<\/code> session:<\/p>\n\n\n\n\\q\n<\/pre>\n\n\n\n
Then log in directly as the new user, connecting to the specific database:<\/p>\n\n\n\n
psql -U app_user -d example_db -h localhost\n<\/pre>\n\n\n\n
\n
Note:<\/strong> The
-h localhost<\/code> flag matters here. By default, PostgreSQL’s local authentication method (peer<\/code> authentication) expects the connecting Linux system username to exactly match the PostgreSQL role name \u2014 which won’t be true for an application-specific role likeapp_user<\/code> unless a matching Linux user also exists. Specifying-h localhost<\/code> forces a TCP connection instead, which uses password authentication (assuming it’s configured, covered in the next step) rather than relying on the Linux username matching.<\/p>\n<\/blockquote>\n\n\n\nXI. Configure Password Authentication (If Needed)<\/h3>\n\n\n\n
If the connection in Step X is rejected, PostgreSQL’s authentication configuration may need adjusting. Open the
pg_hba.conf<\/code> file, whose exact path varies by PostgreSQL version but is commonly:<\/p>\n\n\n\nsudo vi \/etc\/postgresql\/*\/main\/pg_hba.conf\n<\/pre>\n\n\n\n
Look for a line matching local TCP connections, typically something like:<\/p>\n\n\n\n
host all all 127.0.0.1\/32 scram-sha-256\n<\/pre>\n\n\n\n
If this line uses
peer<\/code> orident<\/code> instead ofscram-sha-256<\/code> (ormd5<\/code> on older PostgreSQL versions), change it toscram-sha-256<\/code> for local TCP connections to use standard password authentication. After editing, restart PostgreSQL:<\/p>\n\n\n\nsudo systemctl restart postgresql\n<\/pre>\n\n\n\n
\n
Note:<\/strong>
scram-sha-256<\/code> is the modern, more secure password authentication method in current PostgreSQL versions, and is preferable to the oldermd5<\/code> method where available.<\/p>\n<\/blockquote>\n\n\n\nXII. Common PostgreSQL Administration Commands<\/h3>\n\n\n\n
A few commands worth keeping on hand once your database and user are set up, run from inside
psql<\/code>:<\/p>\n\n\n\nList all databases:<\/strong><\/p>\n\n\n\n
\\l\n<\/pre>\n\n\n\n
List all roles (users):<\/strong><\/p>\n\n\n\n
\\du\n<\/pre>\n\n\n\n
Connect to a specific database:<\/strong><\/p>\n\n\n\n
\\c example_db\n<\/pre>\n\n\n\n
List tables in the current database:<\/strong><\/p>\n\n\n\n
\\dt\n<\/pre>\n\n\n\n
Change a user’s password:<\/strong><\/p>\n\n\n\n
ALTER USER app_user WITH ENCRYPTED PASSWORD 'NewStrongerPassword!';\n<\/pre>\n\n\n\n
Revoke privileges from a user:<\/strong><\/p>\n\n\n\n
REVOKE ALL PRIVILEGES ON DATABASE example_db FROM app_user;\n<\/pre>\n\n\n\n
XIII. Troubleshooting Common Issues<\/h3>\n\n\n\n
“role does not exist” when running
sudo -u postgres psql<\/code>:<\/strong> Double-check the username is exactlypostgres<\/code>, notpostgresql<\/code> \u2014 this is the single most common typo, as covered in Step VIII.<\/p>\n\n\n\n“Peer authentication failed” when connecting as an application user:<\/strong> This means you’re connecting via a Unix socket rather than TCP, and the Linux system username doesn’t match the PostgreSQL role. Add
-h localhost<\/code> to force a TCP connection, as shown in Step X, or adjustpg_hba.conf<\/code> as covered in Step XI.<\/p>\n\n\n\nPostgreSQL service fails to start after installation:<\/strong> Check the service logs for specifics:<\/p>\n\n\n\n
sudo journalctl -u postgresql\n<\/pre>\n\n\n\n
This usually points directly to a configuration file issue or a port conflict with another running service.<\/p>\n\n\n\n
Changes to pg_hba.conf don’t seem to take effect:<\/strong> Confirm you restarted the PostgreSQL service after editing the file \u2014 a reload isn’t always sufficient for authentication method changes, depending on the specific setting changed.<\/p>\n\n\n\n
XIV. Allowing Remote Connections (If Actually Needed)<\/h3>\n\n\n\n
By default, PostgreSQL only listens for local connections. If you have a genuine need for a remote application server to connect to this database \u2014 for example, a separate application server in the same private network \u2014 two files need adjustment.<\/p>\n\n\n\n
1. Allow PostgreSQL to listen on network interfaces<\/strong>, not just localhost. Edit
postgresql.conf<\/code>:<\/p>\n\n\n\nsudo vi \/etc\/postgresql\/*\/main\/postgresql.conf\n<\/pre>\n\n\n\n
Find the
listen_addresses<\/code> line and set it to either a specific IP or all interfaces:<\/p>\n\n\n\nlisten_addresses = '*'\n<\/pre>\n\n\n\n
\n
Using
*<\/code> listens on all network interfaces. For tighter control, specify the exact IP address of the interface you want PostgreSQL reachable on instead.<\/p>\n<\/blockquote>\n\n\n\n2. Add a rule permitting the specific remote host<\/strong> in
pg_hba.conf<\/code>:<\/p>\n\n\n\nhost example_db app_user 192.168.1.50\/32 scram-sha-256\n<\/pre>\n\n\n\n
This line permits connections to
example_db<\/code> from the specific roleapp_user<\/code>, only from the IP address192.168.1.50<\/code>, using password authentication.<\/p>\n\n\n\n3. Restart PostgreSQL<\/strong> to apply both changes:<\/p>\n\n\n\n
sudo systemctl restart postgresql\n<\/pre>\n\n\n\n
4. Update your firewall<\/strong> to allow the connection. If using
ufw<\/code>:<\/p>\n\n\n\nsudo ufw allow from 192.168.1.50 to any port 5432\n<\/pre>\n\n\n\n
\n
Security note:<\/strong> Scope both the
pg_hba.conf<\/code> rule and firewall rule to the narrowest set of hosts and databases actually required. A broad rule like0.0.0.0\/0<\/code> inpg_hba.conf<\/code> combined with an open firewall port exposes your database to the entire internet \u2014 precisely the exposure the security best practices section above recommends avoiding.<\/p>\n<\/blockquote>\n\n\n\nXV. Security Best Practices<\/h3>\n\n\n\n
- \n
- Avoid using the
postgres<\/code> superuser role for applications.<\/strong> Always create scoped, dedicated roles per application or use case, following the least-privilege principle covered in Step IX.<\/li>\n\n\n\n- Restrict network exposure.<\/strong> Unless remote connections are genuinely required, keep PostgreSQL bound to
localhost<\/code> and use SSH tunneling or a VPN for any remote administrative access instead of exposing port 5432 directly to the internet.<\/li>\n\n\n\n- Keep PostgreSQL updated.<\/strong> Like any database software, PostgreSQL receives periodic security patches \u2014 staying current is one of the most effective, low-effort security practices available.<\/li>\n\n\n\n
- Use strong, unique passwords for every role,<\/strong> and rotate them periodically, especially for roles with broad privileges.<\/li>\n\n\n\n
- Back up regularly using
pg_dump<\/code>,<\/strong> and periodically test that backups can actually be restored, since an untested backup provides false confidence rather than real protection.<\/li>\n<\/ul>\n\n\n\nXVI. Conclusion<\/h3>\n\n\n\n
You’ve now installed PostgreSQL on Ubuntu, verified the service is running and set to start on boot, and created a dedicated database and user rather than relying on the default superuser account for everyday use. Along the way, this guide corrected a common typo (
postgres<\/code>, notpostgresql<\/code>, as the system username) and covered the authentication quirks \u2014 particularlypeer<\/code> vs. TCP-based password authentication \u2014 that trip up a lot of newcomers on their first PostgreSQL setup. With that foundation in place, you’re ready to connect real applications to your database securely.<\/p>\n\n\n\nFor deeper reference on roles, authentication methods, and configuration options, see the official PostgreSQL documentation.<\/p>\n\n\n\n
\n\n\n\nFrequently Asked Questions<\/h2>\n\n\n\n
Why do I get “role does not exist” even though I just installed PostgreSQL?<\/strong> This almost always means a typo in the username \u2014 the correct default administrative user is
postgres<\/code>, notpostgresql<\/code>. Double-check the exact spelling in your command.<\/p>\n\n\n\nWhat’s the difference between peer authentication and password authentication in PostgreSQL?<\/strong> Peer authentication (used by default for local Unix socket connections) trusts the connection based on the Linux system username matching the PostgreSQL role name, with no password required. Password-based authentication (like
scram-sha-256<\/code>) requires an actual password, and applies to TCP connections or when explicitly configured inpg_hba.conf<\/code>.<\/p>\n\n\n\nCan I use PostgreSQL and MySQL on the same server at the same time?<\/strong> Yes \u2014 the two use different default ports (5432 for PostgreSQL, 3306 for MySQL) and are otherwise independent, so running both simultaneously on the same host is common and generally not an issue, as long as the server has adequate resources for both.<\/p>\n\n\n\n
Is it safe to expose PostgreSQL’s port directly to the internet?<\/strong> Generally not recommended. Keep PostgreSQL bound to
localhost<\/code> for local application connections, and use SSH tunneling, a VPN, or a properly firewalled private network for any legitimate remote access needs instead of exposing port 5432 publicly.<\/p>\n\n\n\n
\n\n\n\nTalk to Our Technology Experts<\/h2>\n\n\n\n
Setting up or managing a PostgreSQL database? Our team can help with database administration and server security.<\/p>\n\n\n\n
Connect with our technology experts.<\/a><\/p>\n\n\n\n
Related Articles<\/h2>\n\n\n\n
- Restrict network exposure.<\/strong> Unless remote connections are genuinely required, keep PostgreSQL bound to
- Client applications<\/strong> \u2014 whether that’s the
- The PostgreSQL server process<\/strong> (
![\"\"](\"https:\/\/pheonixsolutions.com\/blog\/wp-content\/uploads\/2017\/07\/postgresql_architecture_v2-scaled.png\")
<\/a><\/figure>\n\n\n\n