Column-Level Security

Column-level security enables precise control over database access by restricting user permissions to specific columns within tables. This granular security approach allows organizations to protect sensitive data fields while providing necessary access to non-sensitive information.

Understanding Column-Level Access Control

Security Model Overview

Column-level permissions provide:

Field-Specific Access: Control access to individual table columns
Sensitive Data Protection: Hide confidential fields like salaries, SSNs, or personal information
Compliance Support: Meet regulatory requirements for data access restrictions
Principle of Least Privilege: Grant users access only to data they need for their job functions

Supported Operations

Operation	Description	Use Cases
SELECT	Controls data read access	Protecting sensitive fields in queries and reports
UPDATE	Controls data modification access	Preventing unauthorized changes to critical data

Important Notes:

INSERT and DELETE operations are always controlled by table-level privileges
Column-level permissions apply to INSERT ON CONFLICT and MERGE operations
COPY operations respect column-level restrictions

Object Types Supported

Column-level permissions work with:

Tables: Standard database tables
Views: Both simple and complex views
Materialized Views: Computed and stored query results

Implementing Column-Level Security

Basic Permission Management

Granting Column-Level Access:

-- Grant SELECT permission on specific columns
GRANT SELECT (column1, column2, column3) 
ON table_name 
TO user_or_role;
 
-- Grant UPDATE permission on specific columns
GRANT UPDATE (column1, column2) 
ON table_name 
TO user_or_role;

Revoking Column-Level Access:

-- Revoke SELECT permission on specific columns
REVOKE SELECT (column1, column2) 
ON table_name 
FROM user_or_role;
 
-- Revoke UPDATE permission on specific columns
REVOKE UPDATE (column1) 
ON table_name 
FROM user_or_role;

Practical Implementation Example

Step 1: Create Employee Table

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    department VARCHAR(20),
    salary NUMERIC(10,2),
    ssn VARCHAR(11),
    hire_date DATE,
    performance_rating INTEGER
);

Step 2: Insert Sample Data

INSERT INTO employees (name, email, department, salary, ssn, hire_date, performance_rating)
VALUES
    ('Alice Johnson', 'alice@company.com', 'Engineering', 95000, '123-45-6789', '2020-01-15', 4),
    ('Bob Smith', 'bob@company.com', 'Marketing', 75000, '987-65-4321', '2019-03-22', 3),
    ('Carol Davis', 'carol@company.com', 'Engineering', 105000, '456-78-9123', '2018-07-10', 5);

Step 3: Create User Roles

-- Create roles for different access levels
CREATE ROLE hr_manager;
CREATE ROLE department_head;  
CREATE ROLE regular_employee;
CREATE ROLE external_auditor;

Step 4: Configure Column-Level Permissions

-- HR managers can see all employee data including sensitive fields
GRANT SELECT ON employees TO hr_manager;
GRANT UPDATE (department, salary, performance_rating) ON employees TO hr_manager;
 
-- Department heads can see most data but not salary details
GRANT SELECT (id, name, email, department, hire_date, performance_rating) 
ON employees TO department_head;
GRANT UPDATE (performance_rating) ON employees TO department_head;
 
-- Regular employees can only see basic contact information
GRANT SELECT (id, name, email, department) 
ON employees TO regular_employee;
 
-- External auditors need specific data for compliance
GRANT SELECT (id, name, department, hire_date) 
ON employees TO external_auditor;

Step 5: Create and Test User Accounts

-- Create users and assign roles
CREATE USER "hr_director@company.com";
GRANT hr_manager TO "hr_director@company.com";
 
CREATE USER "eng_manager@company.com";
GRANT department_head TO "eng_manager@company.com";
 
CREATE USER "intern@company.com";
GRANT regular_employee TO "intern@company.com";

Testing Column-Level Access

Testing with Different Users:

-- Test as regular employee (limited access)
SET ROLE "intern@company.com";
 
-- This works - allowed columns
SELECT id, name, email, department FROM employees;
 
-- This fails - salary column not permitted
SELECT name, salary FROM employees;
-- ERROR: permission denied for table employees
 
-- Test as department head (broader access)
SET ROLE "eng_manager@company.com";
 
-- This works - can see performance data
SELECT name, department, performance_rating FROM employees 
WHERE department = 'Engineering';
 
-- This fails - salary still restricted
SELECT name, salary FROM employees;
-- ERROR: permission denied for table employees

Advanced Column Security Patterns

Role-Based Column Access

Departmental Data Segregation:

-- Create department-specific views with column restrictions
CREATE VIEW engineering_employees AS
SELECT id, name, email, hire_date, performance_rating
FROM employees 
WHERE department = 'Engineering';
 
CREATE VIEW marketing_employees AS  
SELECT id, name, email, hire_date
FROM employees
WHERE department = 'Marketing';
 
-- Grant access to department-specific views
GRANT SELECT ON engineering_employees TO eng_team_role;
GRANT SELECT ON marketing_employees TO marketing_team_role;

Progressive Access Levels:

-- Create hierarchical roles with increasing access
CREATE ROLE employee_basic;
CREATE ROLE employee_standard;  
CREATE ROLE employee_manager;
CREATE ROLE employee_executive;
 
-- Basic level: contact information only
GRANT SELECT (id, name, email, department) 
ON employees TO employee_basic;
 
-- Standard level: includes hire date and job details
GRANT employee_basic TO employee_standard;
GRANT SELECT (hire_date) 
ON employees TO employee_standard;
 
-- Manager level: includes performance data
GRANT employee_standard TO employee_manager;
GRANT SELECT (performance_rating) 
ON employees TO employee_manager;
GRANT UPDATE (performance_rating) 
ON employees TO employee_manager;
 
-- Executive level: full access including compensation
GRANT employee_manager TO employee_executive;
GRANT SELECT (salary) 
ON employees TO employee_executive;
GRANT UPDATE (salary, department) 
ON employees TO employee_executive;

Security Best Practices

Permission Design Principles

Least Privilege Access:

Start with minimal necessary permissions
Add permissions only when business requirements justify them
Regularly review and audit column-level permissions
Document business justification for sensitive data access

Layered Security Approach:

Combine column-level security with row-level security when appropriate
Use table-level permissions as the base security layer
Implement application-level controls as additional security
Consider data masking or encryption for highly sensitive fields

Common Security Patterns

Sensitive Data Protection:

-- Example: Protecting financial and personal data
CREATE TABLE customer_data (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(15),
    credit_score INTEGER,
    annual_income NUMERIC(12,2),
    ssn VARCHAR(11),
    account_balance NUMERIC(12,2)
);
 
-- Customer service can see contact info but not financial data
CREATE ROLE customer_service;
GRANT SELECT (customer_id, name, email, phone) 
ON customer_data TO customer_service;
 
-- Financial analysts can see financial data but not personal identifiers
CREATE ROLE financial_analyst;
GRANT SELECT (customer_id, credit_score, annual_income, account_balance) 
ON customer_data TO financial_analyst;
 
-- Compliance officers can see everything for audit purposes
CREATE ROLE compliance_officer;
GRANT SELECT ON customer_data TO compliance_officer;

Troubleshooting and Common Issues

Permission Precedence Rules

Understanding Permission Conflicts:

Table-level permissions override column-level restrictions
If a user has table-level SELECT, they can access all columns
Column-level permissions are additive within the same operation type
DENY permissions (through role revocation) take precedence over GRANT permissions

Example of Permission Precedence:

-- This grants access to ALL columns, overriding column restrictions
GRANT SELECT ON employees TO user_name;
 
-- Even if you later try to restrict columns, the table grant takes precedence
GRANT SELECT (name, email) ON employees TO user_name;  -- This has no effect
 
-- To implement column restrictions, ensure no table-level grants exist
REVOKE SELECT ON employees FROM user_name;
GRANT SELECT (name, email) ON employees TO user_name;  -- Now this works

Common Error Messages

Permission Denied Errors:

-- Error: permission denied for table employees
-- Cause: User lacks any SELECT permission on the table
-- Solution: Grant appropriate column-level SELECT permissions
 
-- Error: column "salary" must appear in the GROUP BY clause or be used in an aggregate function
-- Cause: User has column access but query structure violates SQL rules
-- Solution: Modify query structure or grant additional column permissions

Performance Considerations

Optimizing Column-Level Security:

Column-level permissions have minimal performance impact
Avoid overly complex permission hierarchies
Use views for complex column filtering logic
Consider indexing strategies for filtered column access

Monitoring Permission Usage:

-- Query to check current column-level permissions
SELECT 
    table_name,
    column_name,
    privilege_type,
    grantee
FROM information_schema.column_privileges 
WHERE table_schema = 'public'
ORDER BY table_name, column_name, grantee;

Integration with Other Security Features

Combining with Row-Level Security

Multi-Dimensional Access Control:

-- Enable both row and column level security
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
 
-- Row-level policy: users can only see their own department
CREATE POLICY department_isolation ON employees
    USING (department = (
        SELECT department FROM employees WHERE name = current_user
    ));
 
-- Column-level permissions: restrict salary information
GRANT SELECT (id, name, email, department, hire_date) 
ON employees TO regular_employee;
 
-- Result: Users see only their department colleagues' non-sensitive data

Application Integration

Coordinating with Application Security:

Document which columns are protected at the database level
Ensure application code handles permission denied errors gracefully
Implement consistent security policies across application and database layers
Test application behavior with different user permission levels

Column-level security provides powerful, granular data protection capabilities that integrate seamlessly with existing PostgreSQL security features while maintaining performance and operational simplicity.

Column-Level Security

On this page