Table of contents
Is the handoff ready?
Use these checks before access expands or the first ticket begins.
Scope
Ready: The question, freshness, tables, and columns are written.
Needs work: The account can read the whole schema.
Target
Ready: The account reaches only the replica endpoint.
Needs work: The application account is shared with the reporter.
Cost
Ready: A timeout, a row limit, and a schedule are enforced.
Needs work: Ad hoc queries run unbounded on the replica.
Closure
Ready: The grant closes with the ticket and the login is tested.
Needs work: Old reporting accounts stay open for months.
Name the reporting questions the replica serves
A read replica exists to move reporting and analytical queries away from the primary database. Write the questions the replica must answer and the freshness the answers need. A dashboard that tolerates an hour of lag is a different request from a support screen that needs the last second. Record the tolerance next to each question.
List the tables and the columns each question needs, and mark any personal data. Do not grant a whole schema because one report needs three columns. A narrow grant is easier to review and easier to close. The list becomes the scope of the access ticket.
Grant access to the replica, not the primary
Create a separate database account that can connect only to the replica endpoint. Confirm the account cannot write, cannot change settings, and cannot reach the primary. Record the host, the port, the database, and the role in the ticket. Test the grant from the same network the offshore team uses.
Do not share the application database account or a personal account with the contractor. Use a named account per person or per reporting service. Record the owner of the account and the ticket it serves. A shared account hides who ran a query and makes revocation impossible.
Bound the data and the query cost
Set a statement timeout and a row limit for the reporting role, and require a date range on large tables. Check the query plan for a sequential scan over the largest table before approving a new report. Record the expected run time and the schedule. A report that runs every minute can starve the replica for everyone else.
Exclude sensitive columns unless the question requires them, and mask values where a partial view is enough. Record the masking rule and the person who approved it. Review the rule when the report changes. The grant should be as narrow as the current question, not the next one.
Log each query and its owner
Enable query logging on the replica with enough detail to identify the account, the statement, and the time. Keep the log in the client logging system with a retention rule. Review a sample each week for queries outside the approved tables. A log that nobody reads is storage, not oversight.
Alert on a query that scans a sensitive table or runs longer than the agreed limit. Send the alert to the reporting owner and the delivery lead. Record the response and any change to the grant. The alert should lead to a conversation, not an automatic block that nobody understands.
Review grants on a fixed cadence
Review every replica grant each month against the open reporting tickets. Confirm that the account is still needed and the query scope still matches. Remove accounts that belong to a finished project. Record the review date and the reviewer.
Compare the grant list with the identity provider so a departed contractor cannot keep a database login. Check for accounts created outside the ticket process. Record any exception with an owner and an expiry. The review should be short enough to finish in one sitting.
Close a grant when the ticket ends
Close the grant on the same day the reporting ticket closes, and record the revocation time. Confirm that the login fails before marking the ticket done. Check for scheduled jobs that still use the account. A job that breaks is easier to fix than a login that stays open for months.
Keep the query log for the retention period after the grant closes. Note the final review and any data that was exported. Remove the account from the identity provider as well as the database. A closed grant with a live identity is not closed.
Copy-ready brief
Put the operating agreement in writing.
Record the report question, freshness tolerance, tables, and columns before access is granted.
Create a named read-only account that cannot reach the primary database.
Apply a statement timeout, a row limit, and a masking rule for sensitive columns.
Revoke on ticket close and confirm the login fails before marking the work done.
Frequently asked questions
Why not let reporting use the primary database?
Heavy analytical queries compete with application traffic, so a replica keeps reporting away from customers.
How fresh is replica data?
It depends on replication lag, so record the tolerance and check the lag for time-sensitive questions.
Can a contractor reuse one account for several reports?
Prefer a named account per person or service so the owner and the scope stay visible.
Sources
- PostgreSQL hot standby
Read replica behavior and lag considerations.
- NIST SP 800-53 security and privacy controls
Least privilege and access review expectations.
- NIST SP 800-92 log management
Query logging and retention practice.
- CISA Secure by Design
Accountability and secure access design.
