Connection to postgres database fails

Completed

Hey guys,

it feels i tried out everything, connecting to the postgres database via pycharm.

if i type in my command line:

psql 'dbname=mydatabase user=elsa' it works perfectly and i'm connected to my database. I have no password.

I am sure i use port 5432 as i did (https://stackoverflow.com/questions/5598517/find-the-host-name-and-port-using-psql-commands), which is the default port.

i tried to use: https://jdbc.postgresql.org/documentation/80/connect.html to get directly a url to connect, i tried:

- jdbc:postgresql://localhost:5432/mimic

- jdbc:postgresql:mimic

 

I am asked to privide my username and password (elsa, none) always getting the same error massage:

The specified user/password combination is rejected: [08004] The server requested password-based authentication, but no password was provided.

 

thanks in advance :)

0
19 comments

@ Henry Gessau

 

JDBC connections are made over TCP/IP with password auth.

psql by default works over local socket.

You must use

"host all all 127.0.0.1/32 md5" - auth with password

or

"host all all 127.0.0.1/32 trust" - auth without password

3

@vasily

I don't recall which version I was on at the time I posted that.  I'm currently on 42.2.2 (I believe the most current at the time I'm writing this) and we resolved our issue months ago.  I believe we implemented change in pg_hba.conf (changing authentication method hashes).

Thanks for the followup.

2

@vasily chernov

 

Thanks for your response.

I believe I have already set enable tcp/ip connection

 

as:

pg_hba.conf

```

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
#local all all peer
host all all 127.0.0.0/32 trust
host all all localhost trust
# IPv6 local connections:
host all all ::1/128 trust
```

 

postgresql.conf

```

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)

```

and even tried shut down all firewall,,,,,still cant get Pycharm connect with Postgres

WHILE

Pycharm can connect with SQLite via same build in 'DataGrip' tool 

Pycharm can use sqlalchemy or psycopg2 to connect with postgres,

pgAdmin4 and PSQL shell can connect with Postgres

postgres server is running 

 

so I don't think there is anything wrong for the port or the main body of Pycharm.

 

I have already tried every solution I can search online,,,,spent tons of hours,,, now, I feel frustrating 

 

 

2

hey,

i have solved my issue, needed to change in pg_hba.conf "md5" authentification to "peer" as i was not using a password for my database.

hope somebody can help you with your problem! :)

1

I meet similar issue.

When I can perfectly run psql and pgadmin4

buy my pycharm cant connect, with error:

Connection to postgres@localhost failed.
[08001] Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

 

 

 

1

@vasily chernov

 

thank you for pointing out this.

 

I have corrected and tried again,

 

Still can't work with pycharm,

 

Still fine with pgadmin4

1

for macos, i have 08001 error.

I do the following steps:

brew install postgresql

and create database first, then use datadrip to connect it.

1

I'm experiencing a similar issue with trying to connect to our new Postgres version 10 server. Had no issues with 9.6.

The message I receive is:

[08004] The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver.

I am supplying a username and password in the url.

Anyone able to assist us here?

0

@Bhoover 
What JDBC driver version do you use for connection? Since there were some issues in Postgres JDBC driver with SCRAM-SHA-256 authentication method.

0

My `pg_hba.conf` looks like this and I cannot connect to any local DB with IntelliJ.

```

local all postgres peer
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5

```

 

0

@ Henry Gessau 
Could you specify your error?

 

0

@ vasily chernov

```

The specified database user/password combination is rejected: [08004] The server requested password-based authentication, but no password was provided.

```

The URL is "jdbc:postgresql://localhost:5432/henry"

When I run "psql" from the command line there is no password prompt.

0

Thanks @g

That worked.

0

I am experiencing a similar issue.  I have no password set on my db.  if I run psql postgres I can connect just fine.  But via IntelliJ I get:

The specified database user/password combination is rejected:

[28000] FATAL: no pg_hba.conf entry for host "10.50.8.102", user "my.username", database "postgres", SSL off

When I try to locate my config files, I get:

$locate pg_hba.conf

/usr/local/Cellar/postgresql/9.6.4/share/postgresql/pg_hba.conf.sample

/usr/local/share/postgresql/pg_hba.conf.sample

$postgresql.conf

/usr/local/Cellar/postgresql/9.6.4/share/postgresql/postgresql.conf.sample

/usr/local/share/postgresql/postgresql.conf.sample

I'm new to this, but something tells me I need to drop the '.sample' off of these files and then change some settings?

Any help is much appreciated.  TYIA

0

yes, change in pg_hba.conf
you can find it by psql command

SHOW hba_file;


than change in file to 

# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust

 

0

@B Song
You need to check your hba_file configuration to accept incoming tc/ip connections. Look https://intellij-support.jetbrains.com/hc/en-us/community/posts/115000758124/comments/360000065000 for more details.

 

0

@B Song pay attention to https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html

pg_hba.conf

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
#local all all peer
host all all 127.0.0.0/32 trust
host all all localhost trust
# IPv6 local connections:
host all all ::1/128 trust

There is a type in

host all all 127.0.0.0/32 trust

it have to be:

host all all 127.0.0.1/32 trust

for proper ipv4 connection.

0

@B Song

> Still fine with pgadmin4
pgadmin4 works through socket connection

0

Please sign in to leave a comment.