José Ribamar [Profile]

Executive Officer at Square Cloud

Article written by José Ribamar in 19/03/2024.


If you want to skip the article and download now, Click here

​
What is pgAdmin4?

pgAdmin4 is the most popular and advanced open-source administration and development platform for PostgreSQL and the EDB Advanced Server. It can be used on Linux, Unix, macOS, and Windows.

PostgreSQL, also known as “Postgre”, appeared in 1982 in the USA, is one of the most famous relational DBMS in the world, capable of storing data with consistency and high performance, currently used by large companies such as Apple, Instagram, gandi.net, FlightAware, Caixa Econômica Federal and several other services and banks, second only to MySQL.

​
How did I get this idea

Without a doubt, PostgreSQL is one of the databases I most enjoy working with, both the performance and the set of tools available are impressive, but there is one thing I’ve always wanted to do: Perform queries anywhere, without having to download a program additional (pgAdmin) for this.

So I thought that a way out of this would be to rent a virtual machine or a VPS, and host pgAdmin on it, but that would go against the concept I was looking for with “Access pgAdmin from anywhere without complications”, as I would have to deal with proxies , monitor system status, manage SSL certificates, take extra security precautions - which for a small pgAdmin service, is not worth the effort and cost

​
How I Decided to Use Square Cloud

I already had the idea, I just needed to implement it, as VM and VPS wouldn’t make up for it, I remembered the ease that Square Cloud offers, and I started thinking about hosting pgAdmin on Square Cloud, and I saw several advantages in that:

These are just some of the advantages I listed, there are infinite others, such as easy integration with GitHub for example, and the most impressive thing is the fact that Square can do all of this, even though it is not focused on hosting pgAdmin sites, showing a Square Cloud’s ability to operate in the most different scenarios at a very low cost.

​
Starting with development

Already knowing what I’m going to do and which platform I’m going to use to host it, all I had to do was develop it, so I woke up on 03/12/2024 at 5 am determined to get this off the ground.

​
1. Deciding the installation method

The first thing I looked for was the ways to install pgAdmin, I analyzed all the pgAdmin installation options (Container, Windows, macOS, APT, RPM and Python) and found the ideal option: Installation via Python, and these are the reasons:

  • Simplified installation: The final code is only 19 lines long
  • Greater possibilities: We can run with gunicorn running only 1 bash line
  • Familiarity with the configuration file: The pgAdmin configuration file is in Python
    • Installing with python, we have a 100% Python project
    • This helps who wants to modify later, cause the only prerequisite is known Python

The first thing I did was a Discord BOT in Python to run command lines on the Square Cloud and find out the location of some important things for this to work, like the pgAdmin installation location, commands to run pgAdmin, and another things.

​
2. Creating a Discord Bot to execute commands on terminal

Because Square Cloud is a production-focused platform, the terminal is not interactive, only showing logs (Ideal for production environments)

But in my case I needed an environment like Square Cloud to develop the project, so I decided to develop a simple Discord bot to be able to execute commands in the Square Cloud terminal, and I will share the code with you below:

​
3. Discovering pgAdmin Path

With the Discord BOT, I was ready to run commands on the Square Cloud, so the first thing I did was look for the pgAdmin installation directory, running a command to search for files in Ubuntu: find / -name "pgadmin"

  • In this command, find is the command to search, / parameter is the path to start search, and -name "pgadmin" specifies i want to search by files/directories who contains pgadmin on name

After running this, the BOT replied to me with the pgAdmin internal directory

Now, knowing that the installation directory was /application/.local/lib/python3.11/site-packages/pgadmin4, I ran the ls command in that directory, to find out what files were there

And this command revealed some interesting files to me

  • setup.py: I searched on Google and found this page explaining this file, it is used to perform initial configuration and to manage (add, update and remove) users
  • config.py: As the name suggests, it is the pgAdmin configuration file
  • pgAdmin4.py: I don’t know what it is yet, but it seems important

My objective by running this command was to discover the main pgAdmin file, I started to suspect that this file was pgAdmin4.py, so to confirm, I ran the command cat /application/.local/lib/python3.11/site- packages/pgadmin4/pgAdmin4.py to view the contents of this file

When I ran it, I came across the text “This is the main application entry point for pgAdmin 4.”, and with that, I already had valuable information to run pgAdmin4 on Square Cloud:

  • PGAdmin Path on Square Cloud:
    • /application/.local/lib/python3.11/site-packages/pgadmin4
  • PGAdmin entry file:
    • /application/.local/lib/python3.11/site-packages/pgadmin4/pgAdmin4.py
  • PGAdmin configuration file:
    • /application/.local/lib/python3.11/site-packages/pgadmin4/setup.py

So I tried creating a second project to run the main pgAdmin file and see what happens

​
4. 1st attempt to run pgAdmin4

I finally made the first attempt, and ended up receiving an error related to access permission:

ERROR  : Failed to create the directory /var/lib/pgadmin:
          [Errno 13] Permission denied: '/var/lib/pgadmin'
HINT :   Create the directory /var/lib/pgadmin, ensure it is writeable by
         'square', and try again, or, create a config_local.py file
         and override the SQLITE_PATH setting per
         https://www.pgadmin.org/docs/pgadmin4/8.4/config_py.html

​
Solving “Permission denied” Error

This happens because the only directory that your application should modify is its own directory, so I did what the error message asked and saw about the pgAdmin4.py configuration file and how I could solve this problem with it

Reading the page about the pgAdmin4 configuration file, the first thing I noticed is that the config.py file should not be modified, it is for reference only, instead of modifying it we should create a config_local.py file in the pgAdmin4 root directory

Reading about the parameters that I would have to modify, I saw that there would be 3: DATA_DIR, SQLITE_PATH, SERVER_MODE, and (optional) LOG_FILE, and this was the 2nd version of the project:

​
5. 2nd attempt to run pgAdmin4

On the second attempt, I already made an important advance, I solved the access problems, storing everything in the application directory! Now, trying to run again, I received the error below:

​
Solving “EOF when reading a line”

Reading the error, I saw that this happens because pgAdmin, on first startup, asks you to enter an email and password to create the first user, system administrator - But as I was using the os.system() method to run the code, I get the error EOFError: EOF when reading a line

Having this information in hand, I searched for other ways to run the application, at first I thought about using subprocess, but upon further research I saw that it is possible to define this using environment variables in an .env file, so I went for the third attempt, now setting a default username and password!

​
5. 3rd attempt to run pgAdmin4

And now, we finally have the first error-free log!

Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050 in your browser.
2024-03-13 21:33:12,186: WARNING	werkzeug:	Werkzeug appears to be used in a production deployment. Consider switching to a production web server instead.
 * Serving Flask app 'pgadmin'
 * Debug mode: off

At this point, theoretically pgAdmin was already working and accessible! But, looking at the message, despite there being no error, there are 2 things to note:

  • PGAdmin was opened on port 5050, but whe need to open on HTTP port (80)
  • The warning WARNING werkzeug: Werkzeug appears to be used in a production deployment. Consider switching to a production web server instead.

With these warnings, I realized that, running with Werkzeug I could have problems regarding the performance and security of pgAdmin, and this should not happen under any circumstances, and also that we would have to open on port 80

​
Solving Werkzeug and wrong port

With that, I looked for a way to solve the 2 problems at once, because in a way they are related

So, researching the solutions that the pgAdmin documentation suggested to me, I decided to follow the solution of opening a webserver with gunicorn on port 80, and forwarding these requests to pgAdmin4

Of course, the code above is just an example, so I developed my own code to do this, and this is the final version:

​
Final attempt to run pgAdmin4

Running this final version, everything finally went well! It was possible to access pgAdmin web, create databases, everything worked perfectly!

So, I setup a custom domain, and when I tried to log in again I simply received an empty page! 😐

Opening devtools and analyzing the requests further, I noticed that 3 of them returned the status 400 Bad Request, and in the description there was the message “The CSRF tokens do not match”

And so, it was possible to access pgAdmin, but only once time 🙁

​
Troubleshooting with CSRF

Searching a lot about this CSRF message, I saw 2 useful pages [1] [2], and they suggested some things to me:

  1. Close the tab before i start the pgAdmin (maybe because session storage)
    • I tried this, but without any result
  2. Add ENHANCED_COOKIE_PROTECTION=False on config_local.py
    • I did this, but the problem persists
    • And this appears to represent a security flaw, we are disabling some protection
  3. Add WTF_CSRF_CHECK_DEFAULT=False and WTF_CSRF_ENABLED=False to config_local.py
    • I did this, and now works!
    • But that would basically be sweeping the dust under the rug.
    • Doing so would pose serious security risks as we would no longer be validating CSRF token, allowing CSRF attacks to happen!
  4. Check multiple instances of pgAdmin4
    • I checked, but this was impossible to happen on Square Cloud.
    • And this is the reason to set only 1 worker on gunicorn!
  5. Add PGADMIN_LISTEN_PORT=8082 to config_local.py
    • That didn’t solve it, and honestly, what’s the difference between 5050 and 8082?
  6. Clear the cookies
    • This didn’t work, but I understand this solution, because “if the tokens don’t match, removing the invalid token should solve it”

This left only 3 alternatives:

  • Change proxy mode on Cloudflare - Proxied => DNS Only
  • Configure the Cache Level to No Query String

So, the only solutions left were: Changing the redirect to DNS Only and setting the cache level to No Query String, but both have problems, mainly changing the mode to DNS Only, which poses security problems, problems of resources, is unfeasible.

So I analyzed the entire connection flow and connected all the dots in search of a solution, creating the following diagram:

With this diagram, I found that the problem was between sending the request with the received CSRF token and its interpretation by the server from the 2nd connection onwards.

I also found that the only solutions I had left were to change the mode to DNS Only and change the cache level to No Query String, which also made me think that changing the redirect mode to DNS Only would also remove Cloudflare’s cache, by it, it was a solution!

So I came to the conclusion that the problem was some incompatibility between the Cloudflare cache and the CSRF token in this case, as it delivered the token for the first connection, even though it was the 1000th connection!

So I contacted Square Cloud’s Chief Technology Officer [João Otávio Stivi], as he has control of the cloudflare squareweb.app , and after discussing and testing several solutions for this, we came to the conclusion that the best solution would be to bypass the cache for all requests from subdomains that have -nocache or nocache-, now working with pgadmin-nocache, nocache-pgadmin-something and a lot variations!

​
Download pgAdmin4 for Square Cloud

If you want to see the final result of the project, or even download and host your own pgAdmin4 on Square Cloud, you might want to visit my repository by clicking here

Note that this project has no relationship with Square Cloud. If you have any problems, use the Issues, Pull Requests and similar tools in the repository.
Data Validation with PydanticPractical Guide to Flask