Introduction
This article is for anyone aiming to connect to a production environment PostgreSQL database from a locally run Strapi CMS. While this approach is not considered best practice, it can be useful in scenarios where time is limited, complexity is moderate, and there is little risk. It’s important to note that working with SQLite for content type schema building and adding content in the production environment is recommended. However, in situations where speed is crucial, this guide provides steps to establish a connection.
My Environment
In my setup, I have a DigitalOcean VM (Droplet) hosting both the PostgreSQL database and the production Strapi app. On my local machine, I’m using Linux Ubuntu 22.04.
Prerequisites
Before proceeding, make sure the .env file in your Strapi app contains the correct environment variables. To ensure accuracy, I logged into my VM, copied the generated .env file from the app setup, and pasted it locally. This guarantees that the database name, host, user, and password are correct. Additionally, verify that the config/database.js file in your Strapi app is correctly configured to point to the appropriate environment variables or default to the desired values if no environment variables are set.
Solving Issue #1: Knex Timeout
One common issue you might encounter is the Knex timeout error. This typically occurs when TCP connections to port 5432 (the default PostgreSQL port) are not enabled for your local IP address. To resolve this, you need to ensure that your firewall allows incoming connections on port 5432 from your local IP address. How you do this depends on the firewall manager you are using.
Solving Issue #2: Connection Refused Error
If you encounter the “ECONNREFUSED” error despite enabling the connection, there is another step to consider. By default, PostgreSQL databases only allow connections from the localhost IP address. To allow connections from other IPs, modify the postgresql.conf file. Locate the postgresql.conf file on your system (e.g., /etc/postgresql/13/main/postgresql.conf on Ubuntu) and ensure that the listen_addresses setting includes the desired IP addresses or * to allow connections from any IP. Restart the PostgreSQL service for the changes to take effect.
listen_addresses = 'localhost, 192.168.0.100'
# or
listen_addresses = '*'
Solving Issue #3: pg_hba.conf Entry for IP
You may also encounter the “no pg_hba.conf entry for IP” error. This error is related to the pg_hba.conf file, which specifies access rules for PostgreSQL. To address this, locate the pg_hba.conf file on your system (e.g., /etc/postgresql/13/main/pg_hba.conf on Ubuntu) and add an entry to allow connections from your IP address. Make sure to document the entry with a comment for reference. Restart the PostgreSQL service for the changes to apply. Find the document, read it, it’s helpful, and then at the bottom of the file add your new rule, you’ll see that you will have to be specific about which ip, db, user, and method are used to connect. It is a layer of security that keeps you from making dumb mistakes. This is how a good rule might look:
# IPv4 remote connections for your-strapi-db-user
host your-strapi-db your-strapi-db-user {your ip}/32 md5
Final notes
By following these steps, you should be able to establish a connection between your locally run Strapi CMS and the production PostgreSQL database. Remember that this approach is not the recommended best practice, but it can be a viable option in certain situations. Be mindful of security considerations and explore alternatives like SQLite for local development and content-type schema building. If you still have to connect remotely to your production environment database, then make sure to keep as much security at the psotgresql.conf and pg_hba.conf layer.
Top comments (0)