Connect as user with no password set on Postgresql 8.4 via JDBC

Question

I'm trying to connect to a PostgreSQL 8.4 DB in Ubuntu 10.10 via the JDBC drivers. I'm connecting via jdbc:postgresql:localhost:5433/dbname because PostgreSQL is running on a non-default port 5433 so I have to specify the port.

I've already edited my postgresql.conf to set listen_addresses = "*". I understand that even though it's localhost, it's still using TCP/IP to connect via JDBC.

My problem is that I created a user without a password. If i do not specify a password with DriverManager.connect(url), it errors indicating that I need to specify a password for authentication. Every password I try, including empty string, fails to authenticate with the DB.

How can I connect?

Edit: If connecting over wrong port, the error is : PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. When attempting to connect on the correct port, I'm getting PSQLException: FATAL: password authentication failed for user "user". THis is remedied by the accepted answer below.

Show the full, exact error message and stack trace. Your title says "connection refused", but your text says "it errors that I need to specify a password for authentication". These are completely different things; in the latter case a successful TCP/IP connection is made then authentication is refused. From the description it looks like you get the connection refused when you connect to the default port 5432, because Pg is running on port 5433, but your actual problem is nothing to do with the connection refused error and everything to do with auth. Also: PostgreSQL 8.8? 8.4? — Craig Ringer
– Craig Ringer, Commented Nov 14, 2012 at 0:26
Here you can find versions of PostgreSQL that actually exist, "8.8" is not among them. — Erwin Brandstetter
– Erwin Brandstetter, Commented Nov 14, 2012 at 0:31
Craig, my apologies, I am on posgres 8.4. As for my errors, yes, I was receiving different errors depending on the port. It seems I am indeed on 5433. When i tried a different port, I was getting a connection refused, which is more a networking problem (wrong port) as you imply. I know that in SQLServer sometimes, the error is pretty generic and an authentication error doesn't necessarily mean that the specified DB exists, or may infact be a permissions issue. — Stealth Rabbi
– Stealth Rabbi, Commented Nov 14, 2012 at 12:53
@StealthRabbi You'll be glad to know that PgJDBC doesn't generally have that issue then :-) . Errors are generally pretty specific, especially connection/auth issues, and include the error message from the backend if there was one. — Craig Ringer
– Craig Ringer, Commented Nov 14, 2012 at 23:14
@StealthRabbi Can you please edit to add the actual error you get from PgJDBC, so others with the same problem can find this later? — Craig Ringer
– Craig Ringer, Commented Nov 16, 2012 at 7:38

Craig Ringer · Accepted Answer · 2012-11-14 00:19:20Z

14

If you have pg_hba.conf set to require md5 authentication and the user has no password, then no authentication can occur.

ALTER USER the_user_name PASSWORD 'give_it_a_password';

Alternately, use ident or (for localhost only, unsafe) trust authentication for that user/db combo in pg_hba.conf if you really must have no password. This is usually a bad idea, it's much better to just set a password.

Demo:

$ psql -q -U postgres postgres
postgres=# CREATE USER nopw;
CREATE ROLE

$ psql -h localhost -U nopw postgres
Password for user nopw:               [pressed enter]
psql: fe_sendauth: no password supplied

$ psql -q -U postgres postgres
postgres=# ALTER USER nopw PASSWORD 'test';
postgres=# \q

$ psql -q -h localhost -U nopw postgres
Password for user nopw:            [entered 'test' then pressed enter]
postgres=>

answered Nov 14, 2012 at 0:19

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

3 Comments

Stealth Rabbi Over a year ago

on my local dev machine, I was able to set it to trust mode as you've suggested. I completely agree that specifying a password is best.

asgs Over a year ago

I created user, but somehow without the -h localhost option, I was never able to login. Kept getting the error psql: FATAL: Peer authentication failed for user "test". Does this mean it's trying to connect to some other host? That doesn't make sense at all.

Craig Ringer Over a year ago

@asgs Read the "client authentication" chapter of the manual and read about pg_hba.conf. The TL;DR is that you have a different authentication method configured for local connections over unix sockets (local mode) than you do over tcp/ip connections (host mode) in pg_hba.conf

cYOUNES · Accepted Answer · 2012-11-13 22:34:45Z

1

to connect to your database:

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;

    private final String url = "jdbc:postgresql://localhost:5432/dbname";
    private final String driver = "org.postgresql.Driver";
    private final String login = "username";
    private final String password = "";
        private Connection connection = null;
    //...
    try {
    Class.forName(driver);
        connection = DriverManager.getConnection(url,
                login, password);
    } catch (SQLException e) {
        System.out.print(" Unable set the connection!");
    } catch (ClassNotFoundException e) {
        System.out.print(" Unable to load the driver class!");
    }

answered Nov 13, 2012 at 22:34

cYOUNES

94616 silver badges33 bronze badges

3 Comments

Stealth Rabbi Over a year ago

If I do this with port 5433, I get a PSQLException, password authentication failed. if I do it with port 5422 (or by not specifying a port), I get Connection Refused, asking that I check the host name and port.

Stealth Rabbi Over a year ago

By specifying the correct port, this is the proper way to connect, by specifying a blank password, if that's how the user is set up.

Ujjwal Singh Over a year ago

Negative - did not work. If you are relying only on a blank password and have not set up an other mode of password-less authorization then this will not work. So I set a password.

Collectives™ on Stack Overflow

Connect as user with no password set on Postgresql 8.4 via JDBC

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related