Showing posts with label AWS RDS. Show all posts
Showing posts with label AWS RDS. Show all posts

Tuesday, 7 October 2025

Amazon RDS (Relational Database Service)

 


Amazon Relational Database Service (RDS) 
  • Distributed relational database service
  • Simplifies the setup, operation, and scaling of a relational database
  • Automates admin tasks like patching the database software, backing up databases and enabling point-in-time recovery 
  • Scaling storage and compute resources is done via API call

Amazon RDS supports eight major database engines:
  • Oracle (proprietary)
  • Microsoft SQL Server (proprietary)
  • IBM Db2 (community-developed)
  • Amazon Aurora (MySQL- and PostgreSQL-compatible)(open-source)
  • MySQL
  • MariaDB
  • PostgreSQL


Networking

We can launch Amazon RDS databases in the public or private subnet of a VPC. 

If DB instance is in a public subnet and we want it to be accessible from Internet:

  • Publicly Accessible property of the DB instance needs to be set to Yes
  • Inbound rules for the security group of the RDS instance need to allow connections from source IP
  • Internet Gateway needs to be attached to VPC

Troubleshooting


To test if RDS is accessible from Internet (and also that it's up and listening) we can use Telnet or Netcat.

If using MacOS, Telnet is not installed by default so install it via brew:

% brew install telnet

% telnet test-example-com-13-20240712155825.ckmh7hyrsza3.us-east-1.rds.amazonaws.com 3306 
Trying 121.65.2.95...
Connected to ec2-10-10-129-99.compute-1.amazonaws.com.
Escape character is '^]'.
Connection closed by foreign host.

If we want to use Netcat:

% nc test-example-com-13-20240712155825.ckmh7hyrsza3.us-east-1.rds.amazonaws.com 3306 -v
Connection to test-example-com-13-20240712155825.ckmh7hyrsza3.us-east-1.rds.amazonaws.com port 3306 [tcp/mysql] succeeded!


MySQL

Terraform


These are the privileges that allow user to execute DROP USER in RDS MySQL DB:

# Global privileges
resource "mysql_grant" "bojan_global" {
  user       = mysql_user.bojan.user
  host       = mysql_user.bojan.host
  database   = "*"
  table      = "*"
  privileges = ["CREATE USER"]
}

# Database-level privileges
resource "mysql_grant" "bojan" {
  user       = mysql_user.bojan.user
  host       = mysql_user.bojan.host
  database   = "%"
  privileges = ["SELECT", "SHOW VIEW", "INSERT", "UPDATE", "EXECUTE", "DELETE"]
}

root user in MySQL in RDS has not all admin privileges as if it was the regular MySQL instance.

root user in RDS MySQL can't grant SYSTEM_USER privilege to another user as this error occurs:

Error 1227 (42000): Access denied; you need (at least one of) the RDSADMIN USER privilege(s) for this operation

root user does not have privileges to grant privileges on mysql DB as this error occurs:

Error running SQL (GRANT DELETE ON `mysql`.* TO 'bojan'@'%'): Error 1044 (42000): Access denied for user 'root'@'%' to database 'mysql'


---

Resources:

Monday, 7 February 2022

Running aws-cli in Docker container

Local host prerequisites:
 
The simplest use case is to run aws-cli in Docker container only to check its version:

$ docker run --rm -it amazon/aws-cli --version
Unable to find image 'amazon/aws-cli:latest' locally
latest: Pulling from amazon/aws-cli
3a461b3ae562: Pull complete
7712a86ec383: Pull complete
3cb574325c5f: Pull complete
5d0b01aa5bf0: Pull complete
1aed496d20a4: Pull complete
Digest: sha256:f307dff4414de258337aee8c3c5d8a468c669426fdb8254c81984f72b51fc914
Status: Downloaded newer image for amazon/aws-cli:latest
aws-cli/2.4.16 Python/3.8.8 Linux/5.4.0-91-generic docker/x86_64.amzn.2 prompt/off
 
The output of the aws-cli itself (not Docker) is at the end:
 
aws-cli/2.4.16 Python/3.8.8 Linux/5.4.0-91-generic docker/x86_64.amzn.2 prompt/off 
 
If a new version of the Docker image is available, the one already pulled onto local host should be updated:

$ docker pull amazon/aws-cli:latest
 
To check if AWS credentials and config are set on the local host you can execute aws configure (aws is the name of the locally installed aws-cli binary) and press ENTER for each prompt:

$ aws configure
AWS Access Key ID [****************BWO7]:
AWS Secret Access Key [****************6BhK]:
Default region name [us-east-1]:
Default output format [json]:  

These settings can be set for particular profile. If no profile is explicitly set, a default one is used. To specify profile name, we can use --profile argument:

$ aws configure list --profile default
      Name                    Value             Type    Location
      ----                    -----             ----    --------
   profile                  default           manual    --profile
access_key     ****************
BWO7 shared-credentials-file    
secret_key     ****************
6BhK shared-credentials-file    
    region                us-east-1      config-file    ~/.aws/config

If no profile is specified, a default one is used:

$ aws configure list
      Name                    Value             Type    Location
      ----                    -----             ----    --------
   profile                  default           manual    --profile
access_key     ****************BWO7 shared-credentials-file    
secret_key     ****************6BhK shared-credentials-file    
    region                us-east-1      config-file    ~/.aws/config

To see the plain values of AWS credentials and config set on the local host we can check out the content of files which store that information:
 
$ cat ~/.aws/credentials
[default]
aws_access_key_id = BKI...BWO7
aws_secret_access_key = tdA...6BhK

$ cat ~/.aws/config
[default]
region = us-east-1
output = json
 

To make locally stored AWS credentials and configuration available to aws-cli running in Docker container, we can map host directory ~/.aws into container's /root/.aws:

$ docker run --rm -it -v ~/.aws:/root/.aws amazon/aws-cli --version
aws-cli/2.4.16 Python/3.8.8 Linux/5.4.0-91-generic docker/x86_64.amzn.2 prompt/off

In the example above aws-cli does not use credentials actually but it tests sharing local folder with container.
 
Another way is to pass the credentials and configuration via environment variables:

$ docker run \
--rm \
-it \
-e AWS_ACCESS_KEY_ID=BKI...BWO7 \
-e AWS_SECRET_ACCESS_KEY=tdA...6BhK \
-e AWS_REGION=us-east-1 \
amazon/aws-cli --version

This command above actually does not require having aws-cli installed on the local host.


Accessing Amazon Relational Databases (RDS)


A typical RDS dashboard looks like here: 

 

To get full information about all db instances run:

$ docker run --rm -it -v ~/.aws:/root/.aws amazon/aws-cli rds describe-db-instances

The output is a very comprehensive list of all DB instances and their attributes.
 
To list only DB identifiers we can use --query argument in order to control which properties from the output JSON we want to see:
 
$ docker run --rm -it -v ~/.aws:/root/.aws amazon/aws-cli rds describe-db-instances --query 'DBInstances[*].[DBInstanceIdentifier]' 
[
    [
        "my-db-instance-01"
    ],
    [
        "my-db-instance-02"
    ],
    [
        "my-db-instance-03"
    ],
    [
        "my-db-instance-04"
    ],
    [
        "my-db-instance-05"
    ]
]

If we pipe this output to a file, we'll see that this JSON is polluted with extra characters:


The reason is that the output was first sent to TTY device for no real benefit as we didn't actually need an interactive terminal. Detailed explanation is here: amazon web services - AWS CLI returns JSON with control codes making JQ fail - Stack Overflow. The bottom line is: don't use -it option with docker run if piping the output not to TTY but to another Unix command or tool, like jq.

rds describe-db-instances output can be set not to be JSON but a plain text:

$ docker run --rm -v ~/.aws:/root/.aws amazon/aws-cli rds describe-db-instances --query 'DBInstances[*].[DBInstanceIdentifier]' --output text
my-db-instance-01
my-db-instance-02
my-db-instance-03
my-db-instance-04
my-db-instance-05

 

To get all details for particular DB instance:

$ docker run --rm -v ~/.aws:/root/.aws amazon/aws-cli rds describe-db-instances --db-instance-identifier my-db-instance-01
{
    "DBInstances": [
        {
            "DBInstanceIdentifier": "my-db-instance-01",
            "DBInstanceClass": "db.t3.medium",
            "Engine": "mysql",
            "DBInstanceStatus": "available",
            "MasterUsername": "my_username",
            "Endpoint": {
                "Address": "my-db-instance-01.abcd6efgh3.us-east-1.rds.amazonaws.com",
                "Port": 3306,
                "HostedZoneId": "A252ITUGPM61PM"
            },

...

 To get only details about the endpoint:

$ docker run --rm -v ~/.aws:/root/.aws amazon/aws-cli rds describe-db-instances --db-instance-identifier my-db-instance-01 --query 'DBInstances[0].Endpoint' --output text
my-db-instance-01.abcd6efgh3.us-east-1.rds.amazonaws.com       A252ITUGPM61PM  3306

To pick only Address and Port values:
 
$ docker run --rm -v ~/.aws:/root/.aws amazon/aws-cli rds describe-db-instances --db-instance-identifier my-db-instance-01 --query 'DBInstances[0].Endpoint.[Address, Port]' --output text
my-db-instance-01.abcd6efgh3.us-east-1.rds.amazonaws.com    3306

---