Thread: createuser problem

createuser problem

From
"Partha Sur"
Date:
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






Re: createuser problem

From
Stephan Szabo
Date:
On Fri, 26 Sep 2003, Partha Sur wrote:

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

It looks like your pg_hba.conf is set to want some form of ident style
authentication from the message.  You'd probably need to try logging in to
the psur account from a unix account named psur or changing the
authentication in pg_hba.conf.

Re: createuser problem

From
Oliver Elphick
Date:
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


Re: createuser problem

From
Tom Lane
Date:
"Partha Sur" <p.sur@worldnet.att.net> writes:
> -bash-2.05b$ psql -U psur mytest
> psql:FATAL:IDENT authentication failed for user "psur"

Evidently you selected ident authentication in pg_hba.conf.  The ident
code is explicitly designed to prevent you from logging in as a Postgres
user name different from your Unix login name --- that is, you surely
can not use -U when using ident auth.

It is possible to get around this with "ident maps" that say which OS
user names are allowed to connect as which Postgres user names.  But
I think use of ident maps probably indicates that you haven't thought
through exactly what your security strategy is.

If you are just playing around trying to learn Postgres on a personal
machine, you may as well set your auth method to "trust" and not think
about security.  But I counsel revisiting the issue before you go to
production...

            regards, tom lane