/aurora-dsql

Access Control & Role-Based Permissions

ALWAYS prefer scoped database roles over the admin role. The admin role should ONLY be used for initial cluster setup, creating roles, and granting permissions. Applications and services MUST connect using scoped-down database roles with dsql:DbConnect.

Scoped Roles Over Admin

• ALWAYS use scoped database roles for application connections and routine operations
• MUST create purpose-specific database roles for each application component
• MUST place user-sensitive data (PII, credentials) in a dedicated schema — NOT public
• MUST grant only the minimum permissions each role requires
• MUST create an IAM role with dsql:DbConnect for each database role
• SHOULD audit role mappings regularly: SELECT * FROM sys.iam_pg_role_mappings;

Setting Up Scoped Roles

Connect as admin (the only time admin should be used):

-- 1. Create scoped database roles
CREATE ROLE app_readonly WITH LOGIN;
CREATE ROLE app_readwrite WITH LOGIN;
CREATE ROLE user_service WITH LOGIN;

-- 2. Map each to an IAM role (each IAM role needs dsql:DbConnect permission)
AWS IAM GRANT app_readonly TO 'arn:aws:iam::123456789012:role/AppReadOnlyRole';
AWS IAM GRANT app_readwrite TO 'arn:aws:iam::123456789012:role/AppReadWriteRole';
AWS IAM GRANT user_service TO 'arn:aws:iam::123456789012:role/UserServiceRole';

-- 3. Create a dedicated schema for sensitive data
CREATE SCHEMA users_schema;

-- 4. Grant scoped permissions
GRANT USAGE ON SCHEMA public TO app_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readonly;

GRANT USAGE ON SCHEMA public TO app_readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_readwrite;

GRANT USAGE ON SCHEMA users_schema TO user_service;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA users_schema TO user_service;
GRANT CREATE ON SCHEMA users_schema TO user_service;

-- 5. Apply the same grants to FUTURE tables in users_schema (otherwise tables created
-- after this block won't be reachable by user_service even though it has CREATE on the schema).
ALTER DEFAULT PRIVILEGES IN SCHEMA users_schema
  GRANT SELECT, INSERT, UPDATE ON TABLES TO user_service;

Tip: GRANT … ON ALL TABLES IN SCHEMA only covers tables that exist at GRANT time. ALTER DEFAULT PRIVILEGES is required for any role that will read/write tables created later. Skip the ALTER DEFAULT PRIVILEGES step only when the same role that has CREATE is also the sole writer (it owns its tables and can use them without explicit grants).

IAM Role Requirements

Each scoped database role requires a corresponding IAM role with dsql:DbConnect:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "dsql:DbConnect",
      "Resource": "arn:aws:dsql:us-east-1:123456789012:cluster/<cluster-id>",
      "Condition": {
        "StringEquals": {
          "aws:ResourceTag/Environment": "development"
        }
      }
    }
  ]
}

Note: Scope the Resource ARN to a specific region, account, and cluster ID. Avoid using wildcards (*:*:cluster/*) which grant access across all regions, accounts, and clusters. Add condition keys such as aws:ResourceTag to further restrict access.

Reserve dsql:DbConnectAdmin strictly for administrative IAM identities:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "dsql:DbConnectAdmin",
      "Resource": "arn:aws:dsql:us-east-1:123456789012:cluster/<cluster-id>",
      "Condition": {
        "StringEquals": {
          "aws:ResourceTag/Environment": "development"
        }
      }
    }
  ]
}

Schema Separation for Sensitive Data

• MUST place user PII, credentials, and tokens in a dedicated schema (e.g., users_schema)
• MUST restrict sensitive schema access to only the roles that need it
• SHOULD name schemas descriptively: users_schema, billing_schema, audit_schema
• SHOULD use public only for non-sensitive, shared application data

-- Sensitive data: dedicated schema
CREATE TABLE users_schema.profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(255),
  phone VARCHAR(50)
);

-- Non-sensitive data: public schema
CREATE TABLE public.products (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id VARCHAR(255) NOT NULL,
  name VARCHAR(255) NOT NULL,
  category VARCHAR(100)
);

Connecting as a Scoped Role

Applications generate tokens with generate-db-connect-auth-token (NOT the admin variant):

# Application connection — uses DbConnect
PGPASSWORD="$(aws dsql generate-db-connect-auth-token \
  --hostname ${CLUSTER_ENDPOINT} \
  --region ${REGION})" \
psql -h ${CLUSTER_ENDPOINT} -U app_readwrite -d postgres

Set the search path to the correct schema after connecting:

SET search_path TO users_schema, public;

Role Design Patterns

Revoking Access

-- Revoke database permissions
REVOKE ALL ON ALL TABLES IN SCHEMA users_schema FROM app_readonly;
REVOKE USAGE ON SCHEMA users_schema FROM app_readonly;

-- Revoke IAM mapping
AWS IAM REVOKE app_readonly FROM 'arn:aws:iam::123456789012:role/AppReadOnlyRole';

References

• Using Database and IAM Roles
• PostgreSQL GRANT
• PostgreSQL Privileges