Thread: psql -c "select * from a-user-name-that-exist fails
Hi all,
I have just installed PostGreSQL on my Gentoo Linux Desktop for the first
time. Installation is ok. Then, I have started to install an application
that uses PostGreSQL. To test if I am declared as a user in the data base,
it executes the command :
psql -c "select * from laurent
More precisely this bash script :
----------------------------------------------------------------------------------------
function exit_if_no_postgres_user
{
set +e
( psql -q template1 -c "select * from pg_user;" | grep $1 ) \
>/dev/null 2>&1
if [ $? -eq 1 ]
then
echo ""
echo "*** Error: No postgresql user '$1'"
echo ""
exit 1
# else
# echo "Good: Postgresql can be accessed by $1"
fi
set -e
}
-----------------------------------------------------------------------------------------
USER=$(whoami) is passed as an argument which is $1
-----------------------------------------------------------------------------------------
laurent being the user's name. The error is triggered, thought laurent was
declared with the command : createuser -d -A laurent. laurent appears in the
pgadmin3 roles list. As far as I have understood the documentation, roles
and users are the same thing.
Besides, I am surprised by the "template". What is it for ?
Any clue why I get this error ?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/psql-c-select-from-a-user-name-that-exist-fails-tp2852042p2852042.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
On Thu, Sep 23, 2010 at 7:14 PM, lalebarde <l.alebarde@free.fr> wrote:
>
> Hi all,
>
> I have just installed PostGreSQL on my Gentoo Linux Desktop for the first
> time. Installation is ok. Then, I have started to install an application
> that uses PostGreSQL. To test if I am declared as a user in the data base,
> it executes the command :
>
> psql -c "select * from laurent
>
> More precisely this bash script :
>
> ----------------------------------------------------------------------------------------
> function exit_if_no_postgres_user
> {
> set +e
> ( psql -q template1 -c "select * from pg_user;" | grep $1 ) \
> >/dev/null 2>&1
> if [ $? -eq 1 ]
> then
> echo ""
> echo "*** Error: No postgresql user '$1'"
> echo ""
> exit 1
> # else
> # echo "Good: Postgresql can be accessed by $1"
> fi
> set -e
> }
> -----------------------------------------------------------------------------------------
> USER=$(whoami) is passed as an argument which is $1
> -----------------------------------------------------------------------------------------
>
>
> laurent being the user's name. The error is triggered, thought laurent was
> declared with the command : createuser -d -A laurent. laurent appears in the
> pgadmin3 roles list. As far as I have understood the documentation, roles
> and users are the same thing.
> Besides, I am surprised by the "template". What is it for ?
>
> Any clue why I get this error ?
I'm not in the mood to dig through that bash function of yours to
figure out where that error message is coming from... but I'm betting
your problem is because the createuser command you used didn't specify
the "--login" option. The pg_user view only shows those users able to
login.
To double check, try this command:
SELECT rolcanlogin FROM pg_authid WHERE rolname = 'laurent';
from the psql client. I'm betting you'll see 'f' for false. To fix, try:
ALTER ROLE laurent WITH LOGIN;
Josh
On Thu, Sep 23, 2010 at 7:14 PM, lalebarde <l.alebarde@free.fr> wrote: > laurent was > declared with the command : createuser -d -A laurent. Also, I recommend you don't use that "-A" option, either: it's an obsolete synonym for "--no-superuser". It, along with "-a", its antonym, is not even documented on the createuser man page anymore.
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 ?
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/psql-c-select-from-a-user-name-that-exist-fails-tp2852042p2852359.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
SOLVED I had just to reboot - That's unusual under Linux isn't it ? Anyway, thanks-you very much for your help . -- View this message in context: http://postgresql.1045698.n5.nabble.com/psql-c-select-from-a-user-name-that-exist-fails-tp2852042p2852558.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
On Fri, 24 Sep 2010 05:54:17 -0700 (PDT), lalebarde <l.alebarde@free.fr> wrote: Bad w$ reaction, use: /etc/init.d/postgresql reload (or restart) a reboot is only necessary when you change kernel or really messed up with the system. > I had just to reboot - That's unusual under Linux isn't it ? -- Indifference will certainly be the downfall of mankind, but who cares?
On Fri, Sep 24, 2010 at 5:35 AM, lalebarde <l.alebarde@free.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.
Josh Kupershmidt wrote: > > But there's something suspicious about the sequence of events you > posted: I could connect only with the postgres role. To answer your requests : # which createdb /usr/bin/createdb # which createuser /usr/bin/createuser # createdb --version createdb (PostgreSQL) 8.4.4 # createuser --version createuser (PostgreSQL) 8.4.4 Thanks for the non-reboot tip. I am very tired : I shoud have figured out that the advice from PostgreSQL doc to reboot was for windows machines. Blame on me. I really don't like to reboot because I loose my histories in the many terminals I have. Usually, I suspend my system during months without rebooting. -- View this message in context: http://postgresql.1045698.n5.nabble.com/psql-c-select-from-a-user-name-that-exist-fails-tp2852042p2852646.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
lalebarde wrote: > SOLVED > > I had just to reboot - That's unusual under Linux isn't it ? > > Anyway, thanks-you very much for your help . In this case you did not need to reboot. You made some change that required a restart of postgresql backend service with a command like: /etc/init.d/postgresql restart
On Fri, 24 Sep 2010 06:59:15 -0700 (PDT), lalebarde <l.alebarde@free.fr> wrote: Yeah, as a matter of fact, I just tested and had this PB: $ createuser test -W => Cd line, as postgres, p/w='test' (Can create DBs + roles) $ createdb -O test test => Cd line, as postgres $ psql -U test -d test => Auth by p/w failed (!) the 1st solution I found was to log into 'test' DB as postgres and: ALTER USER test WITH PASSWORD 'test'; then the connexion was Ok. As the error msg was: psql: FATAL: authentication by password failed for user << test >> I tested into 'pg_hba.conf' and finally found I must have: local test test trust into it to log w/o any trick (but it don't ask for a p/w.) So, for security reasons I'll keep the 1st solution. > I could connect only with the postgres role. > > To answer your requests : > # which createdb > /usr/bin/createdb > # which createuser > /usr/bin/createuser > # createdb --version > createdb (PostgreSQL) 8.4.4 > # createuser --version > createuser (PostgreSQL) 8.4.4 -- A fool and his honey are soon parted.
On Fri, 24 Sep 2010 06:59:15 -0700 (PDT), lalebarde <l.alebarde@free.fr> wrote: OOPS: I forgot to say that my 'pg_hba.conf' has this line in: local all all md5 -- QOTD: Sex is like everything else. To get it done right, do it yourself.