[AWS] Read Only user for RDS to S3 feature

Summary

This article describes the options available when backing up RDS to S3.

Description

From version 4.x and upwards, N2W supports exporting RDS MySQL database to S3 for long term cost saving, this is done using two steps:

1. Using the existing AWS RDS export to S3 feature to copy the data to the S3 bucket.
2. Exporting the schema (as this is not exported as part of the AWS RDS export to S3 feature)

For the second step of exporting the schema, N2W only does read operations and requires the user to provide username/password for the database when configuring the backup policy,

This means provided user can be an Admin user with full access or a Read Only user which will allow us to read the schema, for example:

MySQL (4.0 and above):

CREATE USER 'testuser4'@'%' IDENTIFIED BY '12345678A';

GRANT SELECT, PROCESS, LOCK TABLES ON *.* TO 'testuser4'@'%';

FLUSH PRIVILEGES;

PostgreSQL ( 4.1 and above): 
CREATE ROLE <role_name>;

GRANT CONNECT ON DATABASE <schema_name> TO <role_name>;

GRANT USAGE ON SCHEMA <schema_name> TO <role_name>;

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO <role_name>;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA <schema_name> to <role_name>;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <role_name>;

CREATE USER <username> WITH PASSWORD '<password>';

GRANT <role_name> TO <username>;