Documentation
RDS
Configuration

RDS Configuration

💡

When you create a DB instance, the DB instance is associated with the default DB parameter group. Because you cannot modify this group, create a custom parameter group to modify the parameter group. When you change the DB instance parameter group (from default to a custom group), you must reboot the DB instance.

Enable Logging

Create a DB parameter group

  • Open the Amazon RDS console, and then choose Parameter groups from the navigation pane.

/docs/rds-1.png

  • Choose Create parameter group.

Screenshot 2022-12-21 at 8.42.35 PM.png

  • From the Parameter group family drop-down list, choose a DB parameter group family.

Screenshot 2022-12-21 at 8.51.12 PM.png

  • For Type, choose DB Parameter Group.

Screenshot 2022-12-21 at 8.49.20 PM.png

  • Enter the name in the Group name field.

Screenshot 2022-12-21 at 8.53.10 PM.png

  • Enter a description in the Description field.

Screenshot 2022-12-21 at 8.55.16 PM.png

  • Choose Create.

Modify the new parameter group

  • Open the Amazon RDS console, and then choose Parameter groups that you just created in the previous steps from the navigation pane.

Screenshot 2022-12-21 at 9.03.03 PM.png

  • Choose Edit parameters ( steps for mysql , postgres and aurora are given below separately)

Screenshot 2022-12-21 at 9.06.02 PM.png

MySql or Aurora-Mysql Database

  • Choose the parameter that you want to modify, then choose Edit.

Screenshot 2022-12-21 at 9.13.44 PM.png

  • set the following parameters to these values :
  • General_log = 1 (default value is 0 or no logging)

Screenshot 2022-12-21 at 9.15.36 PM.png

  • Similarly set Slow_query_log = 1 (default value is 0 or no logging)
  • Long_query_time = 2 (to log queries that run longer than two seconds)
  • log_output =TABLE (writes both the general and the slow query logs to a table so you can view these logs with a SQL query)
  • Choose Save Changes. Note: You can't modify the parameter settings of a default DB parameter group. You can modify the parameter in a custom DB parameter group if Is Modifiable is set to true.

Screenshot 2022-12-21 at 9.18.46 PM.png

To test the logging, run the following command to query the log tables:

   Select * from mysql.slow_log
   Select * from mysql.general_log

Make a user 'vectoredgeRead' and Grant Read Access

  1. CREATE USER 'vectoredgeRead'@'%' IDENTIFIED BY 'password';
  2. GRANT SELECT on mysql.role_edges TO 'vectoredgeRead'@'%';
  3. GRANT SELECT on information_schema.tables TO 'vectoredgeRead'@'%';
  4. GRANT SELECT on mysql.user TO 'vectoredgeRead'@'%';
  5. GRANT SELECT on mysql.general_log TO 'vectoredgeRead'@'%';
  6. GRANT SELECT on mysql.tables_priv TO 'vectoredgeRead'@'%';
  7. GRANT SELECT on mysql.db TO 'vectoredgeRead'@'%';

Postgres and Aurora-Postgres

Enable query logging on PostreSQL

  • To enable query logging on PostgreSQL, follow these steps:

  • In the Filter parameters field, select the parameters related to logging and change their values as folows:

    Screenshot 2022-12-21 at 10.29.07 PM.png

  • Enter log_statement and change the value to ddl.

Screenshot 2022-12-21 at 10.33.26 PM.png

  • Enter log_min_duration_statement and change the value to 1000. (This value is in milliseconds, so 1000 equals one second).
  • Choose Save changes. Note: These parameters are dynamic. If your DB instance already has a custom parameter group, you don't need to reboot the DB instance for these parameters to take effect.

Make a user 'vectoredgeRead' and Grant Read Access

  1. CREATE USER vectoredgeRead WITH PASSWORD 'my_password';

  2. The following queries need to be run in every individual database:

    GRANT Select ON information_schema.tables to vectoredgeRead; GRANT Select ON information_schema.role_table_grants to vectoredgeRead;

  3. GRANT Select ON pg_stat_activity to vectoredgeRead;

  4. GRANT Select ON pg_user to vectoredgeRead;

  5. GRANT Select ON pg_auth_members to vectoredgeRead;

  6. GRANT Select ON pg_roles to vectoredgeRead;

Associate the instance with the DB parameter group

  • Open the Amazon RDS console, and then choose Databases from the navigation pane.
  • Choose the instance that you want to associate with the DB parameter group, and then choose Modify.
  • From the Database options section, choose the DB parameter group that you want to associate with the DB instance.
  • Choose Continue. Note: The parameter group name changes and applies immediately, but parameter group isn't applied until you manually reboot the instance. There is a momentary outage when you reboot a DB instance, and the instance status displays as rebooting.
Last updated on December 31, 2022