We use Ubiquiti's CRM for billing in our company.

We needed to access the database to extract a bit of information not included in the API.

I figured out how to do this.

HERE IS A PDF Access UCRM Database.pdf (87.15 kb)

Let me start with:

THIS IS A VERY DANGEROUS THING TO DO.  DO NOT DO THIS UNLESS YOU ARE COMPLETELY COMFORTABLE WITH DATABASE ACCESS AND POSSIBLY LOSING YOUR DATA!

Now that the warning is out of the way, here we go.

1. Install Apache2: sudo apt install apache2 <-- this will throw an error.  We will fix this in the next 2 steps
2. Change Apache2 port number in: sudo nano /etc/apache2/ports.conf
3. Start Apache2: sudo service apache2 start
4. Install the public key for the pgAdmin4 repository (if not done previously): sudo curl https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo apt-key add
5. Create the repository configuration file: sudo sh -c 'echo "deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
6. Install pgAdmin4: sudo apt install pgadmin4-web 
7. Configure the webserver: sudo /usr/pgadmin4/bin/setup-web.sh
8. Add IP address restriction to pgAdmin4 directory for an extra layer of security
1. sudo nano /etc/apache2/conf-enabled/pgadmin4.conf
2. Change "Require all granted" to "Require ip your.office.ip.here"
9. Restart Apache2: sudo service apache2 restart
10. Get UCRM postgres DB IP and password:
1. container=`sudo docker ps | grep postgres | awk '{print $1;}'`; sudo docker inspect "$container" | grep '"IPAddress": "172\|UCRM_POSTGRES_PASSWORD'
2. sudo docker ps
3. sudo docker inspect ID xxxxx  <---- WHERE xxxxx is the ID of the docker referencing postgres
4. Look towards top for UCRM DB password
5. Look towards bottom for docker IP
11. Open web browser to: http://ucrm.ip.address.here:customPort/pgadmin4
12. Create new "Server"
1. Give it a name: "UCRM"
2. On the Connection tab:
1. Hostname: The IP from Step 10.3
2. Username: ucrm
3. Password: The password from Step 10.2
4. Save Password: yes.
13. If all goes correctly, you should have direct DB access to the database

14. DO NOT SCREW UP YOUR DATABASE!  I WARNED YOU!

15. Have fun.