This article is dedicated to all the Excel heroes out there who want to integrate and use data stored in PostgreSQL. This time we’ll cover all the steps needed to do so by utilizing Npgsql, which is an open source ADO.NET Data Provider for PostgreSQL. To learn more about this versatile driver, check out its main project page or directly jump into the code base.
Table of Contents
Let me begin with the needed requirements to follow up on this quick tutorial accordingly. Apart from an accessible PostgreSQL server, an appropriate Excel version or license is required to connect to PostgreSQL via Npgsql and not through the common ODBC protocol. To verify your license, open Excel and go through the following menu entries: Data – Get Data – From Database. The resulting container should include an entry for PostgreSQL. Check out figure 1 to see how this looks in my environment.
What else is needed to continue? Please find below a summary of my environment.
To use Npgsql as a database provider for Microsoft Excel, the driver must be installed in the Global Assembly Cache first. To make this task as smooth as possible, we’ll pick one of the available but older MSI installers of Npgsql to outsource this job.
First, we download the driver from https://github.com/npgsql/npgsql/releases/download/v4.0.10/Npgsql-4.0.10.msi and install the package on Windows. The driver should now be available for Excel too. Let’s verify this:
If everything goes well, Excel will continue and ask for connection details and credentials.
Let’s start easy and connect from Excel to PostgreSQL without using SSL. I recommend testing the unsecured connection via psql first before moving on to Excel. To do so, we hand over the parameter sslmode=disabled on client the side as follows:
|
1 2 3 4 5 6 |
user@fn-gis-2:~$ psql 'dbname=perf user=postgres host=10.0.0.217 sslmode=disable' Password for user postgres: psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1)) Type 'help' for help. perf=# |
Great – this works out. In Excel, we step in, enter connection details and credentials, and finally try to connect. The upcoming image series documents these steps briefly:
Uuups – this does not seem to work. The error message suggests that Excel connects to PostgreSQL with SSLMODE enabled by default. Any chance to change this parameter? We try our luck in Excel’s Data Source Settings (Data Data Source Settings). From here on, we can disable the SSLMODE of our connection by unchecking Encrypt connections. Image 4 visualizes this task in detail.
Our next connection attempt (see image 5) succeeds and brings up Excel’s navigator pane designed for further data processing. Mission accomplished.
Let’s dig deeper and improve our setup by securing connections between Microsoft Excel and PostgreSQL. I expect some basic knowledge regarding PostgreSQL’s SSL capabilities.
To refresh your knowledge, I highly recommend PostgreSQL’s own documentation and a blog post from my boss, Hans Schönig.
To keep it simple, we will generate a self-signed certificate on the server and use it as a trusted root certificate on the client.
The overall process consists of the following steps:
Firstly, we generate our self-signed certificate and store it in PostgreSQL’s data directory, where the system will look after it by default. We generate the certificate and key as user postgres or use chown to change ownership accordingly.
|
1 2 3 4 5 |
postgres@fn-gis-2:~/16/main$ openssl req -new -x509 -days 90 -nodes -text -out server.crt -keyout server.key -subj '/CN=10.0.0.217' ...+.....+..........+...+..+......+......+....+.....+.+......+..+.+.....................+...+..+....+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*....................+............+.........+.+........+.+...........+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ..+......+.+...+..............+.+.....+.+..............+......+.+...+..+..........+..+...+.......+...........+.........+...+...+.+...+..+.+...+.....+.......+.....+...+.......+........+.+.....+......+.............+......+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*....+...+.+.....+.+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*....+.....+.+.....+.........+....+...............+...+............+...+.....+....+.....+...+.+.....+.......+...+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ----- postgres@fn-gis-2:~/16/main$ chmod og-rwx server.key server.crt |
Next, we adopt our PostgreSQL configuration in postgresql.conf, point to our files, and finally restart the database server.
|
1 2 3 |
ssl = on ssl_cert_file = 'server.crt' ssl_key_file = 'server.key' |
|
1 |
root@fn-gis-2:/var/lib/postgresql/16/main# sudo systemctl restart postgresql@16-main |
Before deploying our certificate to Windows, let's quickly verify its correctness by connecting via psql.
To use our server certificate as a trusted root certificate, we copy and rename server.crt as root.crt to directory .postgresql, where psql will look after certificates by default.
|
1 2 3 |
postgres@fn-gis-2:~$ mkdir ~/.postgresql postgres@fn-gis-2:~$ cd .postgresql/ postgres@fn-gis-2:~/.postgresql$ cp /var/lib/postgresql/16/main/server.crt root.crt |
Finally, we test the connection.
|
1 2 3 4 5 6 7 |
postgres@fn-gis-2:/home/user/.postgresql$ psql postgresql://postgres@10.0.0.217:5432/postgres?sslmode=verify-full Password for user postgres: psql (16.1 (Ubuntu 16.1-1.pgdg22.04+1)) SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off) Type 'help' for help. postgres=# |
Amazing! The output confirms that we are connecting with SSL mode enabled. By renaming ~/.postgresql/root.crt we can cross-check our configuration.
|
1 2 3 4 5 |
postgres@fn-gis-2:~/.postgresql$ mv root.crt _root.crt postgres@fn-gis-2:~/.postgresql$ psql postgresql://postgres@10.0.0.217:5432/postgres?sslmode=verify-full psql: error: connection to server at '10.0.0.217', port 5432 failed: root certificate file '/var/lib/postgresql/.postgresql/root.crt' does not exist Either provide the file, use the system's trusted roots with sslrootcert=system, or change sslmode to disable server certificate verification. postgres@fn-gis-2:~/.postgresql$ |
We are still not tired and copied our certificate root.crt to Windows. The certificate can be added to the trusted root certification store as follows:
The upcoming image series documents this task.
Now it’s time to revert our PostgreSQL connection settings in Excel and enable “Encrypt connection” again. That’s it. One more time, we open Excel’s data navigator (see chapter on unsecured connection) to verify that everything works as expected.
Still not enough? As homework, force your clients to use SSL by adopting pg_hba .
Today we connected Microsoft Excel to PostgreSQL via Npgsql. This task sounds easy in the first run, but there are some stumbling blocks you might encounter. This post guides you step-by-step through this process and subsequently enables you to concentrate on your Excel magic instead of dealing with frustrating connectivity issues.
+43 (0) 2622 93022-0
office@cybertec.at