Re: Newbie's question: How can I connect to my postgresql-server?

On Sat, Sep 26, 2009 at 03:00:41PM +0700, Ricky Tompu Breaky wrote:
> You've solved my several problem. But now I'm stucked on another
> problem which I know easy to anybody else but difficult as a
> postgres starter like me.

A general observation; you seem to be making things awfully complicated
initially. It may be worth going with the defaults of assuming that PG
usernames and Unix usernames are the same initially. Once you've got
a bit more experience then it may not seem so complicated. The main
reason I'm saying this is that I *very* rarely have to fiddle with these
sorts of things and yet they always do the "right" thing when I try.

Maybe there are other things going on that are getting in the way of
solving the real issue that will go away with more experience.

> I did these steps:
> "
> 1. I created a new opensuse11.1-linux login account + its password
> (username: ivia) with YaST2;

PG users and Unix users/accounts are unrelated so this step is redundant
(think of large sites with thousands of database users). If you've
always got Unix user accounts why not just use the ident auth as
default?

> 2. ivia(at)sussy:~> su postgres -c psql postgres
> 3. postgres'# ALTER USER postgres WITH PASSWORD '<<mypassword>>';
> ALTER ROLE
> postgres=# ALTER USER ivia WITH PASSWORD '<<mypassword>>';
> postgres'#
> 4. sussy:/etc # cat /var/lib/pgsql/data/pg_hba.conf
> host all all 0.0.0.0/0 md5

Were these setting in place when you ran the initial connection with the
postgres account to change people's passwords? If so, I can't see how
it would work. Maybe you are changing the "wrong" config file.

Try putting something invalid into the config file and check to see if
it starts up or gives an error.

> 7. sussy:/etc # psql -h 127.0.0.1 -U ivia -W
> Password for user ivia:
> psql: FATAL: Passwort-Authentifizierung für Benutzer »ivia«
> fehlgeschlagen (my translation: Password-Authentication for user »ivia«
> failed)

It's obviously using password auth, are you sure you got the passwords
right? You haven't got a backslash in the password have you? it'll need
to be escaped in the initial creation bit if you do. A simple ASCII
password such as 'test' may be a good place to start.

> Why can I not login with 'iVia' to my postgresql? Is it because I use
> 'md5()' but not blowfish as I remember OpenSuSE11.1 use 'blowfish()'
> as its default password encryption. But AFAIK, there's nothing to do
> with the RDBMS Encryption (PostgreSQL in my case) and the Host OS
> password encryption method.

Yup, as far as I can tell this shouldn't have any effect. Then again,
I don't use SuSE and it's possible (though very unlikely) that they
changed this.

Sam

--
Sam http://samason.me.uk/