postgres database issues with encoding UTF-8

Question

I have created a docker image with this command docker compose up -d where I was able to load pgAdmin instance in http://localhost:5050/browser/ create a database and table in the same , credentials are working properly.

However when I start to run my main spring boot application CustomerApplication it fails with below error >

org.postgresql.util.PSQLException: FATAL: la autentificación password falló para el usuario «amigoscode» (pgjdbc: autodetected server-encoding to be ISO-8859-1, if the message is not readable, please check database logs and/or host, port, dbname, user, password, pg_hba.conf)

I do not know what is wrong, my credentials are correct. what seems to be the issue?

below are application.yml and docker-compose.yml

docker-compose.yml

services:
  postgres:
    container_name: postgres
    image: postgres
    environment:
      POSTGRES_USER: amigoscode
      POSTGRES_PASSWORD: password
      PGDATA: /data/postgres
    volumes:
      - postgres:/data/postgres
    ports:
      - "5432:5432"
    networks:
      - postgres
    restart: unless-stopped

  pgadmin:
    container_name: pgadmin
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:[email protected]}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}
      PGADMIN_CONFIG_SERVER_MODE: 'False'
    volumes:
      - pgadmin:/var/lib/pgadmin
    ports:
      - "5050:80"
    networks:
      - postgres
    restart: unless-stopped

networks:
  postgres:
    driver: bridge

volumes:
  postgres:
  pgadmin:

application.yml

server:
  port: 8080
spring:
  application:
    name: customer
  datasource:
    username: amigoscode
    url: jdbc:postgresql://localhost:5432/customer
    password: password
  jpa:
    hibernate:
      ddl-auto: create-drop
    properties:
      hibernate:
        dialect: org.hibernate.dialect.PostgreSQLDialect
        format_sql: true
    show-sql: true

customer table Script

CREATE DATABASE customer
    WITH
    OWNER = amigoscode
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;

Answer 1

When you created the role amigoscode did you actually set a password for it?

CREATE ROLE amigoscode WITH LOGIN PASSWORD 'password';

You may have logged into pgadmin as the postgres superuser, created the role amigoscode and never set a password. The encoding issue looks similar to the one specified here which appears when the supplied password is incorrect. It looks like that pgjdbc bug was fixed in 2014, but has possibly regressed in more modern versions >= 42.2.x as there is currently a similar unresolved issue.

Other likely alternatives:

• SPRING_DATASOURCE_PASSWORD environment variable is set to something else.
• You also have an application.properties file which has a different password that will take precedence over the one in application.yml.
• You have a different password specified in any of the property sources that spring checks before application.yml (discussion of precedence here)

Answer 2

Since I have a postgres instance installed and running in my local (port 5432), the microservice customer was trying to connect to that instance, not the one from docker which was using the same port.

the solution was to change the url port from application.yml url: jdbc:postgresql://localhost:5433/customer

and the port of postgres from docker-compose.yml

from

ports: - "5432:5432"

to

ports: - "5433:5432"

so microservice connects to the postgres instance in the docker image, not the local one

then re-run docker command docker compose up -d run the CustomerApplication (SprinbgootApplication) and this time application starts up nice and smoothly by creating the customer table.

Answer 3

The same happened to me, the problem is that i was running another instance that was using the port 5432 (postgresql server and docker), I was using dbeaver and it was trying to connect to that instance instead the one that i wanted to use.

Check which applications are using the port and close the ones that you don't need and try again to connect to the database.