This example shows you how to make an AWS RDS instance available for border0 users.
AWS. Border0 supports two types of authentication methods that can be used with RDS.

Password authentication
With password authentication, your database performs all administration of user accounts. You create users with SQL statements such as CREATE USER, with the appropriate clause required by the DB engine for specifying passwords.
IAM Authentication
You can authenticate to your DB instance using AWS Identity and Access Management (IAM) database authentication. IAM database authentication works with MySQL and PostgreSQL. With this authentication method, you don't need to use a password when you connect to a DB instance. Instead, you use an authentication token.

To make the socket available, you need to run the border0 socket connect or border0 Connector on a host with access to the database. For example, an ec2 instance or any host if the RDS database is configured to be publicly accessible.

Password Authentication

Creating a Database socket for a RDS using password authentication is the same process as creating a regular MySql or Postgres database. The video below shows the process for an RDS Mysql instance. For more details instructions also see:

You can now connect to this database using the Border0 web client, or your favorite client.

$ border0 client db:mysql --host mysql-border0-demo.border0.io
? what is the name of the database schema: mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47400
Server version: 8.1.0 MysocketSQL MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

📘
Securing your credentials
In the preceding example, we configured Border0 with credentials to access an upstream database. These credentials are essential for the connector running in your environment to establish a secure connection. While Border0 stores these credentials in encrypted form, we strongly recommend that you keep them local to the connector.
You can have the Border0 connector integrated with your secret management solution for enhanced security and keep your secrets local. Learn more about this in our guide:
https://docs.border0.com/docs/using-secret-managers-to-store-credentials

IAM Authentication

To make use of IAM authentication you first need to enable this feature for the RDS database, see https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Enabling.html

And make sure the identify you want to use has permissions to access the database, see: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html

First you need to create the IAM users in the database, see https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.DBAccounts.html

In this example we will use the identity of user Border0ConnectorUser.

MySQL> CREATE USER Border0ConnectorUser IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';

Next we have our new user we can give it some privileges. In this example, we'll give it permission to all tables in the MyAwesomeDatabase database.

MySQL> GRANT ALL ON MyAwesomeDatabase.* TO 'Border0ConnectorUser'@'%';
MySQL> FLUSH PRIVILEGES;

The video below shows these steps in action.

We can now change our previously created socket and use IAM based auth instead. This way the connector doesn't need to have a username and password. As long as it has the correct IAM role to request RDS credentials from AWS.

The border0 Connector will try to get the AWS credentials from the well known files and/or environment variables.

AWS_PROFILE
AWS_REGION
AWS_ACCESS_KEY_ID
AWS_SECRET_ACCESS_KEY

Should your environment utilize roles and policies, below is a simple policy you can attach to your instance role (from AWS docs just above)

You can double check who you're connected as using SELECT CURRENT_USER(); like the example below:

$ border0 client db
? choose a host: rdsiam-border0-demo.border0.io []
? choose a client: mysql
? what is the name of the database schema: border0
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 141628
Server version: 8.1.0 Border0SQL Source distribution

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT CURRENT_USER();
+------------------------+
| CURRENT_USER()         |
+------------------------+
| Border0ConnectorUser@% |
+------------------------+
1 row in set (0.09 sec)

mysql> SHOW GRANTS;
+-------------------------------------------------------------------+
| Grants for Border0ConnectorUser@%                                 |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'Border0ConnectorUser'@'%'                  |
| GRANT ALL PRIVILEGES ON `border0`.* TO 'Border0ConnectorUser'@'%' |
+-------------------------------------------------------------------+
2 rows in set (0.10 sec)

Troubleshooting

Any authentication issues the connector may experience will be logged in the connector logs. It's typically because the connector can't connect to the upstream database or has the wrong credentials. To troubleshoot that take a look at the connector logs as described here

If that doesn't help resolve the issue, feel free to contact us, and we'd be happy to help you troubleshoot.