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'
---
No comments:
Post a Comment