Roles and Users

In PostgreSQL, access control revolves around roles. A role is a user or group with defined permissions. You can create roles that don't have login privileges (used for grouping permissions) and separate users with login privileges (used for database access).

This separation allows you to manage permissions at scale. For example, you might have a data_analyst role with SELECT-only access, and then create multiple users who inherit that role.

Q1: Create a Role

Let's create a role for analysts with no login privileges:

sql
CREATE ROLE

Breakdown:

  • CREATE ROLE analyst โ€” Creates a new role named "analyst".
  • WITH NOLOGIN โ€” This role cannot directly log in. It's used for grouping permissions only.

Q2: Grant SELECT Permissions to a Role

Now grant the analyst role permission to SELECT from specific tables in NovaPay:

sql
GRANT

Breakdown:

  • GRANT SELECT ON transactions TO analyst โ€” Allows the analyst role to read from the transactions table.
  • Notice we grant each table separately. Each GRANT is a precise permission.

Q3: Create a User and Assign the Role

Create an actual user who can log in and inherit all analyst permissions:

sql
CREATE ROLE / GRANT

Breakdown:

  • CREATE USER analyst_user WITH PASSWORD โ€” Creates a user with login privileges (users are roles with login).
  • GRANT analyst TO analyst_user โ€” Assigns all permissions from the analyst role to this user.
  • Now analyst_user can log in and automatically has SELECT on transactions, merchants, and webhooks.

Security Note: Always use strong passwords and never hardcode credentials in code. In production, rotate passwords regularly and use environment variables.

Granting and Revoking Permissions

The least privilege principle is fundamental to database security: give users the minimum permissions they need to do their job. No more, no less. When permissions are no longer needed, revoke them immediately.

Q4-Q5: Create Role with Specific Permissions

Let's create a role for a merchant application that needs to INSERT and UPDATE transactions (but not DELETE):

sql
CREATE ROLE / GRANT

Breakdown:

  • GRANT SELECT, INSERT, UPDATE โ€” Grant multiple permissions in one statement, separated by commas.
  • Notice we don't grant DELETE. The merchant app should never delete transactions.

Q6: Revoke Permissions

Later, if the requirements change, revoke INSERT permission:

sql
REVOKE

Breakdown:

  • REVOKE INSERT ON transactions FROM merchant_app โ€” Removes the INSERT permission.
  • The merchant_app role now has only SELECT and UPDATE on transactions.
  • Revoke is symmetric to GRANT: whatever you can GRANT, you can REVOKE.

Least Privilege Principle: Start with zero permissions. Grant only what's needed. Regularly audit and revoke unused permissions. This limits the damage if an account is compromised.

Row-Level Security (RLS)

Roles and permissions work at the table level: you can grant or deny access to entire tables. But what if you need finer-grained control? What if a merchant should only see their own transactions?

That's where Row-Level Security (RLS) comes in. RLS enforces filtering at the database level, so users can only see rows that match a policy, even if they have SELECT permission on the table. This is far more secure than app-level filtering.

Q7: Enable RLS on a Table

Enable RLS on the transactions table:

sql
ALTER TABLE

Breakdown:

  • Once RLS is enabled, all SELECT queries for this table must match at least one policy.
  • If no policies exist, no one (except table owner) can query the table.
  • Policies define the filtering rules.

Q8: Create an RLS Policy

Create a policy that allows analysts to read all transactions, and let the merchant app read only their own merchant's transactions:

sql
CREATE POLICY

Breakdown:

  • CREATE POLICY analyst_read_only โ€” Names the policy.
  • ON transactions โ€” Applies to the transactions table.
  • FOR SELECT โ€” This policy applies to SELECT queries.
  • USING (current_user = current_user) โ€” The condition. This is a simplified example; in production, you'd compare merchant_id = current_merchant_id() or similar.

RLS Advantage: Filtering happens at the database layer, not the application. Even if your app has a bug, the database ensures users see only permitted rows. This is production-grade security.

Advanced Policies: Preventing Updates and Deletes

RLS is not limited to SELECT. You can create policies for INSERT, UPDATE, and DELETE. For example, prevent merchants from updating or deleting each other's transactions.

Q9: Prevent Updates with RLS

Create a policy that prevents unauthorized updates:

sql
CREATE POLICY

Breakdown:

  • FOR UPDATE โ€” This policy applies to UPDATE statements.
  • USING (...) โ€” Checks which rows can be updated (must match the condition).
  • WITH CHECK (...) โ€” Checks the values being set (must also match the condition).
  • This prevents users from updating rows that don't belong to them.

Q10: Prevent Deletes with RLS

Create a policy that prevents deletes of transactions:

sql
CREATE POLICY

Breakdown:

  • FOR DELETE โ€” This policy applies to DELETE statements.
  • USING (FALSE) โ€” The condition is always false, so no deletions are allowed.
  • In a financial system like NovaPay, you never want to delete transaction records. This policy enforces it at the database level.

RLS for Data Integrity: Use RLS to enforce business rules at the database level. No amount of app-level validation is as reliable as database constraints and policies.

Checking Your Work: Auditing Roles and Policies

After creating roles, users, and policies, it's important to audit what you've set up. PostgreSQL provides system tables and functions to view all current access controls.

View All RLS Policies

Query the pg_policies system table to see all policies on a table:

sql
schemaname | tablename      | policyname                | permissive | roles | cmd      | qual
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
public         | transactions | analyst_read_only          | PERMISSIVE | {public} | SELECT | (CURRENT_USER = CURRENT_USER)
public         | transactions | prevent_unauthorized_update | PERMISSIVE | {public} | UPDATE | (CURRENT_USER = SESSION_USER)
public         | transactions | prevent_delete              | PERMISSIVE | {public} | DELETE | FALSE

Check Current User

View your current database user and session user:

sql
current_user               | session_user
โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
analyst_user               | analyst_user

What these mean:

  • session_user โ€” The user who logged in (used for login credentials).
  • current_user โ€” The role being used for permission checks (can change with SET ROLE).

View All Roles and Their Permissions

Query the information_schema to see all roles:

sql

Auditing Best Practice: Regularly run these queries to audit who has access to what. Remove unused roles and revoke unused permissions. In production, log all GRANT and REVOKE statements.

Challenge

Create a complete access control setup for NovaPay:

  • Create a finance_team role with SELECT and UPDATE on transactions.
  • Create a finance_lead user and grant them the finance_team role.
  • Create a policy on transactions that allows the finance_team to update only transaction status (not amount).
  • Test by querying pg_policies to confirm the policy exists.

What's your SQL to set this up?

Show hint

Here's a structure to follow:

sql

Test Your Knowledge

Question 1 of 5
What is the difference between a ROLE and a USER in PostgreSQL?
Question 2 of 5
What is the best way to ensure a merchant sees only their own transaction data in NovaPay?
Question 3 of 5
Why is Row-Level Security more secure than app-level filtering?
Question 4 of 5
What does the REVOKE statement do?
Question 5 of 5
Can you use GRANT to give permission on specific columns only?
0
/5
Africa & India's Best AI Academy
๐Ÿค– AI-first ๐ŸŽ“ Placement Assistance

Your database is now secure.

Join the Best AI Program: The AI-Era Data Engineer โ€” 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Data Engineer โ†’