Overview

You can add RDS databases to Opal easily using RDS IAM database authentication.

Follow the steps below to get started!

Supported RDS operating systems

Amazon IAM database authentication is available for the following database engines:

  • MySQL 8.0, minor version 8.0.16 or higher

  • MySQL 5.7, minor version 5.7.16 or higher

  • MySQL 5.6, minor version 5.6.34 or higher

  • PostgreSQL 12, all minor versions

  • PostgreSQL 11, all minor versions

  • PostgreSQL 10, minor version 10.6 or higher

  • PostgreSQL 9.6, minor version 9.6.11 or higher

  • PostgreSQL 9.5, minor version 9.5.15 or higher
    Don't see what you want here? Let us know by suggesting an edit or emailing us at [email protected] and we'll make it happen.

Adding an RDS database

Step 1: Enable IAM authentication

You'll need to enable IAM database authentication for your RDS instance. You can do this using the AWS Console and CLI. You can read more on how to do this here in the AWS official documentation.

AWS Console

Modify your RDS instance

Modify your RDS instance

Enable IAM database authentication.

Enable IAM database authentication.

AWS CLI

Shell

aws rds modify-db-instance \
--db-instance-identifier mydbinstance \
--apply-immediately \
--enable-iam-database-authentication


🚧 Enabling IAM Authentication requires a brief outage for your RDS database.


Step 2: Create database users

Connect to your database and create a database user with read-only access. If you aren't familiar with how to do this using SQL, here is a snippet you can use for Postgres and MySql.

PL/pgSQL

-- Create a group 
CREATE ROLE readaccess;

--Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

-- Create a user with IAM authentication
CREATE USER readonly;

-- NOTE: THIS USERNAME IS REQUIRED
GRANT readaccess TO readonly;
GRANT rds_iam TO readonly;
CREATE USER fullaccess;

-- NOTE: THIS USERNAME IS REQUIRED
GRANT rds_superuser TO fullaccess;
GRANT rds_iam TO fullaccess;


MySQL

-- NOTE: The usernames readonly and fullaccess are required 
GRANT SELECT ON *.* TO 'readonly'@'%' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';
GRANT SELECT ON *.* TO 'readonly'@'%' WITH GRANT OPTION;

CREATE USER 'fullaccess'@'%' IDENTIFIED WITH AwsAuthenticationPlugin AS 'RDS';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO 'fullaccess'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Custom Database Users

If you'd like to instead define your own database users with custom amounts of access, you can do that easily through AWS tags in two steps.

1. Create the user with any username that you like and grant them rds_iam with the following queries:

PL/pgSQL

GRANT rds_iam TO myuser;

MySQL

CREATE USER myuser IDENTIFIED WITH AwsAuthenticationPlugin AS 'RDS';

2. Tag the RDS database with key opal:user:1 and value myuser. You can manage multiple levels of access using more tags! Example: opal:user:2 => myseconduser. To see how to tag your database check out Step 3.

Now the custom database users will show up in Opal!

Step 3: Tag the database

Finally, tag the RDS database with opal so Opal knows which RDS databases you'd like to make requestable in Opal:

AWS Console

Tagging an RDS database with `opal` in the AWS Console.

Tagging an RDS database with opal in the AWS Console.

AWS CLI

Shell

aws rds add-tags-to-resource \
--resource-name arn:aws:rds:<REGION>:<AWS_ACCOUNT_ID>:db:<DB_INSTANCE_NAME> \
--tags "Key=opal,Value="

Terraform

Add the following tags argument to your AWS RDS instance:

tags = {
opal = ""
opal:database-name="my_db" // This is optional. By default, Opal will pull the database name from RDS's configuration
}

Getting access to an RDS database in Opal

Once you've successfully tagged an RDS database, it will appear under the Postgres or MySQL tabs on the permissions page. If you followed the setup steps above, you'll be able to grant read-only access and full access to your database.

Read-only and full access permissions to RDS in Opal.

Read-only and full access permissions to RDS in Opal.

Access to RDS databases in Opal is session-based, meaning your developers won't have access unless they initiate a session for themselves.

Start a session to an RDS database.

Start a session to an RDS database.

You can instantly open up a connection to the database using your favorite table viewer, like Postico or TablePlus, or connect manually. Your credentials that will only last 15 minutes to either a read-only user or a user with full-access.

Automatically connect to your database or use manual configuration.

Automatically connect to your database or use manual configuration.


NEXT UP ➡️

Did this answer your question?