Re: How should the first step of PostgreSQL implementation should be?

On Sat, 26 Sep 2009 09:35:27 -0400
Bill Moran <wmoran@potentialtech.com> wrote:

> Ricky Tompu Breaky <ricky.breaky@uni.de> wrote:
> >
> > Dear my friends...
> >
> > I've installed postgresql-server on OpenSuSE11.1 successfully but I
> > can't connect to it from psql.
> >
> > I did these steps:
> > "
> > 1. I created a new opensuse11.1-linux login account + its password
> > (username: ivia) with YaST2;
> > 2. ivia@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=# create user ivia with password '<<mypassword>>';
> > CREATE ROLE
> > 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
> > 5. sussy:/etc # rcpostgresql restart
> > Shutting down PostgreSQLServer angehalten
> >                                                 done
> > Starting PostgreSQL                done
> > sussy:/etc #
>
> Step 5 was unnecessary.  There's no need to restart the server after
> altering/adding/removing user accounts.
>
> > 6. sussy:/etc # cat /etc/sysconfig/postgresql
> > POSTGRES_DATADIR="~postgres/data"
> > POSTGRES_OPTIONS=""
> > 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 #
>
> Your did not create the role with the LOGIN priv.  Do:
> ALTER ROLE ivia WITH LOGIN;
>
> > Look, the change to the table of user previously just dissapear and
> > I even don't need to supply the password of 'postgres' user
> > although I've created its password as I mention above:
>
> Your pg_hba.conf is configured for ident authentication, so PG isn't
> even looking at the password.  Based on what you're doing in these
> steps, I would guess that you want to use "password" authentication
> in pg_hba.
>
> Note that you _do_ need to reload the PG server after changing the
> pg_hba.conf
>
> > "
> > sussy:/var/lib/pgsql/data # su postgres -c psql postgres
> > Welcome to psql 8.3.7, the PostgreSQL interactive terminal.
> >
> > Type:  \copyright for distribution terms
> >        \h for help with SQL commands
> >        \? for help with psql commands
> >        \g or terminate with semicolon to execute query
> >        \q to quit
> >
> > postgres=# select * from user;
> >  current_user
> > --------------
> >  postgres
> > (1 row)
>
> I don't think that query does what you think it does.  Try issuing
> \du
> at the postgresql prompt to get a list of configured roles.
>
> > Please help me for the first step I use this PostgreSQL. This RDBMS
> > server is far complicated then MySQL.
>
> I assure you it's not.  Once you've got a grasp of the role system in
> PostgreSQL, I'm willing to bet that you'll understand that it's far
> simpler and more elegant than MySQL's insane grant tables.  Of course,
> being new to something always introduces a learning curve, and
> learning curves are frustrating.
>

====
RB>Dear Bill Moran...

You're absolutely correct and thank you for your advise on the last
line of your previous email. A precious advise to encourage me learning
PostgreSQL.

RB>After editting some configuration files, now my PostgreSQL always
RB>require a password to let me login as 'postgres'. And I can not
RB>login with wrong password. But I still can not login as 'ivia' user
RB>account. The error message said: "Database >>ivia<< does not exist".
RB>What kind of "database" actually does it mean? I believe it's not a
RB>normal RDBMS Database (tables collection), but somewhat different.
sussy:~ # psql -h 127.0.0.1 -U ivia -W
Password for user ivia:
psql: FATAL:  Datenbank »ivia« existiert nicht (my translation:
Database does not exist)
sussy:~ # su postgres -c psql postgres
could not change directory to "/root" (Why does the postgresql look
for '/root'?)
Password:
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# createdb ivia
postgres-# \du
                               List of roles
Role name | Superuser | Create role | Create DB | Connections | Memberof
-----------+-----------+-------------+-----------+-------------+-----------
<<hidden>>| yes       | yes         | yes       | no limit    | {}
ivia      | no        | no          | no        | no limit    | {}
postgres  | yes       | yes         | yes       | no limit    | {}
ricky     | yes       | yes         | yes       | no limit    | {}
(4 rows)

postgres-# commit
postgres-# \q
sussy:~ # psql -h 127.0.0.1 -U ivia -W
Password for user ivia:
psql: FATAL:  Datenbank »ivia« existiert nicht (my translation:
Database does not exist. Look!!! It does not make difference although
I created a database named 'ivia')

RB>This is my current 'pg_hba.conf':
# "local" is for Unix domain socket connections only
local   all         all                               password sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          password sameuser
# IPv6 local connections:
host    all         all         ::1/128               password 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

Please tell me, where is my mistakes?

Thank you very much in advance.