Session-Scoped Database Access: Tenant Isolation Beyond WHERE Clauses

Most multi-tenant applications enforce tenant isolation with a WHERE tenant_id = ? clause. It works until a bug skips it. There’s a stronger pattern: session-scoped credentials that are mathematically incapable of accessing another tenant’s data. Let’s look at how this works across cloud providers and databases.

The Core Pattern: Credential-Based Isolation

The idea is simple. Instead of trusting your application code to filter data correctly, you hand each request a set of throw-away credentials that can only access one tenant’s data. The enforcement happens at the infrastructure layer, not the application layer.

Here’s what this looks like on AWS using STS (Security Token Service):

def query_orders(self, tenant_id, scoped_policy):
    sts = boto3.client('sts')

    # assume a role with an inline policy that restricts to this tenant
    tenant_credentials = sts.assume_role(
        RoleArn=os.environ.get('IDENTITY_ROLE'),
        RoleSessionName=tenant_id,
        Policy=scoped_policy,
        DurationSeconds=1000
    )

    # create a session with those short-lived credentials
    session = boto3.Session(
        aws_access_key_id=tenant_credentials['Credentials']['AccessKeyId'],
        aws_secret_access_key=tenant_credentials['Credentials']['SecretAccessKey'],
        aws_session_token=tenant_credentials['Credentials']['SessionToken']
    )

    # this client can ONLY access tenant's data
    ddb = session.client('dynamodb')

The key insight is the Policy parameter. This is an inline session policy attached at the moment of assuming the role. It can only restrict permissions, never expand them. So even if the base role has full DynamoDB access, the scoped session can only reach one tenant’s rows.

A scoped policy for DynamoDB might look like:

{
  "Statement": [{
    "Effect": "Allow",
    "Action": ["dynamodb:GetItem", "dynamodb:Query"],
    "Resource": "arn:aws:dynamodb:us-east-1:123:table/Orders",
    "Condition": {
      "ForAllValues:StringEquals": {
        "dynamodb:LeadingKeys": ["tenant-abc-*"]
      }
    }
  }]
}

The credentials expire after DurationSeconds, the RoleSessionName shows up in CloudTrail for auditability, and a bug in your application code cannot bypass this. The credentials are the enforcement boundary.

Where This Pattern Shines (and Where It Doesn’t)

Session scoping is only as strong as how deeply IAM is integrated into the resource’s access control. Services built from the ground up on IAM get deep integration. Services that predate the cloud get a shallower layer.

For S3, scoping to a tenant prefix is straightforward:

{
  "Resource": "arn:aws:s3:::my-bucket/tenant-abc/*"
}

The credentials literally cannot read or write outside that prefix.

For RDS, however, the story splits into two layers. IAM can control which instance you connect to, but once you’re inside Postgres or MySQL, tenant isolation is the database engine’s job.

Relational Databases: The Two-Layer Problem

AWS supports IAM database authentication for RDS. You can replace static passwords with short-lived tokens:

rds_client = boto3.client('rds')

token = rds_client.generate_db_auth_token(
    DBHostname=db_host,
    Port=5432,
    DBUsername='app_user'
)

conn = psycopg2.connect(
    host=db_host,
    user='app_user',
    password=token,       # 15-minute expiry
    database='mydb',
    sslmode='require'     # mandatory for IAM auth
)

This gets you to the door. Inside the database, you need a second isolation mechanism.

Pure Postgres Tenant Isolation

Regardless of cloud provider, Postgres gives you three strategies. Each trades isolation strength for operational scalability.

Strategy 1: Separate Database Per Tenant

The strongest boundary. Each tenant is a fully separate Postgres database:

postgres server
  |- tenant_abc_db
  |- tenant_xyz_db
  +- tenant_foo_db

Complete isolation, easy per-tenant backup and restore, no RLS complexity. But connection pooling becomes painful, schema migrations must run N times, and it gets expensive at scale. Best for low tenant counts with strict isolation requirements.

Strategy 2: Separate Schema Per Tenant

One database, but each tenant gets their own Postgres schema:

CREATE SCHEMA tenant_abc;
CREATE TABLE tenant_abc.orders (...);

CREATE SCHEMA tenant_xyz;
CREATE TABLE tenant_xyz.orders (...);

-- restrict user to their schema
ALTER ROLE tenant_abc_user SET search_path = tenant_abc;

Still strong isolation, shared connection pool, one database to manage. Migrations still need to run per schema, and it gets unwieldy past a few hundred tenants.

Strategy 3: Shared Tables + Row Level Security

The most scalable approach. One table for all tenants, Postgres enforces row-level isolation:

CREATE TABLE orders (
    id UUID PRIMARY KEY,
    tenant_id TEXT NOT NULL,
    status TEXT,
    amount NUMERIC
);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant'));

Then in your application, set the tenant context per request:

cursor.execute(
    "SET LOCAL app.current_tenant = %s", (tenant_id,)
)
# every query on this connection is now filtered automatically
cursor.execute("SELECT * FROM orders")  # only sees this tenant's rows

No WHERE clause needed. Postgres enforces it at the engine level.

The Connection Pool Footgun

This is where most RLS bugs happen in practice. Connection pools (PgBouncer, etc.) reuse connections across requests. If you SET a tenant variable on a pooled connection and don’t reset it, the next request inherits the previous tenant’s context.

The critical distinction:

The safe pattern is always SET LOCAL inside a transaction:

with conn.transaction():
    cursor.execute("SET LOCAL app.current_tenant = %s", (tenant_id,))
    cursor.execute("SELECT * FROM orders")
    # tenant variable cleared automatically when transaction ends

If using PgBouncer, configure it in transaction pooling mode so each transaction gets a fresh connection context and session variables don’t leak.

Comparison

For most SaaS products at scale, RLS with shared tables is the pragmatic choice.

Cross-Cloud: GCP and Azure

Google Cloud

GCP’s equivalent to STS is service account impersonation:

from google.auth import impersonated_credentials

target_credentials = impersonated_credentials.Credentials(
    source_credentials=base_credentials,
    target_principal=f'tenant-abc@my-project.iam.gserviceaccount.com',
    target_scopes=['https://www.googleapis.com/auth/cloud-platform'],
    lifetime=3600
)

GCP has IAM Conditions for scoping, which map roughly to AWS session policies:

condition:
  expression: >
    resource.name.startsWith(
      'projects/_/buckets/my-bucket/objects/tenant-abc/'
    )

Works well for GCS. For relational databases (Cloud SQL with Postgres), same RLS patterns apply. One advantage: Cloud Spanner has fine-grained access control at the table and column level natively.

Azure

Azure uses Managed Identities / Service Principals with Azure AD tokens. The key gap: Azure has no equivalent to inline session policies. You cannot dynamically narrow permissions at call time.

This forces you toward:

• A separate Managed Identity per tenant (operationally expensive)
• Shared Access Signatures (SAS) for Blob Storage (token-based, path-scoped, but only for storage)
• Application-level enforcement for everything else

For Azure SQL, tenant isolation uses SQL Server’s Row-Level Security, which works similarly to Postgres RLS:

CREATE SECURITY POLICY tenant_filter
ADD FILTER PREDICATE dbo.fn_tenant_predicate(tenant_id)
ON dbo.orders;

The Cross-Cloud Summary

AWS has the most mature implementation, largely because DynamoDB was designed with IAM as its only auth mechanism. GCP is close but less dynamic. Azure has the biggest gap due to the absence of inline session policies.

The Bottom Line

The strongest tenant isolation doesn’t rely on your application remembering to add a WHERE clause. It uses credentials that physically cannot access another tenant’s data.

For IAM-native services like DynamoDB and S3, session-scoped credentials give you that guarantee. For relational databases, you need a second layer – Postgres RLS is the practical choice, but watch the connection pooling interaction. And across cloud providers, the depth of this pattern depends entirely on how deeply IAM is woven into each service’s access control.

The core principle: push tenant isolation as close to the enforcement boundary as possible. Application-level filtering is a convenience. Infrastructure-level filtering is a guarantee.