PostgreSQL Database, Schema and Table Privileges

Roles and Authentication / Guide 19 of 47 / Intermediate

• OSUbuntu 26.04 LTS
• PostgreSQL18.6-0ubuntu0.26.04.1
• Authscram-sha-256
• TimeAbout 16 min

PUBLIC holds CONNECT on every new database by default, which is why the first step revokes it - otherwise the database-level denial cannot be demonstrated.

This guide includes

Use this when a role cannot read a table and you are unsure which grant is missing. This matters because there are three separate refusals on the way in - the database, the schema, then the table - and each names a different object.

• being refused at the database, before anything else happens
• granting CONNECT, and then being refused at the schema
• granting USAGE, and then being refused at the table itself

Before you start

• The appdb database with a sales schema.

1. 1

   Be refused at the database

2. 2

   Grant CONNECT, then be refused at the schema

3. 3

   Grant USAGE, then be refused at the table

Official sources

• PostgreSQL 18 Documentation - Database Roles
• PostgreSQL 18 Documentation - The pg_hba.conf File
• PostgreSQL 18 Documentation - Password Authentication
• PostgreSQL 18 Documentation - Privileges
• PostgreSQL 18 Documentation - ALTER DEFAULT PRIVILEGES