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, you can choose between two different strategies to escalate your users: role escalation or temporary user creation.

With role escalation, Sym invokes a Lambda function in your AWS account to toggle the requesting user’s permissions in the database. With temporary user creation, Sym creates a temporary user using a Lambda function in your AWS account. Users access their temporary credentials using AWS Secrets Manager.

Full Examples in the Examples Repo

📘

postgres_temp_user_strategy and postgres_role_strategy examples

Because so many of our customers ask for a PostgreSQL integration we’ve built end to end templates that Just Work 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;

For Temporary Role Escalation: Set Up Database Users

If you're going to use the temporary role escalation strategy, then you need 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.

For Temporary User Creation: Set Up AWS Secrets Manager

Refer to MySQL on AWS for the details on setting up AWS Secrets Manager for temporary user access. The setup is also built in to our postgres_temp_user_strategy example.

Provisioning your Sym Flow

Now that you have your database ready, let’s configure your Sym Flow! First get your basic Sym Installation set up. Then copy either the postgres_role_strategy or postgres_temp_user_strategy module into your Sym Terraform configurations. These modules are designed to let you get started with minimal additional configuration.

Configure Sym Targets for your Users

Users request access to specific access targets using a select menu in your Sym Flow. Each target will either correspond to an existing postgres role (for the postgres_role_strategy example), OR create a temporary user with a different set of privileges (for the postgres_temp_user_strategy example).

The starter template includes the Sym configuration for a readonly target:

# A target AWS Lambda that will be invoked on escalate and de-escalate.
# The `name` will be used in the lambda to decide which resource to manage access to
resource "sym_target" "readonly" {
  type  = "aws_lambda_function"
  name  = "readonly"
  label = "Read Only"

  settings = {
    # `type=aws_lambda_function` sym_targets have a required setting `arn`
    # which must be the ARN of the AWS Lambda that will be invoked on escalate and de-escalate
    arn = module.postgres_lambda_function.lambda_function_arn
  }
}

For the postgres_temp_user_strategy example, the corresponding SQL configuration for the readonly target goes in the lambda_src/targets directory. You put SQL files in this directory that are named with the same name as each target name. The SQL file gets executed after Sym's Lambda creates your temporary user:

/*
 Grant temp users the readonly role.
 The lambda function provides username as a named variable you can use.
*/
GRANT readonly TO {username};

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.

postgres_role_strategy: 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("-", "_")

postgres_temp_user_strategy: Accessing Temp Users with AWS Secrets Manager.

If you're using the postgres_temp_user_strategy example, refer to the MySQL on AWS docs for setting up end-user access via AWS Secrets Manager.

Testing in the AWS Console

We've provided example payloads in our template that you can use to iterate on your function in the AWS Console. Copy the test events from lambda_src/test and then save them as test events in the console!

Password Management

The postgres example modules provision 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]
  }
}