Re: createuser problem - Mailing list pgsql-novice

On Sat, 2003-09-27 at 04:27, Partha Sur wrote:
> [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 ...

The permissions needed to run a system's init script depend on the
system.  Clearly Red Hat requires root; the init script itself will
change ownership to postgres in order to run pg_ctl.  If you were
running pg_ctl directly, you would do it as user postgres.

> -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 ?)

initdb creates the database structure.  It is run only once, presumably
when the package is installed.
...
> 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.)

The kinds of access allowed are configured in the pg_hba.conf file.  You
need to read what the administrator's manual has to say about client
authentication.  This file is located in your $PGDATA directory, but I
don't know where that is in the Red Hat package.

At the moment, your authentication policy is set to allow access
according to the _system_ identity of the user; the relevant line
probably looks like this:

local   all  all  ident sameuser

which means that you can only connect as a postgresql user with the same
name as your system login.  You will need to change this to

local   all  all  md5

(or crypt or password instead of md5)

Alternatively, create a database user whose name is the same as your own
login and connect to the database without specifying a username.  The
connection will then happen under your own login/postgresql name without
the need of a password.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "My brethren, count it all joy when ye fall into
      various trials, Knowing that the testing of your faith
      produces endurance."          James 1:2,3