Real-World PostgreSQL Permission Matrix & User Management

  Real-World PostgreSQL Permission Matrix

Role	Access	Purpose

readonly

SELECT on all tables

BI/reporting, analysts

readwrite

SELECT, INSERT, UPDATE

App developers, staging users

admin

ALL PRIVILEGES on DB

Senior DBAs, migration scripts

auditor

SELECT + pg_stat* views

Auditing, compliance, logs

-- Create roles

CREATE ROLE readonly;

CREATE ROLE readwrite;

CREATE ROLE admin;

CREATE ROLE auditor;

-- Grant example:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO readwrite;

GRANT ALL PRIVILEGES ON DATABASE your_db TO admin;

🧪 Hands-On Mini Project: Secure a Real Table

Step 1: Create users

CREATE ROLE hr_user WITH LOGIN PASSWORD 'hrpass';

CREATE ROLE dev_user WITH LOGIN PASSWORD 'devpass';

Step 2: Create table as superuser

CREATE TABLE employee_salary (

    id SERIAL PRIMARY KEY,

    emp_name TEXT,

    salary NUMERIC

);

-- Step 3: Revoke all access

REVOKE ALL ON employee_salary FROM PUBLIC;

-- Step 4: Grant only to HR

GRANT SELECT, INSERT, UPDATE ON employee_salary TO hr_user;

 Now:

• hr_user can access employee_salary.

• dev_user gets permission denied.

• 🔐 5. Role & Privilege Management

• CREATE ROLE app_user LOGIN PASSWORD 'securepass';

• GRANT CONNECT ON DATABASE mydb TO app_user;

• 🔐 6. Auditing and Logging

  Enable and configure logs in postgresql.conf:

  logging_collector = on
  log_connections = on
  log_disconnections = on
  log_statement = 'ddl'

• 1. 🏛️ Database-Level Security

-- Prevent a user from connecting to a specific database

REVOKE CONNECT ON DATABASE mydb FROM someuser;

-- Allow connection

GRANT CONNECT ON DATABASE mydb TO app_user;

2. 📂 Schema-Level Security

Controls access to objects (tables, views, functions, etc.) in a schema.

 Key Concepts:

• Use GRANT USAGE for schema access.

• Use GRANT CREATE to allow object creation.

3. 🧱 Table-Level Security

Controls access to entire tables for SELECT, INSERT, UPDATE, DELETE.

Key Concepts:

• Use GRANT/REVOKE on specific privileges.

• -- Allow read-only access

  GRANT SELECT ON employees TO analyst_user;

  -- Allow full access

  GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO app_user;

  -- Revoke access

  REVOKE ALL ON employees FROM intern_user;

4. 📊 Column-Level Security

Controls which columns a user can SELECT or UPDATE.

Key Concepts:

• PostgreSQL supports GRANT and REVOKE at the column level.

• -- Grant access only to name and department

  GRANT SELECT (name, department) ON employees TO analyst_user;

  -- Allow update to only the salary column

  GRANT UPDATE (salary) ON employees TO hr_manager;

Example :

I have create two user: USER  :- sam , jack

# psql -U sam -p 5432 -d scott;

# create table tree (type varchar);

Now I am going to login with jack:

#psql -U jack -p 5432 -d scott

#select * from tree;

scott=> select * from tree;

ERROR:  permission denied for relation tree

Now I want to give permission to jack for table 

#psql -U sam -p 5432 -d scott

scott=# grant select on tree to jack;

GRANT