Postgres

This guide walks you through configuring Alien Giraffe to connect to PostgreSQL with enterprise-grade security, including read-only database users and column-level access controls.

Prerequisites

• PostgreSQL 12 or higher
• Database administrator access to create users and grant permissions

Setting Up a Read-Only PostgreSQL User

First, create a dedicated read-only user for Alien Giraffe. This ensures that even if security controls are bypassed, the database connection itself cannot modify data.

1. Create the Read-Only User

Connect to your PostgreSQL database as an administrator and run:

-- Create a new user for Alien Giraffe
CREATE USER alien_giraffe_reader WITH PASSWORD 'your-secure-password';

-- Grant connection privileges
GRANT CONNECT ON DATABASE your_database TO alien_giraffe_reader;

-- Grant schema usage (repeat for each schema you want to expose)
GRANT USAGE ON SCHEMA public TO alien_giraffe_reader;
GRANT USAGE ON SCHEMA analytics TO alien_giraffe_reader;

-- Grant SELECT privileges on specific tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO alien_giraffe_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO alien_giraffe_reader;

-- Ensure future tables are also accessible (optional)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO alien_giraffe_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
    GRANT SELECT ON TABLES TO alien_giraffe_reader;

2. Restrict Access to Sensitive Tables

For tables containing highly sensitive data, explicitly revoke access:

-- Revoke access to specific sensitive tables
REVOKE SELECT ON public.user_passwords FROM alien_giraffe_reader;
REVOKE SELECT ON public.api_keys FROM alien_giraffe_reader;
REVOKE SELECT ON public.payment_methods FROM alien_giraffe_reader;

3. Create Views for Partial Table Access

For tables where you need some columns but not others, create filtered views:

-- Create a view that excludes sensitive columns
CREATE VIEW public.users_safe AS
SELECT
    id,
    username,
    email,
    created_at,
    last_login,
    -- Exclude: ssn, date_of_birth, credit_score
FROM public.users;

-- Grant access to the view instead of the table
GRANT SELECT ON public.users_safe TO alien_giraffe_reader;
REVOKE SELECT ON public.users FROM alien_giraffe_reader;

Configuring the PostgreSQL Data Source

Add your PostgreSQL connection to the Alien Giraffe configuration file:

[[datasources.postgres.my_postgres_db]]
name = "production-analytics"
host = "postgres.company.com"
port = 5432
database = "analytics_db"
username = "alien_giraffe_reader"
password = "${POSTGRES_READONLY_PASSWORD}"  # Use environment variable

Column-Level Security Rules

Alien Giraffe provides powerful column-level security controls that work alongside your database permissions.

Global Column Restrictions

Define columns that should never be accessible across all tables:

[security.datasource.postgres.global]
# Columns that are completely blocked across all tables
blocked_columns = [
    "ssn",
    "social_security_number",
    "tax_id",
    "passport_number",
    "driver_license",
    "credit_card_number",
    "cvv",
    "pin"
]

# Columns that require masking when accessed
masked_columns = [
    "email",           # Shows as "j***@example.com"
    "phone_number",    # Shows as "***-***-1234"
    "ip_address",      # Shows as "192.168.*.*"
    "date_of_birth"    # Shows only year: "1985-**-**"
]

Table-Specific Rules

Configure security rules for specific tables:

[[security.datasource.postgres.tables]]
table = "public.users"
# Completely blocked columns for this table
blocked_columns = [
    "password_hash",
    "mfa_secret",
    "recovery_codes"
]
# Columns that return masked data
masked_columns = [
    "last_name",      # Shows as "Smith***"
    "home_address",   # Shows only city and state
    "salary"          # Shows range: "$70,000-$80,000"
]

[[security.datasource.postgres.tables]]
table = "public.transactions"
blocked_columns = [
    "internal_notes",
    "fraud_score"
]
# Custom masking rules
[security.datasource.postgres.tables.masking]
account_number = "PARTIAL"  # Shows last 4 digits only
amount = "RANGE"           # Shows ranges for amounts

Advanced Masking Configurations

Define custom masking strategies:

[security.datasource.postgres.masking_strategies]
# Email masking - show first letter and domain
[security.datasource.postgres.masking_strategies.email]
type = "regex"
pattern = "^(.).*@(.*)$"
replacement = "$1***@$2"

# Phone masking - show area code and last 4 digits
[security.datasource.postgres.masking_strategies.phone]
type = "regex"
pattern = "^(\d{3})-?\d{3}-?(\d{4})$"
replacement = "$1-***-$2"

# Financial data - show ranges
[security.datasource.postgres.masking_strategies.financial]
type = "range"
ranges = [
    { min = 0, max = 10000, display = "$0-$10K" },
    { min = 10001, max = 50000, display = "$10K-$50K" },
    { min = 50001, max = 100000, display = "$50K-$100K" },
    { min = 100001, max = null, display = "$100K+" }
]

# Date masking - show only year
[security.datasource.postgres.masking_strategies.date]
type = "date"
format = "YYYY-**-**"

Schema Definition with Security Annotations

When defining your schema, include security annotations:

{
  "$schema": "http://json-schema.org/draft-07/schema#",
  "title": "Analytics Datasource Definitions",
  "description": "Contains schema definitions for tables in the production-analytics datasource.",
  "definitions": {
    "customer": {
      "title": "Customer",
      "description": "A customer record from the production-analytics datasource.",
      "type": "object",
      "properties": {
        "id": {
          "type": "integer",
          "description": "The unique identifier for the customer.",
          "primary_key": true
        },
        "first_name": {
          "type": "string",
          "description": "The customer's first name."
        },
        "last_name": {
          "type": "string",
          "description": "The customer's last name.",
          "security": "masked"
        },
        "email": {
          "type": "string",
          "description": "The customer's email address.",
          "format": "email",
          "security": "masked",
          "masking_strategy": "email"
        },
        "ssn": {
          "type": "string",
          "description": "The customer's Social Security Number.",
          "security": "blocked"
        },
        "annual_income": {
          "type": "integer",
          "description": "The customer's annual income.",
          "security": "masked",
          "masking_strategy": "financial"
        },
        "created_at": {
          "type": "string",
          "description": "The timestamp when the customer record was created.",
          "format": "date-time"
        }
      },
      "required": ["id", "first_name", "last_name", "email", "created_at"]
    }
  }
}

Install Alien Giraffe

pip install alien-giraffe

Runtime Security Validation

Alien Giraffe enforces these rules at multiple levels:

1. Query Generation: The AI model is aware of blocked columns and won’t generate queries containing them
2. Query Validation: Queries are checked before execution to ensure no blocked columns are accessed
3. Result Processing: Data is masked according to rules before being returned to the client

Example of how this works in practice:

import alien_giraffe

# Initialize client
a10e = alien_giraffe.Client()
a10e.load("customer_analytics")

# This query will work (email will be masked)
customers = a10e.sql(
    "SELECT id, first_name, email FROM customers "
    "WHERE created_at > '2024-01-01'"
)
# Returns: [(1, "John", "j***@example.com"), ...]

# This query will be rejected
try:
    customers = a10e.sql("SELECT id, ssn FROM customers")
except alien_giraffe.SecurityError as e:
    print(f"Security violation: {e}")
    # Output: "Security violation: Access to column 'ssn' is blocked"

# Using DataFrame API with automatic security
df = a10e.df("customers")
# Blocked columns are automatically excluded
# Masked columns show redacted values
print(df.columns)
# ['id', 'first_name', 'last_name', 'email', 'annual_income', 'created_at']

Best Practices

1. Principle of The Least Privilege

• Create separate read-only users for different access levels
• Use PostgreSQL schemas to organize data by sensitivity
• Grant access only to necessary schemas and tables

2. Defense in Depth

• Database-level permissions are your first line of defense
• Alien Giraffe’s column rules provide additional protection
• Use both together for maximum security

3. Audit and Monitoring

Configure audit logging to track all data access:

[security.datasource.postgres.audit]
enabled = true
log_queries = true
log_blocked_attempts = true
include_user_context = true

4. Regular Security Reviews

• Periodically review which columns are being accessed
• Update masking rules based on new privacy requirements
• Remove access to unused tables and columns

Troubleshooting

Common Issues

1. Permission Denied Errors

   • Verify the read-only user has SELECT permissions
   • Check schema USAGE permissions
   • Ensure views are properly granted

2. Columns Not Being Masked

   • Verify column names match exactly (case-sensitive)
   • Check masking strategy configuration
   • Review table-specific rules override global rules

3. Blocked Columns Still Accessible

   • Ensure configuration is loaded correctly
   • Check for typos in column names
   • Verify no conflicting rules exist

Next Steps

• Learn about Multi-Source Data Access
• Explore Advanced Security Patterns
• Set up Compliance Reporting