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

On Sun, 27 Sep 2009 15:36:33 +0700
Ricky Tompu Breaky <ricky.breaky@uni.de> wrote:

> 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
> RB>exist". What kind of "database" actually does it mean? I believe
> RB>it's not a normal RDBMS Database (tables collection), but somewhat
> RB>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.
>

RB>I forgot to show you that I've done these steps too:
postgres=# ALTER ROLE ivia WITH LOGIN;
ALTER ROLE
postgres=# alter user ivia with password '<<my password>>';
ALTER ROLE
postgres=# alter user ivia with login;
ALTER ROLE
postgres=# commit;
WARNUNG:  keine Transaktion offen
COMMIT
postgres=# \q
sussy:~ # psql -h 127.0.0.1 -U ivia -W
Password for user ivia:
psql: FATAL:  Datenbank »ivia« existiert nicht
sussy:~ #