Moped Read Replica

Information about the Moped Read Replica and how to make new accounts

The Moped Database, which runs in RDS, has been converted into a pair of instances, with the new instance providing a read-only, read replica.

Access

The endpoint for TCP/IP connections on port 5432 is moped-read-replica.austinmobility.io.

Network access to the read replica is accomplished via an AWS security group which limits inbound traffic to the CTM network block to cover traffic from COA office locations and COA VPN connections plus a number of single addresses associated to developer homes.

Account management access is additionally used with the following naming scheme:

• Create a new role for each human user, <lastName + firstInitial>, with read only access to atd_moped and atd_moped_staging.

• Add the new user to the staff role, so that they inherit staff's permissions.

The read replica is inherently read-only, but, just in case, if a developer were to open a tunnel via SSH through the bastion host and connect with these users / roles, accounts intended to be accessed via the read replica are configured to not allow write access.

Account Management

Due to the nature of the read replica, account management is done through primary database. The following is an example of the creation of a new read replica account intended for use by a person:

Creating an account for "John Doe"

create user doej with password '09876543210_strong_password_0987654321';
grant staff to doej;

staff role management

This is for historical documentation; this should not be needed when adding a new user. In the near future, this will be done automatically and on a schedule by an ETL.

-- in staging
GRANT CONNECT ON DATABASE atd_moped_staging TO staff;

grant usage on schema public to staff;
grant usage on schema hdb_catalog to staff;
grant usage on schema deprecated to staff;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO staff;
GRANT SELECT ON ALL TABLES IN SCHEMA hdb_catalog TO staff;
GRANT SELECT ON ALL TABLES IN SCHEMA deprecated TO staff;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO staff;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA hdb_catalog TO staff;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA deprecated TO staff;

-- in production
GRANT CONNECT ON DATABASE atd_moped TO staff;

grant usage on schema public to staff;
grant usage on schema hdb_catalog to staff;
grant usage on schema deprecated to staff;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO staff;
GRANT SELECT ON ALL TABLES IN SCHEMA hdb_catalog TO staff;
GRANT SELECT ON ALL TABLES IN SCHEMA deprecated TO staff;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO staff;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA hdb_catalog TO staff;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA deprecated TO staff;