Thread: ...

...

From
Zhiqiang Feng
Date:
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



Re:

From
Andrew Biagioni
Date:
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
>
>