Re: psql -c "select * from a-user-name-that-exist fails

On Fri, Sep 24, 2010 at 5:35 AM, lalebarde <l(dot)alebarde(at)free(dot)fr> wrote:
>
> Hi Josh,
> Thanks for your help. I have recreated the user laurent with :
> $ createuser -S -d -l -P -E -U postgres laurent
> from the postgres linux user.
>
> From the documentation, I should be able to login since LOGIN is default for
> createuser and for the case, I force it with -l. But I just cannot login :
> $ createdb -U laurent mydb -W
> Mot de passe :
> createdb : n'a pas pu se connecter à la base de données postgres : n'a pas
> pu se connecter au serveur : Permission non accordée
>        Le serveur est-il actif localement et accepte-t-il les connexions sur la
>        socket Unix « /var/run/postgresql/.s.PGSQL.5432 » ?
>
> Which translate to :
> createdb: could not connect to database postgres: could not connect to
> server: Permission denied
>        Is the server running locally and accepting
>        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
>
> I am in the  pg_user view :
> postgres=# SELECT usename FROM pg_user;
>  usename
> ----------
>  postgres
>  laurent
> (2 lignes)
>
> and the pg_role one :
> postgres=# SELECT rolname FROM pg_roles;
>  rolname
> ----------
>  postgres
>  laurent
> (2 lignes)
>
> My pg_hba.conf is not a showstopper :
> local   all         all                               trust
> host    all         all         127.0.0.1/32          trust
> host    all         all         ::1/128               trust
>
> Now for your checks :
> postgres=# SELECT rolcanlogin FROM pg_authid WHERE rolname = 'laurent';
>  rolcanlogin
> -------------
>  t
> (1 ligne)
> So it looks ok, isn't it ?

Yeah, those checks show that user 'laurent' should be set up fine. You
were right, the default for the "createuser" command is to enable
logins.

But there's something suspicious about the sequence of events you
posted: you show that you were able to login to your server and query
e.g. pg_user, and also that you were able to run "createuser"
successfully. So the server was definitely running when you did that.
But then you have a message from createdb claiming the server isn't
running. The only hint I see is the socket in the error message from
createdb, /var/run/postgresql/.s.PGSQL.5432 . You translated this as
/tmp/.s.PGSQL.5432 .. maybe a typo you made, or maybe that createdb
command is getting picked up from an old/different install of Postgres
on that machine? Try "which createdb" and "which creatuser", also
"createdb --version" and "createuser --version", make sure they say
the same things.

Anyway, glad that it's working for you now, but in general you
shouldn't need to reboot your machine to resolve these kinds problems:
at most you should need to restart or reload the postmaster, as in
Jean-Yves' message.