Connecting to local instance of PostgreSql with JDBC

Question

I have a running local instance of PostgreSql on a linux machine. When I use psql command from the shell I success to log in without any problem. I need to connect to the PostgreSql via the JDBC, but I don't know what exactly should I pass as url parameter to DriverManager.getConnection().

It should start with jdbc:postgresql: but what's going next?

I was told by the system group that a database with was created like user name. e.g. if my user is jutky a db named jutky was created, but when I try to open a connection to jdbc:postgresql:jutky I get an error

org.postgresql.util.PSQLException: FATAL: password authentication failed for user "jutky" :(

Additional info

When I login via the psql I'm not prompted for the password, so when I try to login via JDBC I pass an empty string as a password - is it correct, or should I pass null or something?

When I type psql --help in the shell I see among the rest this line:

Connection options:
   -h, --host=HOSTNAME      database server host or socket directory (default: "/var/run/postgresql")

So I understand that I connect to PostgreSql via a socket directory, does that matters something to the URL string in the JDBC?

EDIT

First thanks for the answers.

Second: its not first time I'm using JDBC and in particular not the first time I'm connecting to the PostgreSql from JDBC, so I know the general rules and I have read the documentations. However in the described case I'm not sure how exactly should I construct the connection string if the instance is running via the socket directory and what password should I provide. Because when I login via the psql I'm not prompted for password at all.

Thanks in advance.

axtavt · Accepted Answer · 2010-12-30 14:58:39Z

34

In addition to other answers note that by default Postgres is configured to accept connections via Unix sockets with authentication based on your operating system account, that's why psql works fine and doesn't require the password.

JDBC connections are made over TCP/IP with password authentication, so you need to modify pg_hba.conf accordingly. For example, this line allows TCP/IP connections from the same machine to all databases for all users with password authentication:

host    all         all         127.0.0.1/32          md5

After adding this line jdbc:postgresql:databasename should work.

EDIT: You can't create a JDBC connection over Unix socket since PostgreSQL JDBC driver can only work over TCP/IP. The password you use when creating JDBC connection is the password assigned to your user. If you don't have it, you can assign it, for example, using ALTER USER command. See 19.3. Authentication methods.

See also:

19.1. The pg_hba.conf file

edited Dec 30, 2010 at 14:58

answered Dec 30, 2010 at 12:31

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jutky Over a year ago

I've asked the system group to check that issue. Thanks for the advice.

user330315 Over a year ago

Also make sure that listen_address = '*' is enabled in postgresql.conf. Otherwise TCP/IP connection will not be possible at all (which is what JDBC is using, even with a local connection)

jutky Over a year ago

Thanks a lot, indeed the issue was solved by configuring the PostgreSql.

Rajan Bhavsar Over a year ago

@axtavt please help me to edit the configuration file of postgresql i am not able to resolve the Connection Refused Error.

Avi Over a year ago

If I am using a virtual private server, do I still use the 127.0.0.1/32 IP address or the IP address of my the actual server?

darioo · Accepted Answer · 2010-12-30 12:17:53Z

7

It's all explained in official documentation.

This is the relevant part:

String url = "jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true";
Connection conn = DriverManager.getConnection(url);

answered Dec 30, 2010 at 12:17

darioo

47.4k10 gold badges79 silver badges104 bronze badges

Comments

ggDeGreat · Accepted Answer · 2024-05-31 03:08:31Z

0

Mine was fixed by using this setting in pg_hba.conf. Can try it on yours.

host    all             all             0.0.0.0/0               trust
host    replication     all             0.0.0.0/0               trust

answered May 31, 2024 at 3:08

ggDeGreat

1,2161 gold badge19 silver badges33 bronze badges

Comments

Gonzalo Vattino · Accepted Answer · 2025-05-24 01:34:48Z

Nothing of that worked for me. It only worked this:

-- Connect as postgres superuser  
sudo -u postgres psql  
  
-- Create a dedicated user  
CREATE USER username WITH PASSWORD 'your_password';  
  
-- Create database  
CREATE DATABASE database_name;  
  
-- Grant privileges  
GRANT ALL PRIVILEGES ON DATABASE database_name TO username;  
  
-- Connect to gogo database  
\c database_name  
  
-- Grant schema privileges  
GRANT ALL ON SCHEMA public TO username;  
  
-- Exit  
\q

I'm not sure if this is necessary:

echo "host all username 127.0.0.1/32 scram-sha-256" >> /etc/postgresql/15/main/pg_hba.conf

Claude 3.5 helped me. It took me a long time to find this solution, I hope I can make someone else's life easier.

Collectives™ on Stack Overflow

Connecting to local instance of PostgreSql with JDBC

4 Answers 4

5 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Linked

Related