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

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 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.