Thread: createuser problem
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
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.
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
"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