Thread: password authentication failed (postgresql v8.1.4)
Hi. I have been upgrading my PG server from version 7.4 (debian sarge binary) to v8.1.4 (source installation). The installation procedure went fine but I can't log into the database with another role/user than 'postgres'. This is my pg_hba.conf, that I was succesfully using with v7.4: # Database administrative login by UNIX sockets local all postgres ident sameuser # All other connections by UNIX sockets local all all ident md5 # All IPv4 connections from localhost host all admin 0.0.0.0 0.0.0.0 md5 host all bdati 0.0.0.0 0.0.0.0 md5 The remote pgAdmin3 obviously doesn't work and neither does the psql command if I try to login with 'admin' or 'bdati'. Users have been created with CREATE ROLE admin WITH PASSWORD 'xxx' SUPERUSER; CREATE ROLE bdati WITH PASSWORD 'xxx' CREATEDB; It's probably one of the dumbest questions but I really can't sort it out. Thanks Andrea -- Andrea Gozzi <andrea.gozzi@linuxrevolution.eu>
Attachment
Isn't there a specific LOGIN privilege now in 8.1? In my PostgreSQL Manager, there's a "can login?" box which if cleared, creates a role, but doesn't allow them to login. Have you tried using the "createuser" wrapper program to do this for you? Andy > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of Andrea Gozzi > Sent: 17 June 2006 12:41 pm > To: pgsql-admin@postgresql.org > Subject: [ADMIN] password authentication failed (postgresql v8.1.4) > > Hi. > I have been upgrading my PG server from version 7.4 (debian sarge > binary) to v8.1.4 (source installation). > The installation procedure went fine but I can't log into the database > with another role/user than 'postgres'. > This is my pg_hba.conf, that I was succesfully using with v7.4: > > > # Database administrative login by UNIX sockets > local all postgres ident sameuser > > # All other connections by UNIX sockets > local all all ident md5 > > # All IPv4 connections from localhost > host all admin 0.0.0.0 0.0.0.0 md5 > host all bdati 0.0.0.0 0.0.0.0 md5 > > > The remote pgAdmin3 obviously doesn't work and neither does the psql > command if I try to login with 'admin' or 'bdati'. > Users have been created with > > CREATE ROLE admin WITH PASSWORD 'xxx' SUPERUSER; > CREATE ROLE bdati WITH PASSWORD 'xxx' CREATEDB; > > It's probably one of the dumbest questions but I really can't sort it > out. > > Thanks > > Andrea > > > -- > Andrea Gozzi <andrea.gozzi@linuxrevolution.eu>
http://www.postgresql.org/docs/8.1/static/role-membership.html According to the above manual page, the command is like: CREATE ROLE joe LOGIN INHERIT; (presumably with your WITH PASSWORD and other grants tacked on.) > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of Andrea Gozzi > Sent: 17 June 2006 12:41 pm > To: pgsql-admin@postgresql.org > Subject: [ADMIN] password authentication failed (postgresql v8.1.4) > > Hi. > I have been upgrading my PG server from version 7.4 (debian sarge > binary) to v8.1.4 (source installation). > The installation procedure went fine but I can't log into the database > with another role/user than 'postgres'. > This is my pg_hba.conf, that I was succesfully using with v7.4: > > > # Database administrative login by UNIX sockets > local all postgres ident sameuser > > # All other connections by UNIX sockets > local all all ident md5 > > # All IPv4 connections from localhost > host all admin 0.0.0.0 0.0.0.0 md5 > host all bdati 0.0.0.0 0.0.0.0 md5 > > > The remote pgAdmin3 obviously doesn't work and neither does the psql > command if I try to login with 'admin' or 'bdati'. > Users have been created with > > CREATE ROLE admin WITH PASSWORD 'xxx' SUPERUSER; > CREATE ROLE bdati WITH PASSWORD 'xxx' CREATEDB; > > It's probably one of the dumbest questions but I really can't sort it > out. > > Thanks > > Andrea > > > -- > Andrea Gozzi <andrea.gozzi@linuxrevolution.eu>
That was it, now everything's working! Thanks a lot, it seems like I forgot to read that manual page.. Andrea On Sat, 2006-06-17 at 12:56 +0100, Andy Shellam wrote: > http://www.postgresql.org/docs/8.1/static/role-membership.html > > According to the above manual page, the command is like: > > CREATE ROLE joe LOGIN INHERIT; > > (presumably with your WITH PASSWORD and other grants tacked on.) > > > -----Original Message----- > > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > > owner@postgresql.org] On Behalf Of Andrea Gozzi > > Sent: 17 June 2006 12:41 pm > > To: pgsql-admin@postgresql.org > > Subject: [ADMIN] password authentication failed (postgresql v8.1.4) > > > > Hi. > > I have been upgrading my PG server from version 7.4 (debian sarge > > binary) to v8.1.4 (source installation). > > The installation procedure went fine but I can't log into the database > > with another role/user than 'postgres'. > > This is my pg_hba.conf, that I was succesfully using with v7.4: > > > > > > # Database administrative login by UNIX sockets > > local all postgres ident sameuser > > > > # All other connections by UNIX sockets > > local all all ident md5 > > > > # All IPv4 connections from localhost > > host all admin 0.0.0.0 0.0.0.0 md5 > > host all bdati 0.0.0.0 0.0.0.0 md5 > > > > > > The remote pgAdmin3 obviously doesn't work and neither does the psql > > command if I try to login with 'admin' or 'bdati'. > > Users have been created with > > > > CREATE ROLE admin WITH PASSWORD 'xxx' SUPERUSER; > > CREATE ROLE bdati WITH PASSWORD 'xxx' CREATEDB; > > > > It's probably one of the dumbest questions but I really can't sort it > > out. > > > > Thanks > > > > Andrea > > > > > > -- > > Andrea Gozzi <andrea.gozzi@linuxrevolution.eu> > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match
Attachment
On Sat, Jun 17, 2006 at 01:41:13PM +0200, Andrea Gozzi wrote: > Hi. > I have been upgrading my PG server from version 7.4 (debian sarge > binary) to v8.1.4 (source installation). I see in subsequent messages that you got your particular problem worked out, but if you prefer to stick with Debian binaries, Martin Pitt, the maintainer of the Postgresql packages maintains backports to sarge. I've been running them for several months and they seem to work quite well, aside from the occasional snags you hit when running unstable. The sources.list entry I use for this is deb http://people.debian.org/~mpitt/packages/sarge-backports/ /