PostgreSQL Privileges: Why Your GRANT Is Not Working?

PostgreSQL Privileges: Why Your GRANT Is Not Working?

If you’ve ever tried to give a user access to a table and still received the dreaded Permission Denied error, you’re not alone. In PostgreSQL, permissions are like a chain if one link is missing, the whole thing fails.

Let’s break it down using a simple Office Building analogy.

The Hierarchy: Building -> Room -> Desk

To understand why your user can’t see a table, you need to understand the three layers of security:

1. The Building (Database): You need a key to enter the building (CONNECT).
2. The Room (Schema): Even if you are in the building, you can’t enter a private office without permission (USAGE).
3. The Desk (Table): Once you are in the room, you still need permission to open a specific drawer or read a file (SELECT, INSERT).

The Missing Link: USAGE

The most common mistake is giving access to the Table but forgetting the Schema. In PostgreSQL, the Schema is the container. If you don’t have USAGE rights on the Schema, you can't even see that the table exists.

Let’s Code: The Solution

Let’s say we have a database called company_db, a schema called sales_data, and a user named analyst_user.

Step 1: Grant Connection to the Building

First, the user must be able to log in to the database.

-- Connect to your database as a superuser
GRANT CONNECT ON DATABASE company_db TO analyst_user;

Step 2: Open the Door (The Critical Step!)

This is where most people fail. You must allow the user to use the schema.

-- This gives the user permission to 'walk into the room'
GRANT USAGE ON SCHEMA sales_data TO analyst_user;

Step 3: Grant Access to the Files

Now that the user is inside the room, give them permission to read the data.

-- Grant SELECT on all existing tables in that schema
GRANT SELECT ON ALL TABLES IN SCHEMA sales_data TO analyst_user;

-- Don't forget the sequences (for ID columns)
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA sales_data TO analyst_user;

Step 4: Automate the Future

What happens when you create a new table tomorrow? By default, the user won’t see it. You need to set Default Privileges:

-- Ensure future tables are also readable by the analyst
ALTER DEFAULT PRIVILEGES IN SCHEMA sales_data 
GRANT SELECT ON TABLES TO analyst_user;

Summary Checklist

• Database: Can they CONNECT?
• Schema: Do they have USAGE? (The #1 forgotten step)
• Table: Do they have SELECT / INSERT?