Re:

First of all your "su postgres" problem. You will note, that the first time
you executed "su postgres" you were root (and thus didn't need a password), the
second time you were a non-root user, zf2linux, and thus you DO need a
password.

The second problem is who postgreSQL thinks you are. What is not obvious when
starting, is that when you run a postgreSQL command from a shell (as opposed to
running a DB command inside a psql session), the DB assumes that your Linux
user name is a valid postgreSQL user name, and that's who you want to be
identified as. So executing "zf2linux> psql template1" is asking postgreSQL to
run psql for you connecting you as user zf2linux; whereas running "zf2linux>
psql -U postgres template1" is asking postgreSQL to run psql for you connecting
you as user postgres (most postgreSQL commands accept a "-U " parameter that
lets you specify what user you want to run the command as (postgreSQL user,
that is, not linux user) ).

Bottom line: the simple solution is to su (to root), then su to postgres, then
do what you need to do - create real users for the DB, possibly one called
"zf2linux".

Enjoy,

Andrew

1/20/03 11:34:40 AM, Zhiqiang Feng <zf2(at)st-andrews(dot)ac(dot)uk> wrote:

>Hello all,
>
>I am new to postgreSQL. I have installed postgreSQL 7.1.3-2 on Linux 7.2.
>If I login as root (administrator) and change user as postgres I can create
>database on /usr/local/pgsql/data which I created and changed its owner as
>postgres.
>
> root# mkdir usr/local/pgsql/data
> root# chown postgres /usr/local/pgsql/data
> root# su postgres
> bash-2.3# initdb -D /usr/local/pgsql/data
> bash-2.3# postmaster -D /usr/local/pgsql/data > logfile 2 >&1 &
> bash-2.3# createdb testdb
> bash-2.3# psql testdb
>
>However, when I login as a normal linux user I cannot create database. So
>it is a user management problem.
>
> zf2linux# createdb mydb
> FATAL 1: user 'zf2linux' does not exist
>
>I have tried to create a user of zf2linux in postgreSQL but the error still
>occurs.
>If I try to change to postgres from zf1linux it asks for password.
>
> zf2linux# su postgres
> password: ******
> incorrect password.
>
>My pg_hba.conf is left as the default format:
> local all trust
> host all 127.0.0.1 255.255.255.255 trust
>
>I must miss something before I allow other users to access the postgreSQL
>server and account. Could you please help me with the problem?
>
>Thanks for your attention and will sum.
>
>Geoff
>
>University of St Andrews
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>
>