Table Of Contents
  1. 🧪 Build Your PostgreSQL RBAC Lab
  2. 🏦 Meet Our Credit Card Application — The RBAC Scenario
  3. 🔐 Understanding PostgreSQL Roles — LOGIN vs NOLOGIN
  4. 🧪 Hands-On — Create Our PostgreSQL Roles
  5. 🏦 Creating Our PostgreSQL Application Database
  6. 🗂 Creating Our Application Schema
  7. 📋 Creating Our Application Tables
  8. 🔑 Configuring Database Access — CONNECT Privilege
  9. 🚪 Configuring Schema Access — USAGE vs CREATE
  10. 📋 Configuring Table Privileges — SELECT, INSERT, UPDATE, DELETE
  11. 🧪 Testing RBAC — Application vs Reporting Access
  12. 🔗 PostgreSQL Role Membership — INHERIT vs SET ROLE
  13. 🔐 PostgreSQL Object Ownership — Why GRANT ALL Is Not Ownership
  14. 🔍 Auditing PostgreSQL RBAC — Verify Effective Privileges

🧪 Build Your PostgreSQL RBAC Lab

Understanding PostgreSQL roles and privileges becomes much easier when you see them in action.

Instead of simply memorizing GRANT and REVOKE commands, we’ll build a small credit-card application environment and explore how PostgreSQL manages users, object ownership, and access permissions.

🎯 What will you learn?

Through this hands-on lab, we’ll explore:

  • 🔐 Roles and Users — Understand LOGIN, NOLOGIN, and why PostgreSQL treats users and groups as roles.
  • 🏦 Object Ownership — Discover how a NOLOGIN role can own databases, schemas, and tables.
  • 🔑 Privileges and Access Control — Learn how database, schema, and table permissions work together.
  • 🔗 Role Membership — Understand INHERIT, SET ROLE, and how privileges flow between roles.
  • 🛡 Least-Privilege Security — Build separate read-only and read-write access for application and reporting users.
  • ⚙ Default Privileges — Automatically configure access for future database objects.

🖥 Lab prerequisites

You’ll need:

  • PostgreSQL 16 or later. Our examples use PostgreSQL 16.
  • Access to a PostgreSQL administrator account for initial configuration.
  • A SQL client such as psql.
  • A dedicated sandbox environment where you can safely create roles, databases, and tables.

🚀 Don’t have a PostgreSQL sandbox yet?

No problem! I’ve documented the complete PostgreSQL sandbox setup separately so we can keep this article focused on RBAC.

👉 Build Your PostgreSQL Sandbox — Step-by-Step Guide

If you already have PostgreSQL running, you can skip the installation guide and follow the exercises directly.

💡 A note for Oracle DBAs

If you’re coming from Oracle, some PostgreSQL concepts may initially look familiar but behave differently—particularly the relationship between users, roles, schemas, and object ownership.

We’ll highlight these differences wherever they matter, using practical examples rather than lengthy theory.

✅ Ready?

With our sandbox prepared, let’s build a simple production-inspired credit-card application and implement PostgreSQL RBAC step by step.

🏦 Meet Our Credit Card Application — The RBAC Scenario

Imagine we’re managing a PostgreSQL database for a credit-card application.

The application stores customer information and credit-card account details. Different users and services need different levels of access, just like in a real production environment.

Our challenge is simple: How do we ensure everyone gets exactly the access they need—nothing more?

🎯 Real-World Access Requirements

  • 💳 Application Service: Needs to read, insert, update, and delete customer and account records.
  • 📊 Reporting User: Needs read-only access to generate reports without modifying application data.
  • 🔐 Object Owner: Owns application tables and schemas but should not be used as a normal application login.
  • 🛡 Security: Application and reporting users must not be able to create, alter, or drop application tables.

🗂 Our PostgreSQL Lab Architecture

We’ll use a small, easy-to-follow environment throughout this guide.

ComponentNamePurpose
DatabasecarddbCredit-card application database
SchemaappContains application objects
TablecustomersCustomer information
TableaccountsCredit-card account details
Ownership roleapp_ownerOwns application objects
Read-only roleapp_roHolds SELECT privileges
Read-write roleapp_rwHolds INSERT, UPDATE, DELETE privileges
Application loginapp_userConnects on behalf of the application
Reporting loginreport_userConnects for reporting

🔑 Two Types of Roles in Our Design

PostgreSQL doesn’t maintain separate user and group object types. Both are implemented as roles.

We’ll use two categories:

  • 👤 LOGIN roles: app_user and report_user — identities that can authenticate to PostgreSQL.
  • 🔒 NOLOGIN roles: app_owner, app_ro, and app_rw — roles that hold ownership or access privileges without directly authenticating.

💡 Why this separation?

Instead of granting table privileges to each application user individually, we assign permissions to reusable NOLOGIN roles and grant those roles to the appropriate LOGIN identities.

This makes access management simpler, more consistent, and easier to audit.

🛡 Our Security Goal

By the end of this lab:

  • app_user can read and modify application data but cannot perform application DDL.
  • report_user can read application data but cannot modify it.
  • app_owner consistently owns the database objects.
  • Privileges are managed through role membership rather than scattered direct grants.

We’ll build this model step by step and verify it using PostgreSQL’s system catalogs and privilege-checking functions.

🔗 Visualizing Our RBAC Architecture

  app_owner [NOLOGIN]
 │
 Owns objects
 │
 ┌────────┴────────┐
 ▼ ▼
 app.customers app.accounts


report_user [LOGIN] app_user [LOGIN]
 │ │
 ▼ ▼
 app_ro [NOLOGIN] app_rw [NOLOGIN]
 SELECT INSERT, UPDATE, DELETE
 ▲ │
 └──────── Inherits RO ───────────┘

🔐 Understanding PostgreSQL Roles — LOGIN vs NOLOGIN

In PostgreSQL, users and groups are not separate database objects. Both are implemented using a single concept called a ROLE.

What makes them different is whether the role is allowed to log in.

👤 LOGIN Role — Who Connects?

A role with the LOGIN attribute can authenticate and establish a PostgreSQL session, provided it also satisfies authentication rules and has database CONNECT privileges.

For example, our application and reporting identities will be:

  • app_user — connects on behalf of the credit-card application.
  • report_user — connects to generate reports.

🔒 NOLOGIN Role — Who Owns or Holds Privileges?

A NOLOGIN role cannot authenticate directly, but it can still own database objects, receive privileges, and be granted to other roles.

In our lab:

  • app_owner — owns the application schema and tables.
  • app_ro — holds read-only privileges.
  • app_rw — holds read-write privileges.

💡 Why separate LOGIN and NOLOGIN roles?

Instead of granting privileges individually to every application user, we grant them to reusable access roles.

For example:

GRANT app_ro TO report_user; GRANT app_rw TO app_user;

The login roles can then use the privileges inherited through their memberships, subject to the membership’s INHERIT setting.

This gives us a clean separation between authentication, authorization, and ownership.

🆚 Oracle DBA Perspective

In Oracle, a schema is closely associated with a database user. In PostgreSQL, schemas are independent objects, and a NOLOGIN role can own schemas and tables.

For example, app_owner can own all our application objects without ever being used as an application login.

This is one of the most useful PostgreSQL security concepts for Oracle DBAs to understand.

✅ Key Takeaway

  • LOGIN determines whether a role can authenticate.
  • NOLOGIN prevents direct authentication, not ownership or privilege grants.
  • Role membership allows privileges to be shared without granting everything directly to individual login accounts.
  • Object ownership is separate from the ability to connect.

🧪 Hands-On — Create Our PostgreSQL Roles

💡 “Oracle DBA Tip” :

Coming from Oracle CDB/PDB? PostgreSQL roles are created at the cluster level, regardless of which database you’re connected to. You don’t need to recreate application roles in each database.

Now let’s create the roles we’ve just discussed.

Step 1 — Connect as the PostgreSQL administrator

From your sandbox terminal: (as postgres user)

psql -p 5432 -d postgres

Step 2 — Create the three NOLOGIN roles

CREATE ROLE app_owner NOLOGIN; CREATE ROLE app_ro NOLOGIN; CREATE ROLE app_rw NOLOGIN;

Step 3 — Create the two LOGIN roles

For the sandbox, we’ll use example passwords. These are lab credentials, not production recommendations.

CREATE ROLE app_user  LOGIN PASSWORD 'AppLab123!';  CREATE ROLE report_user  LOGIN PASSWORD 'ReportLab123!';

Step 4 — Verify all five roles

SELECT  rolname AS role_name,  CASE  WHEN rolcanlogin THEN 'LOGIN'  ELSE 'NOLOGIN'  END AS role_type,  rolsuper AS superuser,  rolcreatedb AS create_db,  rolcreaterole AS create_role,  rolinherit AS inherit,  rolreplication AS replication,  rolbypassrls AS bypass_rls,  rolconnlimit AS connection_limit FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'  AND oid <> 10 ORDER BY rolname;

Expected output:

🔎 What have we achieved?

  • ✅ Created separate identities for application and reporting connections.
  • ✅ Created reusable roles for ownership and privileges.
  • ✅ Avoided giving application users superuser or role-administration privileges.
  • ✅ Established the foundation for a least-privilege RBAC model.

💡 Production Tip

Application logins should not normally own application tables. Keeping ownership in a separate NOLOGIN role makes deployments, privilege management, and security auditing more consistent.

🏦 Creating Our PostgreSQL Application Database

Now that we’ve created our PostgreSQL roles, let’s create a dedicated database for our credit-card application.

🎯 Why a Separate Application Database?

In a production environment, applications typically use dedicated databases to keep their objects and access controls logically separated.

For our lab:

  • 🏦 Database: carddb — Stores our credit-card application data.
  • 🔐 Database Owner: app_owner — A NOLOGIN role responsible for ownership.
  • 👤 Application Login: app_user — Will connect to the database and perform application operations.
  • 📊 Reporting Login: report_user — Will connect with read-only privileges.

🔐 Create the Database with a NOLOGIN Owner

Create our application database:

CREATE DATABASE carddb  OWNER app_owner;

Expected result:

postgres=# CREATE DATABASE carddb OWNER app_owner; CREATE DATABASE postgres=#

💡 Interesting PostgreSQL Behavior

Notice that app_owner was created with the NOLOGIN attribute.

Yet PostgreSQL allows it to own an entire database!

This demonstrates an important concept:

A PostgreSQL role does not need LOGIN privileges to own databases, schemas, tables, or other database objects.

🔍 Verify Database Ownership

Instead of writing a query that checks only our carddb database, let’s use a reusable PostgreSQL catalog query suitable for any environment.

SELECT  d.datname AS database_name,  pg_get_userbyid(d.datdba) AS database_owner,  pg_encoding_to_char(d.encoding) AS encoding,  d.datallowconn AS allows_connections,  d.datconnlimit AS connection_limit FROM pg_catalog.pg_database d WHERE NOT d.datistemplate ORDER BY d.datname;

This query helps DBAs identify:

  • ✅ All non-template databases in the PostgreSQL cluster.
  • 👤 The owner of each database.
  • 🔤 Database encoding.
  • 🔌 Whether connections are allowed.
  • 📊 Configured connection limits.

Example output:

 database_name | database_owner | encoding | allows_connections | connection_limit
---------------+----------------+----------+--------------------+-----------------
 carddb | app_owner | UTF8 | t | -1
 postgres | postgres | UTF8 | t | -1

A connection limit of -1 means no database-specific connection limit is configured; other PostgreSQL connection limits still apply.

🆚 Oracle DBA Perspective

If you’re coming from Oracle CDB/PDB architecture, remember:

  • PostgreSQL roles are cluster-wide.
  • Databases belong to a PostgreSQL cluster.
  • Schemas exist inside individual databases.
  • A NOLOGIN role can own a database without being used for authentication.

Unlike Oracle, a PostgreSQL schema is not automatically created simply because a role exists.

We’ll configure access in separate layers:

  1. 🔑 Database CONNECT — Allows a role to establish a connection to the database.
  2. 🚪 Schema USAGE — Allows a role to resolve and reference objects within a schema.
  3. 📋 Table Privileges — Determine whether the role can SELECT, INSERT, UPDATE, or DELETE data.

PostgreSQL grants some database privileges to PUBLIC by default, so we’ll also review and restrict those permissions when configuring our security model.

✅ Key Takeaway

We’ve created a dedicated application database owned by a NOLOGIN role.

This establishes a clean separation between database ownership and application authentication, forming the foundation of our PostgreSQL RBAC design.

🗂 Creating Our Application Schema

A PostgreSQL schema organizes database objects such as tables, views, and functions.

We’ll create the app schema inside carddb, owned by our NOLOGIN role app_owner.

🔐 Connect to the Application Database

\c carddb

🛠 Create the Schema

CREATE SCHEMA app AUTHORIZATION app_owner;

🔍 Verify Schema Ownership

Use this reusable query to list user-defined schemas and their owners:

SELECT  n.nspname AS schema_name,  pg_get_userbyid(n.nspowner) AS schema_owner FROM pg_catalog.pg_namespace n WHERE n.nspname <> 'information_schema'  AND n.nspname !~ '^pg_' ORDER BY n.nspname;

Expected Result

💡 Oracle DBA Tip

Unlike Oracle, PostgreSQL schemas are independent objects. Creating a role does not automatically create a schema with the same name.

✅ Key Takeaway

  • 🏦 carddb — Our application database.
  • 📁 app — Our application schema.
  • 🔐 app_owner — Owns the schema but cannot log in directly.

📋 Creating Our Application Tables

We’ll create two tables inside the app schema:

  • 👤 customers — Stores customer information.
  • 💳 accounts — Stores credit-card account details.

Both tables will be owned by app_owner.

🔐 Switch to the Object Owner

Connect to carddb as postgres, then switch roles:

SET ROLE app_owner;  SELECT current_user, session_user;

Expected:

current_user | session_user -------------+------------- app_owner | postgres

💡 SET ROLE lets us create objects as app_owner, even though it is a NOLOGIN role.

🛠 Create the Customers Table

CREATE TABLE appSource：https://postgr.es/p/9yu
My favorites
PreviousMemtest86+ v9.00 Released With Support For ARM64 Memory TestingNextCleaning a giant ship: Roomba-like robots walk on magnetic feet to scrub giant ships

Related posts

SOLID Principles Explained: 5 Essential Principles for Writing Clean, Maintainable Code

SOLID Principles Explained: 5 Essential Principles for Writing Clean, Maintainable Code

dev.to Tutorial
How to Build a Professional Resume in 2026: Best Resume Formats, Templates, and ATS Tips

How to Build a Professional Resume in 2026: Best Resume Formats, Templates, and ATS Tips

dev.to Career
How to Explain Your Project in a Technical Interview

How to Explain Your Project in a Technical Interview

dev.to Career
Stop Calling It "Refactoring" When You Are Just Moving The Bugs Around

Stop Calling It "Refactoring" When You Are Just Moving The Bugs Around

dev.to Career
PostgreSQL migrations that lock a busy table

PostgreSQL migrations that lock a busy table

dev.to Testing
🧠 Do Prompt ao RAG: como fazer um LLM responder sem inventar (e sem processo do cliente)

🧠 Do Prompt ao RAG: como fazer um LLM responder sem inventar (e sem processo do cliente)

dev.to Java