PostgreSQL requirements - Unable to find DB driver for postgres

[martinbehrens]

Issue

lightningd fails with Unable to find DB driver for postgres://user:pw@fqdn:5432/{dbname} when trying to connect to a remote sql db.

What is required to satisfy lightningd in this case? Which psql version is required, recommended, tested? What is required to make it work with psql12?

Environment

• OS:

CentOS Linux release 8.2.2004 (Core)
Linux 4.18.0-193.14.2.el8_2.x86_64

• Psql packages installed:

postgresql12-12.3-5PGDG.rhel8.x86_64
postgresql12-odbc-12.02.0000-1PGDG.rhel8.x86_64
postgresql12-libs-12.3-5PGDG.rhel8.x86_64
postgresql12-devel-12.3-5PGDG.rhel8.x86_64

• PATH:

[user@host]$ env
PATH=/home/user/.local/bin:/home/user/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[user@host]$ whereis psql
psql: /usr/bin/psql /usr/local/bin/psql /usr/share/man/man1/psql.1
[user@host]$ whereis lightningd
lightningd: /usr/local/bin/lightningd

Steps to Reproduce

1. Clone, compile and install the project
2. Create a c-lightning config file with a wallet=postgres://user:pw@fqdn:5432/{dbname} string pointing to a remote database endpoint.
3. Install PostgreSQL12 with:

yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm -y
dnf -qy module disable postgresql
yum install postgresql12 postgresql12-libs postgresql12-devel postgresql12-odbc -y

4. Execute /usr/local/bin/lightningd and observe the error thrown:

Unable to find DB driver for postgres://user:pw@fqdn:5432/{dbname}

[cdecker]

Am I correct that the order was the following:

1. Compile c-lightning from the source
2. Install the postgresql libraries (including the development headers and libraries)

In that case it seems like the build has looked for the postgresql development headers when calling ./configure, didn't find them and excluded the driver from being built. Could you try recompiling (including the ./configure call before make) and see if it picks up the postgresql libraries when they are installed first?

[martinbehrens]

You are correct and thanks for picking this one up. I tried to reconfigure and recompile already. This is the full list of psql related packages installed ...

postgresql12-12.3-5PGDG.rhel8.x86_64
postgresql12-odbc-12.02.0000-1PGDG.rhel8.x86_64
postgresql12-libs-12.3-5PGDG.rhel8.x86_64
postgresql12-devel-12.3-5PGDG.rhel8.x86_64
libpqxx-devel-7.1.2-1.rhel8.x86_64
libpqxx-7.1.2-1.rhel8.x86_64
libpq5-12.3-10PGDG.rhel8.x86_64

while package postgresql12-devel.x86_64 contains development header files and libraries.

Now, executing a make clean && ./configure --enable-developer && make plus make install unfortunately nothing changes. Most most most likely due to checking for postgres... no while configuring. It seems configure doesn't pick up the libraries installed.

[fiatjaf]

You may have a library in /usr/include/libpq-fe.h but you need it on /usr/include/postgresql/libpq-fe.h (because for some reason c-lightning expects it to be imported with #include <postgresql/libpq-fe.h>).

This should fix it:

sudo ln -s /usr/include/libpq-fe.h /usr/include/postgresql/libpq-fe.h

[martinbehrens]

You may have a library in /usr/include/libpq-fe.h but you need it on /usr/include/postgresql/libpq-fe.h (because for some reason c-lightning expects it to be imported with #include <postgresql/libpq-fe.h>).

This should fix it:

sudo ln -s /usr/include/libpq-fe.h /usr/include/postgresql/libpq-fe.h

Nop, but a sudo mkdir -p /usr/include/postgresql && sudo ln -s /usr/pgsql-12/include/libpq-fe.h /usr/include/postgresql/libpq-fe.h did, so thanks @fiatjaf and @cdecker ! ;)

[cdecker]

Hurray for portable include paths. @fiatjaf any idea how we can make sure we pick the right inclusion path, independently of the distribution? Sounds a bit like a job for yet another ./configure` check that changes the inclusion path.

[fiatjaf]

Oops, sorry, I don't understand these library/C things at all! I found this workaround for me by just experimenting and fiddling around.

Now I realize that by revealing the path in which my libpq-fe.h was I gave a hint on which OS I'm using, terrible OPSEC 😪.

[cdecker]

Ok, looks like we should just add a ./configure step that locates the header files out of a list of possible locations and adds it to the include paths. Does that make sense @rustyrussell or is there a more elegant way of doing this?

[cdecker]

Found one more nugget: the official docs mention the pq_config tool which can be used to find the path:

$ pg_config --includedir
/usr/local/include

I'll try to get that working in the configure script.

[martinbehrens]

Works but may have a catch: In my Centos 7 and 8 instances /usr/pgsql-12/bin/ (where pg_config resides) is not in PATH per default, so configure picked up the location only after I manually exported it.