22

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.

jutky
  • 3,895
  • 6
  • 31
  • 45

2 Answers2

33

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:

axtavt
  • 239,438
  • 41
  • 511
  • 482
  • I've asked the system group to check that issue. Thanks for the advice. – jutky Dec 30 '10 at 12:47
  • 1
    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) –  Dec 30 '10 at 12:52
  • Thanks a lot, indeed the issue was solved by configuring the PostgreSql. – jutky Dec 30 '10 at 20:51
  • @axtavt please help me to edit the configuration file of postgresql i am not able to resolve the Connection Refused Error. – Rajan Bhavsar Jul 21 '15 at 10:23
  • 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? – Avi Nov 18 '16 at 03:32
8

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);
darioo
  • 46,442
  • 10
  • 75
  • 103