PostgreSQL on AWS

Use our prebuilt template to manage PostgreSQL escalations with Sym’s AWS Lambda strategy.

How it Works

With Sym’s PostgreSQL integration for AWS Lambda, users request access to a target database role using Slack or the Sym API. Once approved, Sym invokes a Lambda function in your AWS account to toggle the requesting user’s permissions in the database.

688688

End to End Template

📘

Because so many of our customers ask for a PostgreSQL integration we’ve built an end to end template that Just Works out of the box. If you want to go deeper on understanding the various moving parts of setting up a Sym Flow, follow along with our AWS Lambda Strategy tutorial.

Preparing your PostgreSQL Database for Sym Approvals

Connecting to the Database

Sym’s Lambda function needs to be attached to an AWS VPC that can reach your database. Our example module provides variables for the VPC subnets and AWS security groups to put the Lambda function in. Our module also lets you optionally create a VPC and database to test out the integration in a sandbox.

Set up Sym Target Roles

Set up PostgreSQL roles that users can request access to with Sym. Here we create a readonly role, but you can use existing roles or set up new ones as needed:

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE $DB_NAME TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

Set up a Bot User

Sym will need a user that can connect to your PostgreSQL database and make role assignments. This user must have permissions to grant and revoke your target roles. You can do this using WITH ADMIN OPTION. Here’s an example of how to set this up for the readonly role we created above:

CREATE USER sym_bot WITH PASSWORD 'sym_bot';
GRANT readonly to sym_bot WITH ADMIN OPTION;

Set up End Users

Set up the PostgreSQL users that your requesting users will access the database with. Typically each requesting user should have a corresponding database user. This way you have a full audit log of what the user did while escalated.

📘

Support for generating temporary database users is coming soon! Let us know if you're interested.

Provisioning your Sym Flow

Now that you have your database ready, let’s configure your Sym Flow!

Once you’ve got your Sym installation all set up, copy the postgres_lambda_strategy module into your Sym Terraform configurations. The module is designed to let you get started with minimal additional configuration.

Configure Sym Targets for your PostgreSQL Roles

Fill in the postgres_roles Terraform variable with the list of PostgreSQL roles that you want Sym to manage access to:

postgres_roles = [{ name = "readonly", label = "Read Only" }]

Configure your Database and VPC

If you have an existing VPC and database that you want to connect to, then provide:

  1. The VPC subnets and security groups to put the lambda function in
  2. The database connection details.
db_config = {
  "host" = "sym-example.cluster-abcdefg.us-east-1.rds.amazonaws.com"
  "pass" = "CHANGEME"
  "port" = 5432
  "user" = "sym_master"
}
subnet_ids = [
  "subnet-0cf3122cb5211dare",
  "subnet-04646525ab2ed36d4",
  "subnet-08df0bc9a901eb0ba",
]
security_group_ids = ["sg-01af63b9abc51f647"]

📘

Don't want to connect Sym to an existing database or VPC yet?

Instead of providing the VPC and database configurations, simply set the db_enabled Terraform variable to true. The VPC and database configurations will be provisioned and automatically configured without you needing to supply any additional Terraform variables.

We also provision an EC2 instance that you can tunnel to the database with using AWS Systems Manager Session Manager. Use the tunnel.sh script to forward database traffic to a port on localhost, so that you can connect to and administer the new database.

Password Management

The postgres_lambda_strategy module provisions an encrypted AWS Systems Manager Parameter Store parameter for the bot user’s database password. Because Terraform manages the parameter, you must ensure that your Terraform state is managed securely.

Many teams manage production passwords outside of Terraform state. You can update the password configuration to pull the password from another secret store, such as Vault. You can also supply a placeholder value for password, and use the ignore_changes lifecycle argument. Uncomment the ignore_changes block in the parameter declaration. Then you need to ensure the correct password value is set for the password using an alternate approach, like the CLI.

resource "aws_ssm_parameter" "postgres_password" {
  name  = local.db_password_key
  type  = "SecureString"
  value = local.db_config["pass"]

  tags = var.tags
  
  // Ignore changes to the password value
  lifecycle {
    ignore_changes = [value, version]
  }
}

Mapping Users

You'll need to tell your AWS Lambda function how to map Sym users to users in your PostgreSQL database. By default, our function assumes that users are identified by the subject name of the email address associated with the Sym user. You can update the resolve_user implementation to map the users in another way.

def resolve_user(event: dict) -> str:
    """
    Convert the incoming username, which is an email address, into a database username. Our starter
    implementation creates the database username by using the email subject and then substituting
    hyphens with underscores.
    """
    email = event["run"]["actors"]["request"]["username"]
    username = email.split("@")[0]
    return username.replace("-", "_")

Testing in a Local Sandbox

You can iterate on your handler function locally by setting up a docker compose based PostgreSQL database and then invoking your handler function directly. Once you’ve completed the setup, use the test events we’ve included to execute your handler without reprovisioning:

% cat test/escalate.json | python handler.py
2022-07-31 12:30:50,199 - __main__ - DEBUG - Got event: {'id': '38ddcda0-2589-4922-88ce-f5fe4224c51c', 'meta': {'schema_version': 6}, 'state': {'status': 'processing', 'errors': []}, 'run': {'srn': 'healthy-health:run:postgres:2.0.0:2b1befa5-caf5-4ac3-8509-64732c15cb7d', 'parent': 'healthy-health:run:flow_selection:1.0.0:267a346a-81ab-4e4b-abca-61f135620a40', 'flow': 'healthy-health:flow:postgres:2.0.0', 'actors': {'prompt': {'user': 'healthy-health:user:normal:dead6d58-82f2-46bd-bc70-0590479fa7fc:latest', 'name': '', 'username': '[email protected]', 'identity': {'service': 'slack', 'external_id': 'T01ABCDEFGH', 'user_id': 'U01ABCDEFGH'}}, 'request': {'user': 'healthy-health:user:normal:dead6d58-82f2-46bd-bc70-0590479fa7fc:latest', 'name': '', 'username': '[email protected]', 'identity': {'service': 'slack', 'external_id': 'T01ABCDEFGH', 'user_id': 'U01ABCDEFGH'}}, 'approve': {'user': 'healthy-health:user:normal:479e17fc-2b72-414d-9c41-ccf199a49b62:latest', 'name': '', 'username': '[email protected]', 'identity': {'service': 'slack', 'external_id': 'T01ABCDEFGH', 'user_id': 'U01DLRK2491'}}}}, 'event': {'srn': 'sym:event-spec:approval:1.0.0:escalate', 'type': 'escalate', 'template': 'sym:template:approval:1.0.0', 'timestamp': '2022-05-10T14:47:29.442289', 'channel': 'slack'}, 'actor': {'user': 'healthy-health:user:normal:479e17fc-2b72-414d-9c41-ccf199a49b62:latest', 'name': '', 'username': '[email protected]', 'identity': {'service': 'slack', 'external_id': 'T01ABCDEFGH', 'user_id': 'U01DLRK2491'}}, 'fields': {'reason': 'Fix Ticket 123', 'duration': 30, 'target': {'name': 'readonly', 'srn': 'healthy-health:access_target:aws_lambda_function:postgres:latest:92e28a63-aedb-4b90-93ad-e4ecba49c9b1', 'type': 'aws_lambda_function', 'label': 'Postgres', 'settings': {'arn': 'arn:aws:lambda:us-east-1:0123456789012:function:sym-postgres-prod'}}, 'target_id': '92e28a63-aedb-4b90-93ad-e4ecba49c9b1'}, 'type': 'event'}
2022-07-31 12:30:50,199 - sql - DEBUG - Target role: readonly
2022-07-31 12:30:50,199 - __main__ - DEBUG - SQL statement: Composed([SQL('\n        GRANT\n            '), Identifier('readonly'), SQL('\n        TO\n            '), Identifier('sym_user'), SQL('\n    ')])
2022-07-31 12:30:50,204 - __main__ - DEBUG - Result: {'body': {'username': 'sym_user'}, 'errors': []}
handler.py:99 <module>
    result: {
        'body': {
            'username': 'sym_user',
        },
        'errors': [],
    } (dict) len=2

Did this page help you?