5 ms·

> Sounds like something to not connect to your production db With or without SSL, exposing your raw prod data to external services like these is a huge risk. O

> Sounds like something to not connect to your production db With or without SSL, exposing your raw prod data to external services like these is a huge risk. Only ever share filtered and redacted data with external entities.

Finbarr 9y ago
Definitely agree.
slagfart 9y ago
I think, with Postgres, if you have: 1. A dedicated read-only schema 2. A dedicated user, with only CONNECT to the read-only schema 3. A unique password 4. A dedicated read-only replica DB you should be safe against pretty much everything. I'd actually like to be corrected if I'm wrong - this is how I've built numerous externally-facing services.
- philip1209 9y ago
  For non-secured connections, a snooper could still gain full access to all production data.
  - slagfart 9y ago
    How? Even with the password for this user, you could still only gain access to the read-only schema. Something I should have spelled out - the read-only schema has only the data that the charts need (heavily aggregated views). We basically build with the assumption that the schema will be compromised, but only that one schema.
    - andrewstuart2 9y ago
      Without ssl all that data can be observed in transit between your read-only schema and the consuming service. There's very low risk to integrity (i.e. nobody can modify data via read-only methods), but complete list of confidentiality.
  - dx034 9y ago
    I think it was related to the "with or without SSL". Obviously the data you send can be intercepted without SSL but no other data (and with SSL not even that).
remus 9y ago
Not to mention all the potential operational problems it can cause. At a previous job a new system was deployed to help the customer service team. I can't remember the details, but it was backed by this pretty meaty database so we could collect lots of data for later analysis. The etl processes was delayed for some reason so a couple of people were given 'temporary' access to the production db. Lo and behold, a week later the whole thing mysteriously grinds to a halt as an analyst left some huge query running in the background that locked up all the important tables until an admin can in and kicked them off. Letting people run arbitrary workloads on a system you want to be stable is a bad idea.

Finbarr 9y ago

Definitely agree.

slagfart 9y ago

I think, with Postgres, if you have: 1. A dedicated read-only schema 2. A dedicated user, with only CONNECT to the read-only schema 3. A unique password 4. A dedicated read-only replica DB you should be safe against pretty much everything. I'd actually like to be corrected if I'm wrong - this is how I've built numerous externally-facing services.

philip1209 9y ago
For non-secured connections, a snooper could still gain full access to all production data.
- slagfart 9y ago
  How? Even with the password for this user, you could still only gain access to the read-only schema. Something I should have spelled out - the read-only schema has only the data that the charts need (heavily aggregated views). We basically build with the assumption that the schema will be compromised, but only that one schema.
  - andrewstuart2 9y ago
    Without ssl all that data can be observed in transit between your read-only schema and the consuming service. There's very low risk to integrity (i.e. nobody can modify data via read-only methods), but complete list of confidentiality.
- dx034 9y ago
  I think it was related to the "with or without SSL". Obviously the data you send can be intercepted without SSL but no other data (and with SSL not even that).

remus 9y ago

Not to mention all the potential operational problems it can cause. At a previous job a new system was deployed to help the customer service team. I can't remember the details, but it was backed by this pretty meaty database so we could collect lots of data for later analysis. The etl processes was delayed for some reason so a couple of people were given 'temporary' access to the production db. Lo and behold, a week later the whole thing mysteriously grinds to a halt as an analyst left some huge query running in the background that locked up all the important tables until an admin can in and kicked them off. Letting people run arbitrary workloads on a system you want to be stable is a bad idea.