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(dot)breaky(at)uni(dot)de> wrote:

> On Sat, 26 Sep 2009 09:35:27 -0400
> Bill Moran <wmoran(at)potentialtech(dot)com> wrote:
>
> > Ricky Tompu Breaky <ricky(dot)breaky(at)uni(dot)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(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=# 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:~ #