Hi,

I use ssh-tunneling to forward the hosts postgres database port (5432) to a local port (5555) on the client machine:

Code: Select all

ssh -f -N -L 5555:localhost:5432 -l ssh-username hostname

I can connect to and work on the (already existing) database using the postgres client psql

Code: Select all

psql -h localhost -U db-username -p 5555 db-name

(and also with R using the RPostgreSQL package), however I had no luck with Kexi yet. The kexi-postgresql-driver is installed. These are the connection settings in Kexi:

* Engine: PostgreSQL
* Local server
* Hostname: localhost
* Port: 5555

If I click Test Connection the short error message is

Error during starting temporary connection using "template1" database name.

The detailed error message is

Message from server: could not connect to server: Datei oder Verzeichnis nicht gefunden Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5555"?
Server result: 0

If I disregard the failed test, save the connection and try to click the Next button in the Open Project screen, nothing visible happens. If I try to create a new database using the connection the old error occurs:

Error during starting temporary connection using "template1" database name.

The dabase user can create new databases via psql:

Code: Select all

CREATE DATABASE dbname;

I will test later if I can access a postgres database with database and kexi running on the same machine.

Kexi version is 2.5.2.

Hello HmpfCBR. Perhaps you're using local UNIX domain socket "/var/run/postgresql/.s.PGSQL.5555" while you need to use network socket, even if you connect to localhost. To disable using local socket file, while editing your database connection in Kexi, in Add New Database Connection dialog, Details page, uncheck Use socket file instead of TCP/IP port checkbox and save the connection.

Hi jstaniek,

Thanks for the tip. Unfortunately, the short and detailed error messages stay the same, regardless if the Use socket file instead of TCP/IP port checkbox is checked or unchecked (I did save the connection inbetween):

Message from server: could not connect to server: Datei oder Verzeichnis nicht gefunden Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5555"?
Server result: 0

I've been dealing with something very close.

Check out your postgresql.conf file. The full path on my system, it is /etc/postgresql/9.1/main/postgresql.conf

Try adding the following line.

listen = '*'

There should also be some additional reading in the file for more settings and such.

-=/\/=-

Oh did not see that there was another answer to this one.

I guess you mean listen_address instead of listen (the latter is not a valid cofiguration paramter in postgresql.conf which gives an error message when starting the server. Changing listen_adress to

Code: Select all

listen_address = '*'

does not change the behaviour desribed above.

Same problem. Looks like kexi uses 'template1' as --dbname. Is there any way to use another database name?


Note: I use

Code: Select all

docker run postgres

to run DB server

belkka wrote:Same problem. Looks like kexi uses 'template1' as --dbname. Is there any way to use another database name?

Note: I use

Code: Select all

docker run postgres

to run DB server

Hi,
Unfortunately we seem to have that hardcoded for now (in a KDb driver's source code, ALWAYS_AVAILABLE_DATABASE_NAME field). It would be useful indeed to have it part of the connection settings. Current workaround is to build the KDb after changine that value of ALWAYS_AVAILABLE_DATABASE_NAME.

That's unfortunate, sorry. If there are other points that need flexibility, feel free to request as well.
Best regards.

Created a wish: https://bugs.kde.org/show_bug.cgi?id=419932

Anyone willing to implement this?

As it was not so obvious and I found this error asked about in many places, I hereby summarize how you can connect to a PG DB (version 12) using kexi (as of 2020-04-13, Debian Buster Kexi version is : 1:3.1.0-4).

- First thing first, it is logical to try to reach DB "template1" as it should exists in any normal PG installation.
- "suadmin" is a super user with valid password, login and connection permission.
- Everything pertains to PG configuration file : pg_hba.conf … except what pertains to postgresql.conf

* Apparently, kexi's PG driver is only able to connect using the "md5" PG method, not with the "peer" one, even if kexi and PG srv are on the same machine
! The position of the connection checkbox in the connection 2nd tab ("Details") is completely ignored in my version ! :

Code: Select all

local   all   suadmin   peer


Breaks with the usual error message

* once you know that, you'll have to make your 2nd significative line look like that IF YOU CONNECT LOCALLY :

Code: Select all

local   all   suadmin   md5


Locally works !

* or as follow, IF YOU CONNECT REMOTELY :

Code: Select all

host   all   suadmin   samenet   md5


Remotely works !

NB: If your security requires to have one and only one machine able to remotely connect to the PG server, replace 'samenet' with it's IP address, ie: 192.168.1.197/32 (and of course, do not forget to also set: listen_address = '*' into posgresql.conf on the remote server or whatever IP address you need if e.g. you have a PG server with several networks I/F).

My ¢2