What are the best practices for sharing production Postgres access with developers?

Question

We're a small SaaS startup (~10 person team; mostly engineers).

We use Postgres on AWS and GCP in production.

We have signed contracts with some customers that prohibit us from giving full production access to developers (to avoid security concerns). I think this is a good thing.

However, every now and then our backend team needs access to the database to analyze data, table records, and see how things are working.

What are some recommended best practices for this?

At some larger companies, they have internal tooling that gives developers access to a middleman CLI through which they can run SQL queries. This middleman logs these for auditing purposes.

Answer 1

We keep a RO clone of the important DB's. Any dev can access the basic RO clone if he's in the right dev group vpn, because the cloudsql instances are deployed in a private service net that peers with our private net and get be gotten to from the outside. If not they can get added it a group.. Then they just need the ip address and they need the user and password. There's an ROnly user given access to everything the postgres user gets, just RO.

PII data goes in a second PII DB, which is set up the same, but requires our compliance department to vet the person for pii access

We also have a firm policy of no poking the DB. Look at it, sure, clone if you have to to determine how to fix a data error, but no raw manhandling. It's to be done with a (possibly one shot) module off the codes db admin function.