Re: - Mailing list pgsql-admin

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@st-andrews.ac.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@postgresql.org so that your
>message can get through to the mailing list cleanly
>
>