javapostgresqljdbcconnection-stringdatabase-connection

Connecting to local instance of PostgreSql with JDBC

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.

Solution

  • 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: