How to Check If a User Can Access a Database in PostgreSQL(The Right Way)

In PostgreSQL, being able to authenticate as a user does not automatically mean you can connect to a database.

Connection permission is a separate, explicit check—and if you’ve ever wondered why a valid user suddenly gets:

FATAL: permission denied for database

this article is for you.

Let’s walk through the correct and reliable way to verify database connection privileges using PostgreSQL’s built-in system function:

has_database_privilege.

---

Why This Matters

PostgreSQL performs multiple checks before allowing a connection:

1. Authentication rules (pg_hba.conf)
2. Role attributes (can the role log in?)
3. Database-level privileges (CONNECT)

Most people focus on the first two and forget the third.

That’s where has_database_privilege becomes essential.

---

What Is has_database_privilege?

has_database_privilege is a system information function that returns a boolean:

See also: Mastering the Linux Command Line — Your Complete Free Training Guide

• true → the privilege exists
• false → it does not

It answers a simple but critical question:

Does this role have permission to perform a specific action on this database?

---

Basic Syntax

There are two common ways to call the function.

1. Check a specific user

SELECT has_database_privilege('username', 'database_name', 'CONNECT');

Use this when auditing permissions for another role.

---

2. Check the current session user

SELECT has_database_privilege('database_name', 'CONNECT');

This version implicitly checks current_user and is useful for debugging live sessions.

---

Understanding the Arguments

1. User

You can specify the user in several ways:

• Role name (text)
• Role OID
• public (the pseudo-role representing all users)

Example:

SELECT has_database_privilege('public', 'app_db', 'CONNECT');

This tells you whether all users can connect to the database.

---

2. Database

Like users, databases can be identified by:

• Database name
• Database OID

Using names is usually clearer unless you’re working inside system catalogs.

---

3. Privilege

For database-level checks, valid privilege strings include:

• CONNECT
• CREATE
• TEMPORARY
• TEMP (alias for TEMPORARY)

For connection access, CONNECT is the key privilege.

---

Advanced Usage Patterns

Checking Multiple Privileges

You can test more than one privilege at once by separating them with commas.

SELECT has_database_privilege(
  'alice',
  'sales_db',
  'CONNECT, CREATE'
);

Important:

The function returns true if the user has any one of the listed privileges—not all of them.

---

Checking Grant Options

To see whether a role can grant a privilege to others, append:

WITH GRANT OPTION

Example:

SELECT has_database_privilege(
  'alice',
  'sales_db',
  'CONNECT WITH GRANT OPTION'
);

This is particularly useful for security audits and delegated administration.

---

CONNECT Privilege vs. pg_hba.conf

This is where many people get confused.

Even if has_database_privilege returns true, PostgreSQL may still deny the connection.

Why?

Because PostgreSQL checks both:

Layer	Purpose
pg_hba.conf	Who is allowed to attempt a connection
CONNECT privilege	Who is allowed to enter the database

Both must pass.

Real-World Scenario

• User has CONNECT privilege
• pg_hba.conf blocks their IP

➡️ Connection fails

Or:

• pg_hba.conf allows the connection
• CONNECT privilege was revoked

➡️ Connection fails

---

Default Permissions: A Hidden Footgun

By default, PostgreSQL grants the PUBLIC role:

• CONNECT
• TEMPORARY

on newly created databases.

This means:

Unless explicitly revoked, any authenticated user can connect.

To lock a database down properly, administrators often do:

REVOKE CONNECT ON DATABASE app_db FROM PUBLIC;

Then grant access explicitly to approved roles.

---

Practical Use Cases

• Debugging mysterious “permission denied for database” errors
• Auditing database access before production launches
• Verifying security hardening steps
• Writing monitoring or compliance checks
• Explaining access issues to application teams

---

A Simple Analogy

Think of connecting to a PostgreSQL database like entering a high-security office building:

• pg_hba.conf is the security guard at the gate (checks who you are and where you’re coming from)
• CONNECT privilege is the keycard for a specific office door

You need both.

Using has_database_privilege is like checking the access control system to see whether an employee’s keycard is authorized for that door—before they try to enter.

---

Final Thoughts

If you work with PostgreSQL in production, has_database_privilege should be part of your default troubleshooting toolkit.

It’s simple, reliable, and removes guesswork from permission checks—especially in environments with:

• Multiple roles
• Strict security policies
• Automated provisioning

When PostgreSQL says “permission denied,” this function helps you find out why—with certainty.