How to setup postgresql db correctly

aaP_ronywisnuwardana

I want to use a PostgreSQL db on my server. I see there is a Databases > PgSQL menu and also a PostgreSQL Manager app. Each appears to work differently because the db added from PostgreSQL Manager is not available in the Databases menu. What I want to achieve is that I can create a database and then I can connect to this DB from my local PC with the DBeaver app.
Please anyone here can help me

aaP_ronywisnuwardana

I have added a db in PostgreSQL Manager but when i try to connect i got this error. I'm 100% sure that the db username & password used is correct, the same as in PostgreSQL Manager. I set access permission to 'everyone'

aaP_douglassilvestrini

Verify your pg_hba config in client authentication section (PostgreSQL Manager).

host all all 0.0.0.0/32 md5

That way you can access all databases from any ip address.

aaP_ronywisnuwardana

aaP_douglassilvestrini i can connect from my local PC but i have problem with password authentication as in the screenshot

aaP_ronywisnuwardana

Finally, i figured out the issue. I CAN NOT connect to the DB created from PostgreSQL Manager (password authentication failed for user xxxx) but i CAN connect to the DB created from Databases menu.
It is so weird

aaP_demo1

aaP_ronywisnuwardana
How to connect it:

PostgreSQL Manager > Configuration file > Add this line:

listen_addresses = '*'

or limit to your public IP address then save

Go to Client authentication > Add this command:

# IPv4 remote connections:
host    all             all             0.0.0.0/0            md5
host    replication     all             0.0.0.0/0            md5

then save and restart the postgresql service

If still can't connect, make sure to release port 5432 for postgres port connection.

aaPanel_Kern

Hello, please check if the database exists? Whether to use special symbols for passwords, the beginning of numbers
Log in:
su - postgres

enter the database
psql

Check out the database:
\l

View all database information
SELECT * FROM pg_database ORDER BY datname;

quit
\q