Connection to postgres database fails
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 :)
请先登录再写评论。
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?
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! :)
ref. https://intellij-support.jetbrains.com/hc/en-us/community/posts/115000758124/comments/360000007850
to get path of `pg_hba.conf` file ask postgres ref. https://askubuntu.com/a/256711/22308
```
psql
show hba_file
```
@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.
@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.
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
```
@ Henry Gessau
Could you specify your error?
@ 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.
@ 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
Thanks @g
That worked.
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:
When I try to locate my config files, I get:
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
yes, change in pg_hba.conf
you can find it by psql command
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
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.
@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.
@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
@B Song pay attention to https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html
There is a type in
it have to be:
for proper ipv4 connection.
@vasily chernov
thank you for pointing out this.
I have corrected and tried again,
Still can't work with pycharm,
Still fine with pgadmin4
@B Song
> Still fine with pgadmin4
pgadmin4 works through socket connection
for macos, i have 08001 error.
I do the following steps:
and create database first, then use datadrip to connect it.