createuser problem - Mailing list pgsql-novice

Hello,

I am new to PostgreSQL (but not new to SQL; I am an experienced Oracle
Programmer).  Any help to resolve my problem will be greatly appreciated.

I am trying to create a new database user and use PostgreSQL server from
this account and not the "postgres" (supervisor) userid.  I fail to do this.
Please tell me how to do this successfully.  Many thanks.

Logged in to Red Hat Linux 9.0 as root.

Tried to start postgresql server from "postgres"  userid (as suggested in
"Beginning Databases with Postgresql" - Stones & Matthew).
I used the supplied init script called  /etc/rc.d/init.d/postgresql.   I did
not use pg_ctl to start the server.

[root@localhost root] su - postgres
-bash-2.05b$
-bash-2.05b$ /etc/rc.d/init.d/postgresql start
starting postgresql service: standard in must be a tty  [FAILED]
-bash-2.05b$

Ah well ... maybe I have to do this from ~root (as shown in "Postgresql
Developer's Handbook" - Geshwinde & Schonig) - I wish these authors would
make their mind up ...

-bash-2.05b$ exit

[root@localhost root] /etc/rc.d/init.d/postgresql start
starting postgresql service        [OK]
[root@localhost root]

SUCCESS !

(By the way I did not see anywhere in the /etc/rc.d/init.d/postgresql script
the executable  initdb is executed ... where is this done ? Or is not
required ?)

[root@localhost root] su - postgres
-bash-2.05b$

Create database "mytest"

-bash-2.05b$ createdb mytest
CREATE DATABASE
-bash-2.05b$

Check new database created

-bash-2.05b$ psql -l

   List of databases
Name      |    Owner    |    Encoding
-------------------------------------
mytest    |  postgres   | SQL_ASCII
template0 |  postgres   | SQL_ASCII
template1 |  postgres   | SQL_ASCII

  (3 rows)
-bash-2.05b$

Create new database user "psur"

-bash-2.05b$ createuser -d -P psur
Enter password for user "psur"
Enter it again:
Shall the new user be allowed to create more new users ? (y/n) n
CREATE USER
-bash-2.05b$

Check that PostgreSQL user "psur" has been created

-bash-2.05b$ psql mytest
Welcome to psql ...
...
mytest=#
mytest=# select * from pg_user ;

usename   |  usesysid  | ...
----------------------------
postgres  |  ...
psur      |  ...

(2 rows)

mytest=#
mytest=# \q

Now try to connect to database "mytest" as user "psur"

-bash-2.05b$ psql -U psur mytest
psql:FATAL:IDENT authentication failed for user "psur"
-bash-2.05b$

Perhaps database user "psur" not allowed to attach to database "mytest"
(which was created under CURRENT_USER = "postgres")  ... Is this true ?

In that case ... try to create database "psurtest" so that user "psur" can
connect to it.

-bash-2.05b$ createdb -U psur psurtest
psql:FATAL:IDENT authentication failed for user "psur"
createdb: database creation failed.
-bash-2.05b$

HELP !

Although the user "psur" seems to be successfully created,  I AM UNABLE TO
LOG IN TO Postgresql server under "psur" userid.  (I only want to use
"postgres" userid to do DBA type work.)

 Please help.  Thanks again.

Regards,

Partha Sur