Least Privilege: DynamoDB vs. Aurora DSQL

One read-only Lambda, two databases, and two places to look when access is denied.

A read-only Lambda needs a different permission setup in DynamoDB and Aurora DSQL.

If a Lambda can connect to Aurora DSQL, can it delete a row?

I started asking this because I already knew how I’d give a Lambda read-only access to DynamoDB. If the function only calls GetItem, I give its execution role dynamodb:GetItem on the table it uses. I can read the IAM policy and see exactly which operation the function is allowed to perform.

Then I looked at the IAM policy for a Lambda that reads from Aurora DSQL. The action was dsql:DbConnect.

Where was the part that said the Lambda could only read?

DynamoDB puts the operation in the IAM policy

Imagine a Lambda that fetches a product by its ID. It uses GetItemCommand to read one item from a DynamoDB table called Products.

Here’s the CDK policy statement I’d attach to the Lambda’s execution role.

new PolicyStatement({
  actions: ["dynamodb:GetItem"],
  resources: [productsTable.tableArn],
})

The action tells me what the Lambda can do. The resource tells me where it can do it. Here, that means one DynamoDB operation on one table.

DynamoDB evaluates each request using the Lambda’s AWS identity. If I change the handler to call Query, that request needs dynamodb:Query. GetItem and Query both read data, but IAM treats them as separate actions. With the policy above as the only grant, the Query request is denied. AWS DynamoDB IAM policy examples

The Lambda’s IAM policy specifies both the DynamoDB action and the table it applies to.

Aurora DSQL separates login from SQL privileges

Now imagine the same Lambda reading from shop.products in Aurora DSQL. The policy attached to its execution role looks like this.

new PolicyStatement({
  actions: ["dsql:DbConnect"],
  resources: [clusterArn],
})

This gives the Lambda’s execution role permission to connect to one cluster. At runtime, the DSQL connector uses the role’s temporary AWS credentials to generate a login token. The Lambda also supplies a database username, product_reader in this example.

When the connection opens, DSQL checks that the IAM identity can connect to the cluster and that it’s mapped to the requested database role. AWS Aurora DSQL authentication and authorization

Get Anuradha Ranasinghe’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

An administrator sets up that mapping and the database privileges. This example assumes the IAM role, schema, and table already exist. The ARN is illustrative.

CREATE ROLE product_reader WITH LOGIN;

AWS IAM GRANT product_reader
  TO 'arn:aws:iam::123456789012:role/ProductReaderLambdaRole';
GRANT USAGE ON SCHEMA shop TO product_reader;
GRANT SELECT ON TABLE shop.products TO product_reader;

AWS IAM GRANT links the Lambda’s execution role to the product_reader login. USAGE lets that login access objects in the shop schema when it has privileges on them. SELECT is the grant that lets it read shop.products. AWS database roles and IAM mapping · PostgreSQL privileges

IAM authorizes the connection. The role mapping determines which login the Lambda can use, and SQL grants determine what that login can do.

Where can I see the mapping and the grants?

The IAM policy shows dsql:DbConnect and the cluster ARN. Inside Aurora DSQL, I can inspect the role mapping and the SQL grants separately. The query results below are illustrative.

Find the IAM role mapped to the database login

From an admin session, I can look up product_reader in Aurora DSQL’s sys.iam_pg_role_mappings view. Aurora DSQL system views

SELECT pg_role_name, arn, grantor_pg_role_name
FROM sys.iam_pg_role_mappings
WHERE pg_role_name = 'product_reader';

pg_role_name   | arn                                                  | grantor_pg_role_name
product_reader | arn:aws:iam::123456789012:role/ProductReaderLambdaRole | admin

That row tells me which IAM role is mapped to product_reader and who created the mapping. If the Lambda can’t log in, I can compare the ARN in this row with the Lambda’s execution role and check that it’s requesting the right database username.

Find the privileges on the schema and table

PostgreSQL records the schema’s privileges in pg_catalog.pg_namespace.nspacl and the table’s privileges in pg_catalog.pg_class.relacl. I can query both for shop.products. Aurora DSQL system catalogs · PostgreSQL schema catalog · PostgreSQL table catalog

SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       n.nspacl AS schema_grants,
       c.relacl AS table_grants
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS n
  ON n.oid = c.relnamespace
WHERE n.nspname = 'shop'
  AND c.relname = 'products';

Here are the entries for product_reader. The full output can also show grants for the owner and other roles.

schema_name | table_name | schema_grants          | table_grants
shop        | products   | product_reader=U/admin | product_reader=r/admin

U means USAGE on the schema. r means SELECT on the table. /admin tells me who granted each privilege. These entries show the grants made directly to product_reader. PostgreSQL privilege abbreviations

What happens if the Lambda tries to delete a product?

With DynamoDB, the Lambda sends DeleteItem. DynamoDB checks whether the Lambda’s execution role has dynamodb:DeleteItem on the products table. In this example, the role only has dynamodb:GetItem, so DynamoDB denies the request. DynamoDB table policies

With Aurora DSQL, the Lambda can log in as product_reader and read from shop.products. If it sends a DELETE statement, DSQL rejects it because product_reader only has SELECT on that table. PostgreSQL privileges

Conclusion

That changes what I look for in a code review. For DynamoDB, I check the action and the table in the Lambda’s IAM policy. For Aurora DSQL, seeing dsql:DbConnect in CDK tells me which cluster the Lambda can connect to. I still have two questions to answer. Which database role can it log in as? What can that role do once it’s connected?