06_role_based_access.sql

-- PostgreSQL Data Anonymization in AWS RDS
-- Role-based access control for anonymized data

-- Create roles for different access levels
CREATE ROLE analyst;
CREATE ROLE developer;
CREATE ROLE researcher;
CREATE ROLE admin;

-- Grant appropriate permissions
GRANT SELECT ON analyst_users TO analyst;
GRANT SELECT ON developer_users TO developer;
GRANT SELECT ON research_patients TO researcher;
GRANT SELECT ON users, patients TO admin;

-- Create a function to check if a user has access to a specific view
CREATE OR REPLACE FUNCTION anon.has_view_access(view_name text)
RETURNS boolean AS
$$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM information_schema.role_table_grants
    WHERE table_name = view_name
      AND grantee = current_user
  );
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

-- Create a function to get the appropriate view for the current user
CREATE OR REPLACE FUNCTION anon.get_users_view()
RETURNS text AS
$$
BEGIN
  IF anon.has_view_access('users') THEN
    RETURN 'users';
  ELSIF anon.has_view_access('analyst_users') THEN
    RETURN 'analyst_users';
  ELSIF anon.has_view_access('developer_users') THEN
    RETURN 'developer_users';
  ELSIF anon.has_view_access('masked_users') THEN
    RETURN 'masked_users';
  ELSE
    RETURN NULL;
  END IF;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

-- Create a function to query the appropriate view based on user role
CREATE OR REPLACE FUNCTION anon.query_users()
RETURNS SETOF record AS
$$
DECLARE
  view_name text;
  query text;
  rec record;
BEGIN
  view_name := anon.get_users_view();
  
  IF view_name IS NULL THEN
    RAISE EXCEPTION 'You do not have access to any users view';
  END IF;
  
  query := 'SELECT * FROM ' || view_name;
  
  FOR rec IN EXECUTE query LOOP
    RETURN NEXT rec;
  END LOOP;
  
  RETURN;
END;
$$
LANGUAGE plpgsql SECURITY DEFINER;

-- Example usage:
-- SELECT * FROM anon.query_users() AS t(id int, email text, first_name text, last_name text, ...);

-- Note: In AWS RDS, you would typically create these roles and then map them to database users
-- using the CREATE USER command and GRANT statements.