Thread: How should the first step of PostgreSQL implementation should be?
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 #
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 #
".
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:
"
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)
postgres=#
".
Please help me for the first step I use this PostgreSQL. This RDBMS
server is far complicated then MySQL.
Thank you very much in advance.How should the first step of PostgreSQL
implementation should be?
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. -- Bill Moran http://www.potentialtech.com
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.
Re: How should the first step of PostgreSQL implementation should be? (revised)
From
Ricky Tompu Breaky
Date:
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:~ #
Re: How should the first step of PostgreSQL implementation should be? (revised)
From
Alban Hertroys
Date:
On 27 Sep 2009, at 10:44, Ricky Tompu Breaky wrote: > 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:~ # You didn't specify a database to connect to. By default psql tries to connect to a database named after the login user, in this case "ivia". You probably want to connect to the database named "postgres" that's created by default (at the initdb step). Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4abf320b11688043321471!
On Sun, Sep 27, 2009 at 2:36 AM, Ricky Tompu Breaky <ricky.breaky@uni.de> wrote:
> 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
1: This is not the SQL command, it's the command line command. SQL is:
create database dbname;
Note the semicolon (or you can use \g to execute the buffer)
> postgres-# \du
Note that your prompt has postgres-# not postgres=#
the - tells you there's already something in the buffer.
Also, \du shows you users. \l shows you databases.
> 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
Again, no ;. Note that pgsql doesn't start an open transaction
automagically like oracle. you need an explicit begin; to open a
transaction block or all your commands will be individual transactions
executed immediately when \g or a semi colon is used.
> 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')
Nope, you only thought you did. Go back and try again. :)
Re: How should the first step of PostgreSQL implementation should be? (revised)
From
Ricky Tompu Breaky
Date:
Dear Alban. You've solved my problem.... Thank you 1000x. On Sun, 27 Sep 2009 11:36:09 +0200 Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote: > On 27 Sep 2009, at 10:44, Ricky Tompu Breaky wrote: > > > 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:~ # > > > You didn't specify a database to connect to. By default psql tries > to connect to a database named after the login user, in this case > "ivia". You probably want to connect to the database named "postgres" > that's created by default (at the initdb step). > > Alban Hertroys > > -- > Screwing up is the best way to attach something to the ceiling. > > > !DSPAM:737,4abf320b11688043321471! > > >