When agents act on their own, governance has to live in the data layer

Learn how to implement data governance controls in PostgreSQL to enforce AI agent behavior at the data layer, including role-based access control, row-level security, and audit logging.

Introduction

In this tutorial, you'll learn how to set up a basic data governance framework using PostgreSQL, which is a core part of the approach described in the article. This framework ensures that AI agents (or any data users) can only access and modify data according to defined rules, enforcing governance at the data layer. We'll focus on implementing role-based access control (RBAC), row-level security, and audit logging to create a secure environment where agents are governed by policies that are enforced in real-time, not just after the fact.

Prerequisites

To follow this tutorial, you'll need:

PostgreSQL installed on your system (version 12 or higher recommended)
Basic understanding of SQL and database concepts
A terminal or SQL client (like psql or pgAdmin)
Some familiarity with how databases manage users and permissions

Step-by-Step Instructions

Step 1: Create a Test Database and User

First, we'll set up a test environment to simulate how an AI agent would interact with data.

Why: We need a clean space to experiment with access control and audit logging. This simulates a real-world environment where data is managed securely.

CREATE DATABASE ai_governance_demo;
\c ai_governance_demo;

Now, create a user for the AI agent to simulate its identity:

CREATE USER ai_agent WITH PASSWORD 'secure_password';

Step 2: Create a Sample Table

We'll create a simple table that will be used to demonstrate access control.

Why: This table represents the kind of data an AI agent might work with. We'll later apply security policies to it.

CREATE TABLE employee_data (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary INT,
  sensitive_info TEXT
);

Insert some sample data:

INSERT INTO employee_data (name, department, salary, sensitive_info) VALUES
('John Doe', 'Engineering', 75000, 'Bank account details'),
('Jane Smith', 'Marketing', 65000, 'Customer list'),
('Bob Johnson', 'HR', 60000, 'Personal records');

Step 3: Implement Role-Based Access Control

We'll create roles for different access levels and assign them to users.

Why: This simulates how different agents or users might have different permissions based on their roles. RBAC is one of the foundational controls mentioned in the article.

CREATE ROLE read_only;
CREATE ROLE data_analyst;
CREATE ROLE hr_manager;

GRANT CONNECT ON DATABASE ai_governance_demo TO read_only;
GRANT USAGE ON SCHEMA public TO read_only;
GRANT SELECT ON TABLE employee_data TO read_only;

GRANT CONNECT ON DATABASE ai_governance_demo TO data_analyst;
GRANT USAGE ON SCHEMA public TO data_analyst;
GRANT SELECT, INSERT, UPDATE ON TABLE employee_data TO data_analyst;

GRANT CONNECT ON DATABASE ai_governance_demo TO hr_manager;
GRANT USAGE ON SCHEMA public TO hr_manager;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employee_data TO hr_manager;

GRANT read_only TO ai_agent;

Step 4: Enable Row-Level Security (RLS)

Now we'll enable row-level security on the table to restrict access to certain rows.

Why: Row-level security ensures that even if an agent has access to the table, it can only see or modify rows that it's authorized to access. This is critical for protecting sensitive data.

ALTER TABLE employee_data ENABLE ROW LEVEL SECURITY;

CREATE POLICY emp_policy ON employee_data
  FOR ALL
  TO ai_agent
  USING (department = 'Engineering');

This policy means that the AI agent can only access rows where the department is 'Engineering'.

Step 5: Set Up Audit Logging

Next, we'll set up a simple audit log to track who accessed what data.

Why: Audit trails are essential for accountability and compliance. They allow you to reconstruct what happened when an agent accessed or modified data.

CREATE TABLE audit_log (
  id SERIAL PRIMARY KEY,
  user_name VARCHAR(100),
  action VARCHAR(50),
  table_name VARCHAR(100),
  timestamp TIMESTAMP DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (user_name, action, table_name)
  VALUES (CURRENT_USER, TG_OP, TG_TABLE_NAME);
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_trigger
  AFTER INSERT OR UPDATE OR DELETE ON employee_data
  FOR EACH ROW EXECUTE FUNCTION audit_trigger();

Step 6: Test the Governance Controls

Now let's test our setup by having the AI agent perform some operations.

Why: Testing helps us confirm that our governance controls are working as intended. We'll simulate what happens when an agent tries to access data it shouldn't be able to.

\c ai_governance_demo ai_agent

-- This should work because the agent has access to Engineering department rows
SELECT * FROM employee_data;

-- This should fail because the agent doesn't have access to rows outside the Engineering department
UPDATE employee_data SET salary = 80000 WHERE department = 'Marketing';

-- Check audit log
SELECT * FROM audit_log;

Summary

In this tutorial, we've created a basic but functional data governance framework using PostgreSQL. We've implemented role-based access control, row-level security, and audit logging. These are the foundational elements of the data-layer governance approach described in the article. The key idea is that governance is enforced at the point where data is accessed, not just after the fact. This ensures that even autonomous AI agents are bound by rules, and all actions are tracked for accountability.

While this is a simplified example, it demonstrates the core concepts that enterprises can build upon to govern AI agents at scale. The real-world implementation would include more complex policies, integration with identity management systems, and more comprehensive logging, but this gives you a starting point to understand how to apply these principles in practice.