Postgresql : can't connect with new created users

Question

Here's the error :

$  psql -h localhost -U kMbjQ6pR9G  -d fzvqFILx0d
Password for user kMbjQ6pR9G: 
psql: FATAL:  password authentication failed for user "kMbjQ6pR9G"
FATAL:  password authentication failed for user "kMbjQ6pR9G"

I'm probably missing a configuration step on using fresh PostgreSQL.

Here's the command lines I tried to create a new user with his own database :

sudo -u postgres bash -c "psql -c \"CREATE USER $USER WITH PASSWORD '$PASSWORD';\"" &&
sudo -u postgres bash -c "psql -c \"CREATE DATABASE $DB;\"" &&
sudo -u postgres bash -c "psql -c \"GRANT ALL PRIVILEGES ON DATABASE $DB to $USER;\"" &&

Here's the PostgreSQL configuration :

$ sudo grep -v ^# /etc/postgresql/9.5/main/pg_hba.conf  |grep -v ^$
local   all             postgres                                md5
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Here's all the accound I created to test :

$ sudo  -u postgres bash -c 'psql -c "select * from pg_shadow;"'
Password: 
  usename   | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig 
------------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
 av6izmbp9l |    16384 | f           | f        | f       | f            | md5a49721ef3f5428e269badc03931baf48 |          | 
 rqmejchq7n |    16386 | f           | f        | f       | f            | md54edf3a05ca96a435f94152b75495e9dc |          | 
 yyfiknesu8 |    16388 | f           | f        | f       | f            | md5b3d4a03913569abbf318bdc490d0f821 |          | 
 qgv2ryqvdw |    16390 | f           | f        | f       | f            | md5d0959b4b5e1ed2982e19e4d0af574b11 |          | 
 postgres   |       10 | t           | t        | t       | t            | md5xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |          | 
 pf09joszuj |    16392 | f           | f        | f       | f            | md5a920dc31666459e5f0a96e9430d07f02 |          |

I think it something simple, but I miss this point.

Thanks for your support, David.

I can't seem to figure out the error message. The output looks normal to me. As long as there is no error message, it means the connection is successful. — alvits, Apr 13 '17 at 23:59
How are you trying to connect, and what kind of error are you getting? You've shown us good detail on how you're creating the user, but we need to see what you're doing that is having the problem in order to help. — jmelesky, Apr 14 '17 at 03:22

score 9 · Answer 1 · answered Apr 14 '17 at 04:24

Did you explicitly specify the database when trying to connect?

psql -h localhost -U myuser -d mydb

This is how I usually set up a user to completely own a given database:

create role myuser with createdb login encrypted password 'mypassword';
create database mydb with owner 'myuser' encoding 'utf8';

pg_hba.conf:
local   myuser           mydb                                  md5
...

score 2 · Accepted Answer · answered Apr 14 '17 at 15:05

The problem was obvious : username & database names are lowercase !

Here's the script I use to create user and his own database :

#!/bin/bash
if [ -n "$1" ]; then
    DB="$1"
else
    DB=$(php -r "echo substr(str_shuffle(str_repeat('abcdefghijklmnopqrstuvwxyz', ceil(10/63) )),1,10);")
fi

USER=$(php -r "echo substr(str_shuffle(str_repeat('abcdefghijklmnopqrstuvwxyz', ceil(10/63) )),1,10);")
PASSWORD=$(php -r "echo substr(str_shuffle(str_repeat('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ceil(20/63) )),1,20);")

sudo -u postgres psql -c "CREATE USER $USER WITH PASSWORD '$PASSWORD';"
sudo -u postgres psql -c "CREATE DATABASE $DB;"
sudo -u postgres psql -c "GRANT ALL PRIVILEGES ON DATABASE $DB to $USER;"

cat << EndOfMessage
POSTGRESQL_ADDON_DB="$DB"
POSTGRESQL_ADDON_HOST="localhost"
POSTGRESQL_ADDON_PASSWORD="$PASSWORD"
POSTGRESQL_ADDON_PORT="5432"
POSTGRESQL_ADDON_USER="$USER"

EndOfMessage

https://github.com/davidbouche/linux-install/blob/master/pgsql-database-create.sh

Thanks for your contribution. David

Postgresql : can't connect with new created users

2 Answers2