{"id":3711,"date":"2021-10-29T13:59:00","date_gmt":"2021-10-29T11:59:00","guid":{"rendered":"https:\/\/blog.besharp.it\/?p=3711"},"modified":"2021-10-28T12:42:41","modified_gmt":"2021-10-28T10:42:41","slug":"installing-custom-plugins-on-rds-using-cloudformations-custom-resource","status":"publish","type":"post","link":"https:\/\/blog.besharp.it\/installing-custom-plugins-on-rds-using-cloudformations-custom-resource\/","title":{"rendered":"Installing custom plugins on RDS using CloudFormation\u2019s Custom resource"},"content":{"rendered":"\n
Database management and administration<\/strong> has always been a very delicate task both on-premises and on the cloud. The effort to configure and maintain a single database is pretty high: network configurations<\/strong>, restricting permissions to various users, groups,<\/strong> and roles, and the various backups<\/strong> and updates<\/strong> are tasks that take a lot of resources. Moreover, those configurations may change over time<\/strong>, causing the need to revise everything and modify somewhere, involving the risk attached to those modifications. <\/p>\n\n\n\n Having the DB as a managed service really lowers the effort required with features like scalability, high availability, fault tolerance, and security. <\/p>\n\n\n\n Given this, there are several cases in which a database administrator (DBA) may need to install one or more plug-ins to enable some useful features<\/strong> for the database. Some examples, specifically related to the PostgreSQL engine, may be the installation of PostGis<\/strong>, for location queries, and PGCrypto<\/strong> for cryptographic functions, like generating salts and hashing some strings. These are usually manual operations and they require a lot of time for a single database. <\/p>\n\n\n\n However, it\u2019s something that is not feasible in a real-world scenario in which, even small companies have multiple databases depending on the different services and applications that they have. Repeating the same steps to properly configure everything for another database, or many more can be very time-consuming<\/strong> and requires a lot of effort. Moreover, manual configurations are always prone to human errors<\/strong>. <\/p>\n\n\n\n For these reasons, the next big challenge is to find a reproducible set of configurations to deploy several other databases in an automated way<\/strong>. This is where Infrastructure as Code (IaC) comes into play. Tools like Terraform or Pulumi can be used to automate the deployment of different resources in different environments, both local or on some supported cloud providers, describing the desired infrastructure with code. Usually, cloud providers offer their proprietary IaC solutions. The AWS one is CloudFormation. <\/p>\n\n\n\n Using cloud services combined with IaC templates, we are able to build a reusable solution that, with few manual actions, can automatically deploy several databases that are fully managed. This means: no more time spent patching the databases or replacing the old hardware! However, there is still the need for manual actions<\/strong> for the installation of specific plug-ins, therefore, involving all the issues related to the manual action on the configurations of a database. <\/p>\n\n\n\n For this problem, there is still no standard solution. Indeed, there is quite some variability. Starting from the selection of a given database engine, for example, PostgreSQL or MySQL, there are different versions of it, each one supporting different plug-ins and the different versions of them. In short, it is necessary to find a reproducible set of configurations that is compatible and work together. Once we have this reproducible solution we can take advantage of IaC to fully automate the deployment of several database instances.<\/p>\n\n\n\n In this article, we will propose a possible solution to this problem automating the deployment of databases along with the installation of some plug-ins<\/strong> over them. The examples in this article will use AWS, as a cloud provider, and PostgreSQL (RDS), as a database service.<\/p>\n\n\n\n Before getting our hands dirty with the Cloudformation code, there are some things that we need to take into account. We will assume that all the configurations relative to the environment in which the database will be installed are already done since they are out of the scope of this article. For this reason, things like VPC, subnets, routing tables, and security groups are meant as already prepared and, therefore, will be taken as input parameters in our infrastructure as code.<\/p>\n\n\n\n Starting from the deployment of a database, three major resources are needed to create a database instance: a subnet group to handle the networking of the database, a parameter group to define some parameters specific to the given database family, and an option group to configure some specific features of the given database engine.<\/p>\n\n\n\n As we can see from the template, we have placed the database instance inside the private subnets (subnet group) and we\u2019ve configured the option group to use Postgres13. Meanwhile, with the parameter group, we have configured a simple parameter, for the sake of explanation, that is the maximum number of connections for the database.<\/p>\n\n\n\n Now, it\u2019s the time of the actual database instance. Since this is just a proof of concept, we can just use very basic configurations so we can use a db.m5.large instance with 20 GBs of storage (gp2). Then we can set other additional parameters like instance name, database name, master user along with the master password, the parameters for the encryption of the storage, like the KMS key, the preferred backup, and maintenance windows.<\/p>\n\n\n\n Along with the DB instance, there are just a few additional resources that can improve the security of our database. We have defined a KMS key, along with its alias, to encrypt the storage and a secret inside the Secrets Manager to store the admin credentials to access the DB. Moreover, we could also set up a rotation mechanism that rotates the password very frequently, like every single day. These kinds of resources could be cloudformated<\/em> too, however, we won\u2019t get into the details of it since it\u2019s not the main topic of this article.<\/p>\n\n\n\n Just one last thing to add regarding DB access, inside AWS there is also the possibility of using IAM credentials to access DB instances. This feature may be useful to increase the security of the database since the number of credential pairs is drastically reduced.<\/p>\n\n\n\n Now that we have deployed our CloudFormation template and our database is in place, we can try to connect to it and do some queries, for example, we can check the set of installed plug-ins with <\/p>\n\n\n\n <\/p>\n\n\n\nDBSubnetGroup:\n Type: 'AWS::RDS::DBSubnetGroup'\n Properties:\n DBSubnetGroupDescription: !Sub \"${DBName}-db-subnet-group\"\n SubnetIds: [!Ref PrivateSubnetA, !Ref PrivateSubnetB, !Ref PrivateSubnetC]\n Tags:\n - Key: Name\n Value: !Sub \"${DBName}-db-subnet-group\"<\/code><\/pre>\n\n\n\n
DBOptionGroup:\n Type: \"AWS::RDS::OptionGroup\"\n Properties:\n EngineName: \"postgres\"\n MajorEngineVersion: 13\n # OptionConfigurations: [] # no options needed for PostgreSQL\n OptionGroupDescription: !Sub \"${DBName}-db-option-group\"\n Tags:\n - Key: Name\n Value: !Sub \"${DBName}-db-option-group\"<\/code><\/pre>\n\n\n\n
DBInstance:\n Type: 'AWS::RDS::DBInstance'\n Properties:\n DBInstanceIdentifier: !Ref DBName\n Engine: \"postgres\"\n DBInstanceClass: \"db.m5.large\"\n StorageType: \"gp2\"\n AllocatedStorage: 20\n DBParameterGroupName: !Ref DBParameterGroup\n OptionGroupName: !Ref DBOptionGroup\n DBSubnetGroupName: !Ref DBSubnetGroup\n VPCSecurityGroups: [!Ref DBSecurityGroup]\n MasterUsername: !Ref DBMasterUser\n MasterUserPassword: !Ref DBMasterUserPassword\n DBName: !Ref DBName\n Port: 5432\n AutoMinorVersionUpgrade: true\n CopyTagsToSnapshot: true\n Tags:\n - Key: Name\n Value: !Sub \"${DBName}-db\"<\/code><\/pre>\n\n\n\n
SELECT * FROM pg_extension;<\/pre>\n\n\n\n