1. What is the problem? Be very detailed.
I created a DigitalOcean droplet and installed ODK Aggregate. Everything worked fine until now and I have already submitted an important amount of data collected in the field. Now the Aggregate web page is not available anymore and it says "Error: Unable to retrieve granted authorities of USER_IS_ANONYMOUS". When I try to connect the database using pgadmin4 from my local machine I cannot see the "Aggregate" schema anymore and I have any idea how to access my data.
Please help!
Thank you very much.
2. What app or server are you using and on what device and operating system? Include version numbers.
I am using a basic Digital Ocean droplet. I followed the instructions https://docs.opendatakit.org/aggregate-do/ step by step.
3. What you have you tried to fix the problem?
4. What steps can we take to reproduce the problem?
5. Anything else we should know or have? If you have a test form or screenshots or logs, attach below.
@scampus Sorry to hear you are suffering data loss. Here's what I'd recommend...
- Turn on Digital Ocean's backups so you have a snapshot of the machine.
- Do a PostgreSQL backup and get that off the server. SFTP would help here.
- It sounds like you've been trying to connect pgadmin4 to your DB and perhaps something went wrong. Can you provide more detail about the steps you took?
- Is there anything else that changed between when everything was working and when you saw failure?
@yanokwa first of all thank you so much for your reply and your recommendation! I will treasure it for my further usages.
Unfortunately the disappearance of the "aggregate" schema (and all my data with it!) is not the only thing! I realized that a new table appeared saying:
"To recover your lost Database and avoid leaking it: Send us 0.06 Bitcoin (BTC) to our Bitcoin address 1GH42zWNAoFWVC6UyNut9aJDi2u83sqSeu and contact us by Email with your Server IP or Domain name and a Proof of Payment. If you are unsure if we have your data, contact us and we will send you a proof. Your Database is downloaded and backed up on our servers. Backups that we have right now: aggregate . If we dont receive your payment in the next 10 Days, we will make your database public or use them otherwise"
It makes me feel so powerless and frustrated but at the same time aware of the fact that all problems encountered help understanding how to take any required actions like you recommended.
@scampus I'm sorry to say you've been a victim of a hack. https://www.bitcoinabuse.com/reports/1GH42zWNAoFWVC6UyNut9aJDi2u83sqSeu suggests you are not the only one.
I encourage you to contact Digital Ocean and report it. They might be able to help recover your data.
Do you still have the devices that submitted the data? By default, ODK Collect doesn't delete the submissions, so if you have the devices, you should still have the data.
It would be good to understand what the security hole was.
- Any chance your other accounts may have been compromised? See https://haveibeenpwned.com to check.
- Did you have a strong unique password on your Digital Ocean account?
- Did you have a strong unique root password on the machine?
- Did you change any of the PostgreSQL settings to allow remote access?
@yanokwa yes I still have the submitted forms on my device and I am waiting for the Digitalocean Team to reply me.
I am in the process of implementing the basic requirements to improve my machine security. Thanks again for your support!
@scampus Can you help me understand what the security hole was? I'm asking again because if we know where the hole was, we can update our docs to make sure other users don't make the same mistake.
- Did you have a strong unique password on your Digital Ocean account?
- Did you have a strong unique root password on the machine?
- Did you change any of the PostgreSQL settings to allow remote access? If so, what were the changes?
@yanokwa sorry for being so hurried in my reply. I will try to take you through the steps I took.
- I signed up to Digital Ocean account using the option "Sign up with Google" and my Google password is not the best example of a strong unique password.
- I am afraid my machine password is not a strong unique password either: no upper case, no symbols!
- Yes I changed some of the PostgreSQL settings to allow remote access. On the machine, I added the line
host all all 0.0.0.0/0 trust on /etc/postgresql/10/main/pg_hba.conf and changed /etc/postgresql/10/main/postgresql.conf by setting listen_addresses='*' to make postgres be listened from remotely. I guess that using 0.0.0.0/0 instead of my local machine IP address is not exactly what people would call a brilliant idea!
Please let me know if you need more details as well as providing any other recommendation to avoid obvious mistakes.
Thank you very much
1 Like
Thanks for sharing this, @scampus!
Of the things you listed, changing the settings on PostgreSQL to allow remote access without adding a much stronger password to the database was the likely root cause. I've filed an issue at https://github.com/opendatakit/docs/issues/1037 so we can warn other users.
@yanokwa thanks for opening the issue, I will keep on eye on it. The best thing would be to strengthen the password as long as this change doesn't affect the functionality of Aggregate. In addition it would be good to secure PostgreSQL remote access.
https://www.digitalocean.com/community/tutorials/how-to-secure-postgresql-against-automated-attacks suggests a step by step procedure, but I am still having difficulties with it.
Thanks again
Thanks for all the feedback you're giving us, @scampus! These incidents are very unfortunate, but we can learn so much from them to make ODK better!
It would be great to add to our docs any good advice the DigitalOcean team has given you in response to your issue. If you feel you can share any piece of information, link or guide, feel free to add it to @yanokwa's issue at https://github.com/opendatakit/docs/issues/1037
@ggalmazor thanks for your advice. Following your suggestion I added a comment to @yanokwa's issue with the reply that the DigitalOcean team gave to me.
3 Likes