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:
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:
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:
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):
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:
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:
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:
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 comparemerchant_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:
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:
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:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
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:
โโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโโ
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:
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_teamrole with SELECT and UPDATE on transactions. - Create a
finance_leaduser 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?
Here's a structure to follow:
Test Your Knowledge
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 โ