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

Dear Sam.

Thank you very much for your help.

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.

I did these steps:
"
1. I created a new opensuse11.1-linux login account + its password
(username: ivia) with YaST2;
2. ivia(at)sussy:~> su postgres -c psql postgres
Passwort:
Dies ist psql 8.3.7, das interaktive PostgreSQL-Terminal.

Geben Sie ein: \copyright für Urheberrechtsinformationen
\h für Hilfe über SQL-Anweisungen
\? für Hilfe über interne Anweisungen
\g oder Semikolon, um eine Anfrage auszuführen
\q um zu beenden
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
# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
#local all all ident sameuser
# IPv4 local connections:
#host all all 127.0.0.1/32 ident sameuser
# IPv6 local connections:
#host all all ::1/128 ident sameuser
#local all all md5
#host all all 127.0.0.1/32 md5
#host all all ::1/128 md5
host all all 0.0.0.0/0 md5
sussy:/etc #
5. sussy:/etc # rcpostgresql restart
Shutting down PostgreSQLServer angehalten
done
Starting PostgreSQL
done sussy:/etc #
6. sussy:/etc # cat /etc/sysconfig/postgresql
POSTGRES_DATADIR="~postgres/data"
POSTGRES_OPTIONS="-i"
POSTGRES_LANG=""
sussy:/etc #
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)
sussy:/etc #
".

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.

Please keep telling me.

I am confused.

Thank you very much in advance.

On Thu, 24 Sep 2009 21:16:53 +0100
Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:

> On Fri, Sep 25, 2009 at 02:47:23AM +0700, Ricky Tompu Breaky wrote:
> > sussy:~ # psql -U ricky -W
>
> I didn't realize you were running commands as root. My comments to
> the mailing list still apply, but this is normally frowned upon as
> it's easy to kill the system by accident. Also, when Linux starts
> getting viruses it'll be able to infect the whole system if you do
> this rather than just your (unprivileged) account. If you've not
> used it before, "sudo" is your friend here.
>

On Thu, 24 Sep 2009 20:59:44 +0100
Sam Mason <sam(at)samason(dot)me(dot)uk> wrote:

> On Fri, Sep 25, 2009 at 02:47:23AM +0700, Ricky Tompu Breaky wrote:
> > I'm sure that I used the correct password as "createuser" the
> > account of 'ricky'.
>
> By default; PG is set up not to use passwords in the "local" case.
> This is what the "ident" in the error is about. Your PG username
> needs to be the same as your Unix username or ident authentication
> will fail.
>
> Have a look at:
>
> http://www.postgresql.org/docs/current/interactive/auth-pg-hba-conf.html
>
> if you want to change it to use passwords as the authentication method
> in the local case.
>
>
> Note, that you don't need to restart PG when adding/removing users,
> but you do when changing the config files like pg_hba.conf and
> postgresql.conf. It's a "inside"/"outside" PG thing, users are stored
> in the database and hence PG knows when they're added/removed, but the
> config files are outside and PG doesn't know when to go looking for
> changes. There are plans to change this for pg_hba.conf, but you'll
> have to wait a bit before that happens.
>