LibreOffice Base Connect to postgresql

[Mike]

I understand my question is not directly related to postgresql; but,
I'm hoping someone else has used libreoffice base to connect to, and
query postgres databases.

I have a very simple database.
It resides on my notebook computer running Fedora 27.
postgres --version
postgres (PostgreSQL) 9.6.6

The name of the database is "joe" and it owned by user "joe."

I open libreoffice base and use the database wizard.
I choose Connect To An Existing Database and from the drop-down menu
choose "PostgresSQL" and click NEXT.
Then, under Connection Settings, I type in -
postgresql://localhost:5432/home/joe and click NEXT.
Then I provide the user authentication - User Name: joe

I then click FINISH and always receive error ---

The connection to the data source "New Database" could not be established.
Couldn't establish database connection to
'sdbc:postgresql:postgresql://localhost:5432/home/joe
FATAL: Ident authentication failed for user "joe"

However, joe is a configured user and owner of database "joe."
I'm simply stuck and can't figure out the cause of the failed authentication.
Is there a better/simpler/easier app I can use or try to connect to,
and manage this postgres database.
I want to expand it and rely more heavily on it, but cannot do all my
select, update, and insert via psql interface --- I constantly have to
rely on cheat sheets and command history scrolling.
Got to be a better way.

Thank you for reading.

Mike

[Carnes, Richard]

I believe using LibreOffice Base, it is specifically connecting via JDBC. You will need a JDBC driver for PG which you can find here:
http://jdbc.postgresql.org

JDBC connection string should be
jdbc:postgresql://localhost:5432/joe
Connection string format is as follows:
protocol:driver://(ip/host):(port)/(db)

Maybe this page would be worth a read:
http://www.postgresonline.com/journal/archives/8-Using-OpenOffice-Base-2.3.1-with-PostgreSQL.html

As for recommending a management app, I would have to say give pgadmin a try. I'm not a fan of pgadmin 4, but with the newer versions of PG, pgadmin 3 is getting less and less compatible since 9.2+.

Other UIs which I would say work well are Squirrel SQL and SQL Workbench, both of these depend on a JDBC driver also where PG Admin does not.

Richard Carnes

-----Original Message-----
From: Mike [mailto:***@gmail.com]
Sent: Wednesday, December 27, 2017 1:53 PM
To: pgsql-novice <pgsql-***@postgresql.org>
Subject: LibreOffice Base Connect to postgresql

I understand my question is not directly related to postgresql; but, I'm hoping someone else has used libreoffice base to connect to, and query postgres databases.

I have a very simple database.
It resides on my notebook computer running Fedora 27.
postgres --version
postgres (PostgreSQL) 9.6.6

The name of the database is "joe" and it owned by user "joe."

I open libreoffice base and use the database wizard.
I choose Connect To An Existing Database and from the drop-down menu choose "PostgresSQL" and click NEXT.
Then, under Connection Settings, I type in - postgresql://localhost:5432/home/joe and click NEXT.
Then I provide the user authentication - User Name: joe

I then click FINISH and always receive error ---

The connection to the data source "New Database" could not be established.
Couldn't establish database connection to 'sdbc:postgresql:postgresql://localhost:5432/home/joe
FATAL: Ident authentication failed for user "joe"

However, joe is a configured user and owner of database "joe."
I'm simply stuck and can't figure out the cause of the failed authentication.
Is there a better/simpler/easier app I can use or try to connect to, and manage this postgres database.
I want to expand it and rely more heavily on it, but cannot do all my select, update, and insert via psql interface --- I constantly have to rely on cheat sheets and command history scrolling.
Got to be a better way.

Thank you for reading.

Mike

________________________________

Confidentiality Notice: This email and its attachments (if any) contain confidential information of the sender. The information is intended only for the use by the direct addressees of the original sender of this email. If you are not an intended recipient of the original sender (or responsible for delivering the message to such person), you are hereby notified that any review, disclosure, copying, distribution or the taking of any action in reliance of the contents of and attachments to this email is strictly prohibited. If you have received this email in error, please immediately notify the sender at the address shown herein and permanently delete any copies of this email (digital or paper) i

[Mike]

Mr. Carnes,

Thank you for the thoughtful reply.
Grabbing the postgresql-jdbc driver and will give it a try.
I like the looks of SQL Workbench and will need to follow up with this too.

Kind regards,

Mike

On Wed, Dec 27, 2017 at 3:16 PM, Carnes, Richard

Post by Carnes, Richard
http://jdbc.postgresql.org
JDBC connection string should be
jdbc:postgresql://localhost:5432/joe
protocol:driver://(ip/host):(port)/(db)
http://www.postgresonline.com/journal/archives/8-Using-OpenOffice-Base-2.3.1-with-PostgreSQL.html
As for recommending a management app, I would have to say give pgadmin a try. I'm not a fan of pgadmin 4, but with the newer versions of PG, pgadmin 3 is getting less and less compatible since 9.2+.
Other UIs which I would say work well are Squirrel SQL and SQL Workbench, both of these depend on a JDBC driver also where PG Admin does not.
Richard Carnes
-----Original Message-----
Sent: Wednesday, December 27, 2017 1:53 PM
Subject: LibreOffice Base Connect to postgresql
I understand my question is not directly related to postgresql; but, I'm hoping someone else has used libreoffice base to connect to, and query postgres databases.
I have a very simple database.
It resides on my notebook computer running Fedora 27.
postgres --version
postgres (PostgreSQL) 9.6.6
The name of the database is "joe" and it owned by user "joe."
I open libreoffice base and use the database wizard.
I choose Connect To An Existing Database and from the drop-down menu choose "PostgresSQL" and click NEXT.
Then, under Connection Settings, I type in - postgresql://localhost:5432/home/joe and click NEXT.
Then I provide the user authentication - User Name: joe
I then click FINISH and always receive error ---
The connection to the data source "New Database" could not be established.
Couldn't establish database connection to 'sdbc:postgresql:postgresql://localhost:5432/home/joe
FATAL: Ident authentication failed for user "joe"
However, joe is a configured user and owner of database "joe."
I'm simply stuck and can't figure out the cause of the failed authentication.
Is there a better/simpler/easier app I can use or try to connect to, and manage this postgres database.
I want to expand it and rely more heavily on it, but cannot do all my select, update, and insert via psql interface --- I constantly have to rely on cheat sheets and command history scrolling.
Got to be a better way.
Thank you for reading.
Mike
________________________________
Confidentiality Notice: This email and its attachments (if any) contain confidential information of the sender. The information is intended only for the use by the direct addressees of the original sender of this email. If you are not an intended recipient of the original sender (or responsible for delivering the message to such person), you are hereby notified that any review, disclosure, copying, distribution or the taking of any action in reliance of the contents of and attachments to this email is strictly prohibited. If you have received this email in error, please immediately notify the sender at the address shown herein and permanently delete any copies of this email (digital or paper) in your possession.

[Mike]

On Thu, Dec 28, 2017 at 12:12 AM, Jayadevan M

Probably this has to do with the pg_hba.conf entries.
Please see this link
https://confluence.atlassian.com/bitbucketserverkb/fatal-ident-authentication-failed-for-user-unable-to-connect-to-postgresql-779171564.html

Dear Jayadevan,

I apologize for my late response, I have recently returned from travel
over the holidays.
I completed the suggested changes to the pg_hba.conf and am able to
connect to the postgresql database using the jdbc driver.
Thank you so much for helping me resolve the issue.
Kind regards,

Mike