Role-Based Access Control

-- Create roles
CREATE ROLE readonly;
CREATE ROLE developer;
CREATE ROLE admin WITH ADMIN OPTION;

-- Grant database privileges
GRANT CONNECT ON DATABASE mydb TO readonly;
GRANT CREATE ON DATABASE mydb TO developer;
GRANT ALL PRIVILEGES ON DATABASE mydb TO admin;

-- Grant table privileges
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO developer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;

-- Grant column-specific privileges
GRANT SELECT (id, name, email) ON users TO readonly;
GRANT SELECT ON users TO developer;  -- All columns
GRANT UPDATE (status) ON users TO developer;

-- Grant on specific objects
GRANT EXECUTE ON FUNCTION calculate_balance TO developer;
GRANT USAGE ON SEQUENCE user_id_seq TO developer;

-- Assign roles to users
GRANT readonly TO alice;
GRANT developer TO bob;
GRANT admin TO charlie;

-- Role inheritance
CREATE ROLE poweruser;
GRANT readonly TO poweruser;
GRANT poweruser TO alice;  -- alice gets readonly privileges

-- Enable row-level security
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create policy: Users see only their own orders
CREATE POLICY user_orders ON orders
  FOR SELECT
  USING (user_id = current_user_id());

-- Create policy: Managers see team orders
CREATE POLICY manager_orders ON orders
  FOR SELECT
  USING (
    department_id IN (
      SELECT department_id 
      FROM employees 
      WHERE user_id = current_user_id()
      AND role = 'manager'
    )
  );

-- Create policy: Admins see everything
CREATE POLICY admin_all_orders ON orders
  FOR ALL
  USING (current_user_has_role('admin'));

-- Multiple policies with different operations
CREATE POLICY user_insert_own_orders ON orders
  FOR INSERT
  WITH CHECK (user_id = current_user_id());

CREATE POLICY user_update_own_orders ON orders
  FOR UPDATE
  USING (user_id = current_user_id())
  WITH CHECK (user_id = current_user_id());

-- Disable RLS for specific role
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
GRANT BYPASSRLS TO admin;

-- Check active policies
SELECT * FROM pg_policies WHERE tablename = 'orders';

-- Create masking policy
CREATE MASKING POLICY mask_email AS (email TEXT) 
RETURNS TEXT AS $$
  SELECT CASE 
    WHEN current_user_has_role('admin') THEN email
    WHEN current_user_has_role('support') THEN 
      SUBSTRING(email, 1, 3) || '***@' || SPLIT_PART(email, '@', 2)
    ELSE '***@***.com'
  END
$$ LANGUAGE SQL;

-- Apply masking policy
ALTER TABLE users 
  ALTER COLUMN email 
  SET MASKING POLICY mask_email;

-- Create policy for credit card numbers
CREATE MASKING POLICY mask_credit_card AS (cc_number TEXT)
RETURNS TEXT AS $$
  SELECT CASE
    WHEN current_user_has_role('finance') THEN cc_number
    ELSE '****-****-****-' || RIGHT(cc_number, 4)
  END
$$ LANGUAGE SQL;

ALTER TABLE payments
  ALTER COLUMN credit_card
  SET MASKING POLICY mask_credit_card;

-- Dynamic masking based on context
CREATE MASKING POLICY dynamic_salary AS (salary NUMERIC)
RETURNS NUMERIC AS $$
  SELECT CASE
    WHEN current_user_id() = employee_id THEN salary  -- Own salary
    WHEN current_user_has_role('hr') THEN salary      -- HR sees all
    WHEN current_user_is_manager_of(employee_id) THEN salary  -- Manager sees team
    ELSE NULL  -- Others see nothing
  END
$$ LANGUAGE SQL;

-- Create attribute-based policy
CREATE POLICY regional_access ON customers
  FOR SELECT
  USING (
    region = current_setting('app.user_region')
    OR current_user_has_role('global_viewer')
  );

-- Set user attributes in session
SET app.user_region = 'us-west';
SET app.user_department = 'sales';
SET app.user_clearance_level = '3';

-- Policy using multiple attributes
CREATE POLICY document_access ON documents
  FOR SELECT
  USING (
    (classification_level <= current_setting('app.user_clearance_level')::int)
    AND
    (department = current_setting('app.user_department')
     OR is_public = true)
  );

-- Time-based access
CREATE POLICY business_hours_access ON sensitive_data
  FOR SELECT
  USING (
    current_user_has_role('admin')
    OR
    (EXTRACT(HOUR FROM NOW()) BETWEEN 9 AND 17
     AND EXTRACT(DOW FROM NOW()) BETWEEN 1 AND 5)
  );

-- IP-based access
CREATE POLICY office_network_access ON confidential
  FOR ALL
  USING (
    current_user_has_role('remote_admin')
    OR
    inet_client_addr() <<= '10.0.0.0/8'::inet
  );