Postgres DB password authentication failed error

Get Help
1

Greeting. I just installed the latest metabase.jar on my linux server which also hosts a postgres db server. I created the database metabase and setup these env variables:

export MB_DB_TYPE=postgres
export MB_DB_DBNAME=metabase
export MB_DB_PORT=5432
export MB_DB_USER=postgres
export MB_DB_PASS=somePw
export MB_DB_HOST=localhost

Upon running the jar file, I get a bunch of error messages which leads to this caused by:

Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "postgres"

I can connect to the db server from my laptop using pgadmin and sql squirrel client so I know the password works just fine.

How can I fix this? Thanks,

2

DANGER: DO NOT USE the postgres user (or any superuser) for Metabase! Create a separate role for Metabase to use, and create the database with that role as the owner. That is sufficient permissions for Metabase to manage its app database.

Make sure you quote the password if it has special characters in it as the shell might interpret them. Avoid putting shell metacharacters in the password. If you’re still having trouble, try using MB_DB_CONNECTION_URI instead so there isn’t any issues with multiple interpretation of the inputs.

export MB_DB_CONNECTION_URI="jdbc:postgresql://databaseserver.example.com:5432/metabase?user=metabase&password=thepasswordisplease"
3

One last thing to check - does your pg_hba.conf file allow the connection?

4

I think so. My pg_hba.conf file snippet is below:

"local" is for Unix domain socket connections only

local all all peer

IPv4 local connections:

host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.153.1/32 trust # VMNet8

psql (PostgreSQL) 17.9

I followed Doug White’s advice and created a user metabase and a db role metabase. I’m still getting the same error for the user metabase however.

5

Thanks. Went thru the motions of creating the new user and role but still the same error.

6

Did you add the new user & client IP range to pg_hba.conf and run select pg_reload_conf(); to reload it? Also check the PostgreSQL log, it may have additional diagnostics, especially if there is a syntax error in the conf.

What network is Metabase connecting from, given your pg_hba.conf? You require a password to connect from the database server itself but host 192.168.153.1/32 does not require a password (and might give that error if you provide one and one is not needed).

7

Yes I added a new linux user metabase and a new db role named metabase. Perhaps an explanation of my machine topography. I’m running 2 VM’s on my windows 11 laptop.

the 1st VM is my source postgres db server. This is where the data to analyze lives. It has 2 roles, postgres and metabase.

I can’t connect to either db using either the postgres or metabase users.

the 2nd VM is where metabase.jar lives in /usr/local/metabase. It has 2 users postgres & metabase.

It also has a postgres server with a role named metabase. This is the db I want to run with the app, instead of the default.

Here is a piece of pg_hba.conf:

IPv4 local connections:

host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.153.1/32 trust # VMNet8

both VM’s are on the 192.168.153.1 subnet and the postgres perms are wide open on both VM’s.

8

Thanks, your explanation helps quite a lot. I am assuming, since you didn’t mention it, that you are not running Metabase nor PostgreSQL in containers.

So there are 2 PostgreSQL clusters/instances. Let’s focus on the one that will hold the app database, which is running on the same host as Metabase, if the info in the first post is correct. First thing I would do is change pg_hba.conf to trust connections from 127.0.0.1/32 and ::1/128. This is usually in the default pg_hba.conf, but your Linux distribution or PostgreSQL packager may have changed it. The lines look like this:

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

Make sure these are near the top of the file. (pg_hba.conf is first-match-wins.) Metabase should be able to connect to this database without a password with the above 2 lines in the file. (Don’t forget to pg_reload_conf()!) You can test this using the psql client on the Metabase host (psql -h localhost -U metabase should connect to the database). If you get any other result, then either there are problematic entries in pg_hba.conf or “localhost” is somehow not localhost (127.0.0.1 or ::1) and we need to track down why.

Once that is settled, if you want to put the password back on, you can. For simplicity’s sake in a lab env I’d leave it off, but if you want to simulate a production environment, I understand.

One other thing. You have an entry to trust 192.168.153.1/32. But there is a “subnet,” you say. The /32 prefix length there means it identifies a single host only. If you want to allow the whole subnet, specify the prefix length of that subnet instead of /32.

Hope that helps; let us know if you are still stuck.

9

Hi Doug,

Thanks for pointing me in the right direction. Yes I'm not using containers. Too much overhead.

So there were several solutions to my connectivity issues:

1. the metabase user role in my postgres/metabase db server didn't like the '!' character in the password. Once I recreated the role with a simpler password, I was able to connect. As for my pg_hba.conf file, these are the actives lines:

# IPv4 local connections:
host all all 127.0.0.1/32 scram-sha-256
host all all 192.168.153.1/32 trust # VMNet8

IPv6 local connections:

host all all ::1/128 scram-sha-256

The VMNET entry is the only change I made.

2. Then I ran into an issue with populating the metabase db (create table errors). I had to grant additional permissions like so:

CREATE ROLE metabase WITH
LOGIN
INHERIT
PASSWORD 'myCoolPW'
;

GRANT ALL ON DATABASE metabase TO metabase; -- not enough permissions

GRANT ALL ON SCHEMA public TO metabase; -- fixed the create table errors

Now I can start metabase with a postgres db. For now being on the same server is fine for just 1 user.

As for the webapp itself, I changed the password for the metabase role

and added an extra line in pg_hba.conf on my DW db server:

host all metabase 192.168.153.132/32 trust # metabase

I'm now ready to learn the app.

Thanks again for your help. Kevin

1 Like
10

Glad to be of service :slight_smile: