search

powa-team / powa-web

PoWA user interface
http://powa.readthedocs.io/
73 stars 31 forks source link

Use SSL for database connection #154

Closed okohll closed 2 years ago

okohll commented 2 years ago

When trying to log in, I get

[E 220412 22:18:18 user:20] (psycopg2.OperationalError) SSL error: certificate verify failed
    FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "[myusername]", database "[mydatabase]", SSL off

    (Background on this error at: https://sqlalche.me/e/14/e3q8)

in the log.

My pg_hba.conf allows only SSL connections, is it possible to get POWA to use one? I don't wish to allow non-secure connections to my databases.

rjuju commented 2 years ago

Hi,

I tried to reproduce the behavior with this pg_hba.conf:

hostnossl    powa            all             127.0.0.1/32          reject
hostssl        powa            all             127.0.0.1/32          trust

but I don't have any problem connecting the UI to the repository database, and I can see that the connection is using SSL by checking the pg_stat_ssl view. Same if I only have the "hostssl" line, without anything else accepting connections.

Can you give more detail for you setup? Is it connection to the repository database that doesn't work? What version of powa-web and psycopg are you using?

rjuju commented 2 years ago

Can you also share you powa-web.conf file and your pg_hba.conf?

okohll commented 2 years ago

Ah there's probably something basic I've missed then. In fact, after a bit more testing of various things, I've found that

psql "postgresql://myusername@127.0.0.1:5432/mydatabase?sslmode=require"

gives the error

psql: error: SSL error: certificate verify failed

so it looks like there's a certificate problem, though Letsencrypt seems to think it's fine and doesn't need renewing.

It looks like it's not POWA anyway - the same command works on a live server (this is a test one).

Thanks Oliver