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

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.

--
Bill Moran
http://www.potentialtech.com