Recently, I gave a presentation on database security, focusing on essential aspects such as users, roles, permissions, Row-Level Security (RLS), and Column-Level Security (CLS). Inspired by that experience, I thought of putting together a comprehensive blog post that deeply explores these crucial elements.
This post will provide a concise overview of how to implement a robust security model that addresses the following key elements:
- User Roles and Permissions: Establishing distinct roles to control access and operations.
- Row-Level Security (RLS): Customizing data access based on user roles.
- Column-Level Security (CLS): Protecting sensitive data (e.g., salary, phone number) through encryption.
- Auditing and Compliance: Utilizing tools like pgAudit to track actions on employee records and ensure adherence to data governance policies.
This post assumes that readers have a basic understanding of users, roles, and privileges in PostgreSQL. While the demonstration may not adhere strictly to best practices, it aims to illustrate how these components work together to create a secure database environment.
Let’s delve into the key requirements and strategies for achieving these objectives.
We will explore these concepts through the example of Pghyd, illustrating how they work together to enhance data security and privacy in PostgreSQL.
Table of Contents
Problem Objective:
Implement a database security model for the Pghyd organization to manage employee data while ensuring data privacy and compliance.
Key Requirements:
User Roles and Permissions:
- pghyd_viewer: Limited access to specific columns (e.g., cannot see salary or phone number).
- pghyd_core: Full access to all data and operations.
- pwi_core: Manage PWI-specific records with full access except for delete.
- pwi_regular: Read-only access to PWI records, with no modification rights.
Data Privacy and Security:
- Implement Row-Level Security (RLS) to restrict data access based on roles.
- Prevent certain roles from deleting records to maintain data integrity.
Column-Level Security:
- Protect sensitive information (salary, phone number) using encryption and restrict access to specific roles.
Auditing and Compliance with pgAudit:
Ensure that all actions on employee records comply with data governance policies, with tracking of access for audits.
Implementation:
Step 1: Create the Database
First, we create a new database named pghyd_db to hold our employee data. After creating the database,
CREATE DATABASE pghyd_db; \c pghyd_db;
we connect to it and define the pghyd_employees table to store employee information, including sensitive details like salary and phone numbers.
CREATE TABLE pghyd_employees (
id SERIAL PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50),
salary NUMERIC,
organization VARCHAR(50),
finalized BOOLEAN,
phone_number BYTEA,
CONSTRAINT phone_number_encrypt CHECK (phone_number IS NOT NULL) );Next, we insert sample data into the pghyd_employees table, the data has sensitive information, so we need to install pgcrypto before inserting data.
Using the pgp_sym_encrypt function from the pgcrypto extension to encrypt the phone numbers.
-- Create extension pgcrypto CREATE extension IF NOT EXISTS pgcrypto;
-- Insert sample data INSERT INTO pghyd_employees (name, department, salary, organization, finalized, phone_number) VALUES ('Frank', 'Engineering', 85000, 'PWI', FALSE, pgp_sym_encrypt('8888888888', 'mysecretpass')), ('Grace', 'HR', 65000, 'PGHYD', TRUE, pgp_sym_encrypt('9999999999', 'mysecretpass')), ('Heidi', 'Marketing', 72000, 'PWI', FALSE, pgp_sym_encrypt('1231231234', 'mysecretpass')), ('Ivan', 'Marketing', 68000, 'PGHYD', TRUE, pgp_sym_encrypt('2342342345', 'mysecretpass')), ('Judy', 'Finance', 92000, 'PWI', FALSE, pgp_sym_encrypt('3453453456', 'mysecretpass')), ('Mallory', 'Engineering', 78000, 'PWI', TRUE, pgp_sym_encrypt('4564564567', 'mysecretpass')), ('Niaj', 'HR', 62000, 'PGHYD', FALSE, pgp_sym_encrypt('5675675678', 'mysecretpass')), ('Olivia', 'Finance', 82000, 'PGHYD', TRUE, pgp_sym_encrypt('6786786789', 'mysecretpass')), ('Peggy', 'Engineering', 80000, 'PWI', FALSE, pgp_sym_encrypt('7897897890', 'mysecretpass')), ('Trent', 'Marketing', 71000, 'PGHYD', TRUE, pgp_sym_encrypt('8908908901', 'mysecretpass'));
Step 2: Create Roles
Now, we create several roles to define different levels of access within the organization:
-- Create roles
CREATE ROLE pghyd_viewer;
CREATE ROLE pghyd_core;
CREATE ROLE pwi_core;
CREATE ROLE pwi_regular;
Step 3: Grant Permissions to Roles
-- Grant full access to pghyd_core and pwi_core roles GRANT SELECT, INSERT, UPDATE, DELETE ON pghyd_employees TO pghyd_core; GRANT SELECT, INSERT, UPDATE, DELETE ON pghyd_employees TO pwi_core;
-- Grant SELECT access to pwi_regular role GRANT SELECT ON pghyd_employees TO pwi_regular;
-- Grant SELECT access on few columns to pghyd_viewer GRANT SELECT (name, department,organization, finalized) ON pghyd_employees TO pghyd_viewer;
Step 4: Enable Row-Level Security
To further enhance security, we enable Row-Level Security (RLS) on the pghyd_employees table:
-- Enable row-level security on the table
ALTER TABLE pghyd_employees ENABLE ROW LEVEL SECURITY;
Step 5: Create Row-Level Security Policies
-- pghyd_core: Full access to all data CREATE POLICY pghyd_core_access ON pghyd_employees FOR ALL TO pghyd_core USING (true);
-- pwi_core: Full access to PWI data CREATE POLICY pwi_core_access ON pghyd_employees FOR ALL TO pwi_core USING (organization = 'PWI'); -- Assuming organization is used to filter PWI data
-- pwi_regular: Select access to PWI data CREATE POLICY pwi_regular_access ON pghyd_employees FOR SELECT TO pwi_regular USING (organization = 'PWI'); -- Restricting to only PWI data
-- pwi_core: Cannot delete PWI data CREATE POLICY restrict_deletes_pwi_core ON pghyd_employees AS RESTRICTIVE FOR DELETE TO pwi_core USING (FALSE);
Testing:
Set Up pgAudit for auditing (Optional)
-- Create pgaudit extension
dnf install pgaudit_17
shared_preload_libraries='pgaudit'
create extension pgaudit;
alter system set pgaudit.log='all';1. Testing Access for pghyd_viewer User
SET ROLE pghyd_viewer;
SELECT name, department, organization, finalized, phone_number FROM pghyd_employees;Expected Outcome: pghyd_viewer cannot access the data because the table is enabled with RLS.
pghyd_db=# pghyd_db=# SET ROLE pghyd_viewer; SET pghyd_db=> SELECT name, department, organization, finalized, phone_number FROM pghyd_employees; ERROR: permission denied for table pghyd_employees pghyd_db=>
Apply BYPASSRLS as postgres user to pghyd_viewer
\c - postgres
ALTER USER pghyd_viewer BYPASSRLS;Check again
SET ROLE pghyd_viewer;
SELECT name, department, organization, finalized, phone_number FROM pghyd_employees;
Expected Outcome: The query should exclude the salary and phone_number columns, as the pghyd_viewer role has limited access to specific columns.
pghyd_db=# set role pghyd_viewer; SET pghyd_db=> SELECT name, department, organization, finalized, phone_number FROM pghyd_employees; ERROR: permission denied for table pghyd_employees
Check Limited Access:
Expected Outcome: You should be able to view the data.
pghyd_db=> SELECT name, department, organization, finalized FROM pghyd_employees;
name | department | organization | finalized
---------+-------------+--------------+-----------
Frank | Engineering | PWI | f
Grace | HR | PGHYD | t
Heidi | Marketing | PWI | f
Ivan | Marketing | PGHYD | t
Judy | Finance | PWI | f
Mallory | Engineering | PWI | t
Niaj | HR | PGHYD | f
Olivia | Finance | PGHYD | t
Peggy | Engineering | PWI | f
Trent | Marketing | PGHYD | t
(10 rows)
2. Testing Access for pwi_regular User
SET ROLE pwi_regular;
SELECT id, name, department, organization, finalized
FROM pghyd_employees;Expected Outcome: The user should only see PWI records, ensuring that the row-level security policy restricts access based on the organization. You cannot update/delete the data
pghyd_db=> SET ROLE pwi_regular; SELECT id, name, department, organization, finalized FROM pghyd_employees; SET id | name | department | organization | finalized ----+---------+-------------+--------------+----------- 3 | Heidi | Marketing | PWI | f 5 | Judy | Finance | PWI | f 6 | Mallory | Engineering | PWI | t 9 | Peggy | Engineering | PWI | f 1 | Frank | Engineering | PWI | f (5 rows) pghyd_db=>
3. Testing Access for pwi_core User
SET ROLE pwi_core;
SELECT id, name, department, salary, organization, finalized, pgp_sym_decrypt(phone_number, 'mysecretpass') AS decrypted_phone_number FROM pghyd_employees;
-- you can update the data
UPDATE pghyd_employees SET salary = 1000 WHERE id = 1;
-- You cannot delete the data
DELETE FROM pghyd_employees WHERE id = 1;Expected Outcome: You should be able to view PWI data, update it but cannot delete
pghyd_db=> pghyd_db=> SET ROLE pwi_core; SET pghyd_db=> SELECT id, name, department, salary, organization, finalized, pgp_sym_decrypt(phone_number, 'mysecretpass') AS decrypted_phone_number FROM pghyd_employees; id | name | department | salary | organization | finalized | decrypted_phone_number ----+---------+-------------+--------+--------------+-----------+------------------------ 3 | Heidi | Marketing | 72000 | PWI | f | 1231231234 5 | Judy | Finance | 92000 | PWI | f | 3453453456 6 | Mallory | Engineering | 78000 | PWI | t | 4564564567 9 | Peggy | Engineering | 80000 | PWI | f | 7897897890 1 | Frank | Engineering | 1000 | PWI | f | 8888888888 (5 rows) pghyd_db=> UPDATE pghyd_employees SET salary = 1000 WHERE id = 1; UPDATE 1 pghyd_db=> DELETE FROM pghyd_employees WHERE id = 1; DELETE 0 pghyd_db=>
4. Final Testing for pghyd_core User
SET ROLE pghyd_core;
SELECT id, name, department, salary, organization, finalized, pgp_sym_decrypt(phone_number, 'mysecretpass') AS decrypted_phone_number FROM pghyd_employees;
-- you can update the data
UPDATE pghyd_employees SET salary = 1000 WHERE id = 1;
-- You cannot delete the data
DELETE FROM pghyd_employees WHERE id = 1;Expected Outcome: You can do everything.
pghyd_db=> SET ROLE pghyd_core; SET pghyd_db=> SELECT id, name, department, salary, organization, finalized, pgp_sym_decrypt(phone_number, 'mysecretpass') AS decrypted_phone_number FROM pghyd_employees; id | name | department | salary | organization | finalized | decrypted_phone_number ----+---------+-------------+--------+--------------+-----------+------------------------ 2 | Grace | HR | 65000 | PGHYD | t | 9999999999 3 | Heidi | Marketing | 72000 | PWI | f | 1231231234 4 | Ivan | Marketing | 68000 | PGHYD | t | 2342342345 5 | Judy | Finance | 92000 | PWI | f | 3453453456 6 | Mallory | Engineering | 78000 | PWI | t | 4564564567 7 | Niaj | HR | 62000 | PGHYD | f | 5675675678 8 | Olivia | Finance | 82000 | PGHYD | t | 6786786789 9 | Peggy | Engineering | 80000 | PWI | f | 7897897890 10 | Trent | Marketing | 71000 | PGHYD | t | 8908908901 1 | Frank | Engineering | 1000 | PWI | f | 8888888888 (10 rows) pghyd_db=>
Handy PostgreSQL Commands for Security and Privilege Management
List users
\du
Check Role Privileges
SELECT
r.rolname,
r.rolsuper,
r.rolinherit,
r.rolcreaterole,
r.rolcreatedb,
r.rolcanlogin,
r.rolreplication
FROM
pg_roles r;Check Table-Level Privileges
SELECT
grantee,
table_schema,
table_name,
privilege_type
FROM
information_schema.role_table_grants
WHERE
table_name = 'pghyd_employees';
Check Column-Level Privileges
SELECT
grantee,
table_schema,
table_name,
column_name,
privilege_type
FROM
information_schema.column_privileges
WHERE
table_name = 'pghyd_employees';
Check Row-Level Security (RLS) Policies
SELECT
*
FROM
pg_policies;
Check Default Privileges
SELECT
defaclrole::regrole,
defaclnamespace::regnamespace,
defaclobjtype,
defaclacl
FROM
pg_default_acl
WHERE
defaclrole = 'pghyd_core'::regrole;
Check Database-Level Privileges
SELECT
grantee,
privilege_type
FROM
information_schema.role_usage_grants
WHERE
table_catalog = 'pghyd_db'; -- Replace with your database name
Check Schema-Level Privileges
SELECT
grantee,
schema_name,
privilege_type
FROM
information_schema.role_schema_grants;
Conclusion
In this blog, we explored the implementation of a comprehensive database security model for the Pghyd organization, focusing on user roles, Row-Level Security (RLS), and data privacy.
As we move forward, securing data in transit becomes equally critical. In the next blog post, we will discuss how to implement Secure Sockets Layer (SSL) to protect data during transmission, ensuring that sensitive information remains confidential and secure across networks. Stay tuned!
