postgresqlubuntu-16.10

Postgresql : can't connect with new created users


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.


Solution

  • 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