A Five-Step Tutorial as an Antidote to Incorrect Documentation
Or my angry sub-subtitle: ‘Yet again, an issue with documentation…’
Google Sheets + PostgreSQL — Nice!
I’m working on an app that currently needs a lot of database maintenance as we are in the data collection and cleaning phase. I had heard about Zapier many times, and even had an account for the longest time, but never had used it. So, when I heard that they have a ‘Zap’ to connect Google Sheets to PostgreSQL, creating and editing rows and writing them to the database, I figured I would give it a try.
However, I was quickly led astray by incomplete and simple wrong Zapier documentation and Stack Overflow answers.
In the end, the correct configuration is five simple steps. I hope these steps can spare you from the pain I experienced, because there aren’t many things that frustrate me more than poor documentation, which just wastes everyone else’s time.
***As of April 2020, those were the steps I took to get a working connection, with the newest version of PostgreSQL at the time, 12.2. I assume these steps are basic enough that they are backwards-compatible to the oldest currently supported PostgreSQL version, which is 9.5. (Supported until February 11, 2021)
1. Edit Your postgresql.conf File to Listen on All Addresses
On normal Linux machines, the
postgresql.conf file should be located at the following location:
or, for example, if you have an older version like 9.5:
You can also find this file path from inside a PostgreSQL command line with:
postgresql.conf file, uncomment the
listen_addresses line (the first setting in that file) such that it reads:
listen_addresses = '*'
This ensures PostgreSQL is listening for connections on all ports.
***Note that you have a tool like Uncomplicated Firewall, aka command-line command
ufw (or any firewall for that matter), you will need to allow connections on port 5432. (With
ufw for example, that is
ufw allow 5432 ). You can check if your port is open with this handy Open Port Check Tool.
2. Create Your PostgreSQL User and Grant Permissions
Considering this user would be used by Zapier, I named the PostgreSQL user
zapier as well. Makes sense right? Note that you do NOT need to create a Linux user for this connection to work. (In the background, Zapier is using
psycopg2 — a PostgreSQL database adapter for Python, which connects directly via a PostgreSQL URI, skipping the SSH layer entirely)
Note that the specified PostgreSQL command in the Zapier documentation (which you need to venture all the way to the MySQL portion anyway just to find) does not work! The
'user'@'localhost' (or whatever host or IP) syntax does not work in PostgreSQL! The correct commands are as follows:
Login in as the
postgres user (or whatever your root PostgreSQL user is):
psql -U postgres
If you haven’t created the user yet, do so now:
CREATE USER zapier WITH PASSWORD 'somesuperstrongpasswordhere'
Connect to the database your target table is in:
Grant the proper permissions to the Zapier user you just created — in my case, user
GRANT INSERT, UPDATE, SELECT ON your_table_here TO zapier;
3. Add a Custom Entry to Your pg_hba.conf File
This is where the documentation fails. Zapier simply states:
For PostgreSQL, you’ll need to configure the server to accept logins from remote IPs (in
pg_hba.conf), and create a user for Zapier to use.
Ok, great thanks guys. 😞 No help at all.
They of course don’t provide an example. Well, I will!
First off, your
pg_hba.conf file should be located in the same folder, for version 12 again, as an example:
or for 9.5 as an example:
again, you can always get this file directly from a PostgreSQL command-line connection by issuing:
As we see in the
pg_hba.conffile, the order of the elements is written in a commented line as such:
# TYPE DATABASE USER ADDRESS METHOD
Zapier at least tells us the IP at which it will always access the database from,
If you know the name of the table you want Zapier to access, we can compose the line we need in
pg_hba.conf. However, there is a critical step here that you need to include. We want exactly this IP and this IP only, so we must at a
/32 to the end of the IP. See the PostgreSQL pg_hba.conf documentation under
address for more details. So our
pg_hba.conf line appears as follows:
# Access for zapier user host your_table_here zapier 18.104.22.168/32 md5
Note here that
host is literally the word
host. You only need to modify the table name
your_table_here , and
zapier if you’ve defined a different user for Zapier to use.
4. Restart your PostgreSQL instance
sudo service postgresql restart
for the changes we made in
pg_hba.conf to take effect.
5. Fill Out Information in Zapier PostgreSQL Form
Once arriving at the form that Zapier prompts you with for your PostgreSQL information, you should be able to enter all the server information and user credentials, and the connection should work right away! No cryptic error messages.
🍺 Cheers all,