Thread: How to create a new user with password in commandline with no prompt for password

Hi all
Im using postgresql-8.0.3-1.
I need to create a new user in postgresql with password ..But it should not prompt me to get the password ..ie only through command line I've to give the password as input
Steps i had done are as follows

[root@g151 ~]# su postgres -c "createuser -A -d  globus"
could not change directory to "/root"
CREATE USER

[root@g151 ~]# su globus -c "createdb rftDatabase"
could not change directory to "/root"
CREATE DATABASE

Also in /var/lib./pgsql/data/pg_hba.conf I ve added the entry as

host    rftDatabase     "globus"        "172.16.10.151"  255.255.255.0   md5

Now when i tried to login to that database I got the following error

[root@g151 ~]# psql -U globus -d rftdatabase
psql: FATAL:  Ident authentication failed for user "globus"

Also with password promt it was prompting password ..But I haven't created the user with password
 [root@g151 ~]# psql -U globus -d rftdatabase -W
Password:     


Is it possible to create any new user in postgresql with password through command line and with no prompt for password.??Is there any option in Postgresql?
Also  if  I create  a new user with su postgres -c "createuser -A -d  globus" .Will it set any default password while creating the user ??

Please I need some valuable suggestion s

Thanks /Regards
Rajiv
Project Associate
CARE,MIT,
Anna University

Rajiv Rajaian wrote:
> Hi all
> Im using postgresql-8.0.3-1.
> I need to create a new user in postgresql with password ..But it should not
> prompt me to get the password ..ie only through command line I've to give
> the password as input
> Steps i had done are as follows
>
> [root@g151 ~]# su postgres -c "createuser -A -d  globus"
> could not change directory to "/root"
> CREATE USER

For adding a user in SQL with a password you can use -
CREATE ROLE name WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password';

Using that from the command line you can -
echo "CREATE ROLE name....." | psql -U postgres
to bypass the password prompt.

> [root@g151 ~]# su globus -c "createdb rftDatabase"
> could not change directory to "/root"
> CREATE DATABASE
 >
 >
> Also in /var/lib./pgsql/data/pg_hba.conf I ve added the entry as
>
> host    rftDatabase     "globus"        "172.16.10.151"  255.255.255.0   md5
>
> Now when i tried to login to that database I got the following error
>
> [root@g151 ~]# psql -U globus -d rftdatabase
> psql: FATAL:  Ident authentication failed for user "globus"

Your pg_hba.conf line above says a password must be used.
You can add a line with your no password account using trust to get past
that.

The other way around that is to use a .pgpass file in your local home
dir. http://www.postgresql.org/docs/8.3/interactive/libpq-pgpass.html
for more detail.

> Also with password promt it was prompting password ..But I haven't created
> the user with password
>  [root@g151 ~]# psql -U globus -d rftdatabase -W
> Password:
 >
>
> Is it possible to create any new user in postgresql with password through
> command line and with no prompt for password.??Is there any option in
> Postgresql?
> Also  if  I create  a new user with su postgres -c "createuser -A -d
> globus" .Will it set any default password while creating the user ??

No password is stored unless you specify it.


As much as these are ways around your inconveniences, you should also
consider the security implications of using them.


--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

Hi Shane
Thanks for u r suggestions
I tried to create user with the following command ..Im getting authentication error for postgres user

[root@g151~]# echo "CREATE USER globus WITH PASSWORD 'globus' CREATEDB;" | psql -U postgres
psql: FATAL:  IDENT authentication failed for user "postgres"

Hi vishal

While running it as "root" user Im getting
[root@g151 ~]# echo "CREATE ROLE globus WITH PASSWORD 'globus' CREATEDB;" |
psql -U postgres
psql: FATAL:  Ident authentication failed for user "postgres"

Also with "postgres" user getting the same error

[postgres@g151 ~]$  echo "CREATE ROLE globus WITH PASSWORD 'globus'
CREATEDB;" | psql -U postgres
psql: FATAL:  database "postgres" does not exist


On Feb 20, 2008 3:43 PM, Vishal Arora <aroravishal22@hotmail.com> wrote:

>
> you have to be Postgres Super user to use this command. and instead of
> CREATE USER - use CREATE ROLE...
>
>
>  ------------------------------
> Date: Wed, 20 Feb 2008 14:45:50 +0530
> From: rajiv.pgsql@gmail.com
> To: pgsql@sheeky.biz
> Subject: Re: [ADMIN] How to create a new user with password in commandline
> with no prompt for password
> CC: pgsql-admin@postgresql.org
>
>
> Hi Shane
> Thanks for u r suggestions
> I tried to create user with the following command ..Im getting
> authentication error for postgres user
>
> [root@g151~]# echo "CREATE USER globus WITH PASSWORD 'globus' CREATEDB;" |
> psql -U postgres
> psql: FATAL:  IDENT authentication failed for user "postgres"
>
>
>
> Get Sheeky @ http://Sheeky.Biz <http://sheeky.biz/>
>
>
>
> ------------------------------
> It's about getting married. Click here! Try it!<http://ss1.richmedia.in/recurl.asp?pid=201>
>

Hi Vishal

Now I m able to create new user using command line
I ve created a database named 'postgres' for postgres user and I ve created
the new user  in  comand line

The steps I ve used


[root@g151 ~]# su postgres -c "createdb postgres"
could not change directory to "/root"
CREATE DATABASE
[root@g151 ~]# echo "CREATE USER globus WITH PASSWORD 'globus' CREATEDB;" |
psql -U postgres -d postgres
psql: FATAL:  Ident authentication failed for user "postgres"
[root@g151 ~]# su postgres -c "echo \"CREATE USER globus WITH PASSWORD
'globus' CREATEDB;\" | psql -U postgres -d postgres"
could not change directory to "/root"
CREATE USER
[root@g151 ~]# su globus -c "createdb rftDatabase"
could not change directory to "/root"
CREATE DATABASE



Thanks to Vishal, Shane and pgsql-admin list

Thanks/Regards
Rajiv
Project Associate,
CARE,MIT,
Anna University

 
Saludos a todos.
 
Se me presenta el siguiente problema: olvidé y extravié la clave o password del usuario postgres y no puedo accesar las distintas bases de datos.
 
Por favor, alguien sabe cómo podría recuperar esta clave o password?
Me han dicho que si vuelvo a instalar la aplicación, es decir, el manejador de postgres, puedo perder toda la información de las bases de datos.
 
Necesito de la ayuda de ustedes!!!
 
Gracias por su valiosa colaboración.
 
Wolfgang Rodriguez
Analista de Sistemas
Caracas, Venezuela
 
 

---

Tecnología, moda, motor, viajes,.suscríbete a nuestros boletines para estar siempre a la última MSN Newsletters

this is an english list, you can write in spanish at
pgsql-es-ayuda@postgresql.org

2008/2/21 Wolfgang Rodriguez <mayckmarck@hotmail.com>:
>
>
> Saludos a todos.
>
> Se me presenta el siguiente problema: olvidé y extravié la clave o password
> del usuario postgres y no puedo accesar las distintas bases de datos.
>
> Por favor, alguien sabe cómo podría recuperar esta clave o password?
> Me han dicho que si vuelvo a instalar la aplicación, es decir, el manejador
> de postgres, puedo perder toda la información de las bases de datos.
>

is this the password for the OS user "postgres" or the DB role "postgres"?

if the former you tell that to your sysadmin in order to him to reset it...

then change the pg_hba.conf file and put trust as the method
authorization for local access or maybe sameuser then reset it with
ALTER ROLE

of course, you need access to the machine...

--
Atentamente,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
 Richard Cook

---

> Date: Fri, 22 Feb 2008 00:06:35 -0500
> From: systemguards@gmail.com
> To: mayckmarck@hotmail.com
> Subject: Re: [ADMIN] Clave usuario postgres
> CC: pgsql-admin@postgresql.org
>
> this is an english list, you can write in spanish at
> pgsql-es-ayuda@postgresql.org
>
> 2008/2/21 Wolfgang Rodriguez <mayckmarck@hotmail.com>:
> >
> >
> > Saludos a todos.
> >
> > Se me presenta el siguiente problema: olvidé y extravié la clave o password
> > del usuario postgres y no puedo accesar las distintas bases de datos.
> >
> > Por favor, alguien sabe cómo podría recuperar esta clave o password?
> > Me han dicho que si vuelvo a instalar la aplicación, es decir, el manejador
> > de postgres, puedo perder toda la información de las bases de datos.
> >
>
> is this the password for the OS user "postgres" or the DB role "postgres"?
>
> if the former you tell that to your sysadmin in order to him to reset it...
>
> then change the pg_hba.conf file and put trust as the method
> authorization for local access or maybe sameuser then reset it with
> ALTER ROLE
>
> of course, you need access to the machine...
>
> --
> Atentamente,
> Jaime Casanova
>

This is the password for the DB role "postgres"
 
Thankyou for your help.
 
Wolfgang Rodriguez

 

---

Sigue los principales acontecimientos deportivos en directo. MSN Motor

Rajiv Rajaian-2 wrote:
>
> [root@g151 ~]# su postgres -c "createdb postgres"
> could not change directory to "/root"
> CREATE DATABASE
> [root@g151 ~]# echo "CREATE USER globus WITH PASSWORD 'globus' CREATEDB;"
> |
> psql -U postgres -d postgres
> psql: FATAL:  Ident authentication failed for user "postgres"
> [root@g151 ~]# su postgres -c "echo \"CREATE USER globus WITH PASSWORD
> 'globus' CREATEDB;\" | psql -U postgres -d postgres"
> could not change directory to "/root"
> CREATE USER
> [root@g151 ~]# su globus -c "createdb rftDatabase"
> could not change directory to "/root"
> CREATE DATABASE
>

Rajiv,

Did you have your METHODs set to "trust" in your "pg_hba.conf" file (in the
$PGDATA directory)?

My question is, similar to yours, is there a way to:
1) run "initdb" as user "postgres" on an empty $PGDATA directory to start
from scratch
2) change the METHODs in "$PGDATA/pg_hba.conf" from "trust" -> "password"
3) start the postgresql server
4) run "createdb mydb" with no prompt for a password? (as user root or
postgres?)

I can only perform step 4 (createdb mydb) when the default METHOD is "trust"
in pg_hba.conf and I'm wondering if is any way around it?  I want to disable
"trust" for security purposes.

Must I use the METHOD "trust" first in order to create a user with
"createdb" privileges, and then change METHODs in pg_hba.conf from "trust"
to "password"?

--
View this message in context:
http://www.nabble.com/How-to-create-a-new-user-with-password-in-commandline-with-no-prompt-for-password-tp15583904p15817886.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Rajiv Rajaian-2 wrote:
>
> [root@g151 ~]# su postgres -c "createdb postgres"
> could not change directory to "/root"
> CREATE DATABASE
> [root@g151 ~]# echo "CREATE USER globus WITH PASSWORD 'globus' CREATEDB;"
> |
> psql -U postgres -d postgres
> psql: FATAL:  Ident authentication failed for user "postgres"
> [root@g151 ~]# su postgres -c "echo \"CREATE USER globus WITH PASSWORD
> 'globus' CREATEDB;\" | psql -U postgres -d postgres"
> could not change directory to "/root"
> CREATE USER
> [root@g151 ~]# su globus -c "createdb rftDatabase"
> could not change directory to "/root"
> CREATE DATABASE
>

Rajiv,

Did you have your METHODs set to "trust" in your "pg_hba.conf" file (in the
$PGDATA directory)?

My question is, similar to yours, is there a way to:
1) run "initdb" as user "postgres" on an empty $PGDATA directory to start
from scratch
2) change the METHODs in "$PGDATA/pg_hba.conf" from "trust" -> "password"
3) start the postgresql server
4) run "createdb mydb" with no prompt for a password? (as user root or
postgres?)

I can only perform step 4 (createdb mydb) when the default METHOD is "trust"
in pg_hba.conf and I'm wondering if is any way around it?  I want to disable
"trust" for security purposes.

Must I use the METHOD "trust" first in order to create a user with
"createdb" privileges, and then change METHODs in pg_hba.conf from "trust"
to "password"?

--
View this message in context:
http://www.nabble.com/How-to-create-a-new-user-with-password-in-commandline-with-no-prompt-for-password-tp15583904p15818939.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

bwnabble wrote:
>
> Did you have your METHODs set to "trust" in your "pg_hba.conf" file (in the
> $PGDATA directory)?
>
> My question is, similar to yours, is there a way to:
> 1) run "initdb" as user "postgres" on an empty $PGDATA directory to start
> from scratch
> 2) change the METHODs in "$PGDATA/pg_hba.conf" from "trust" -> "password"
> 3) start the postgresql server
> 4) run "createdb mydb" with no prompt for a password? (as user root or
> postgres?)

1 & 3 are standard CLI commands that can be scripted as well as anything
else you do on your system. (3 would be scripted already so that pg
starts with the system)

2 is a simple editing of a file that gets performed once after step 1.

You would only do steps 1-3 once on a system (maybe a few more if you
are repeating it as a learning experience)

cratedb on the other hand may be something that gets run every time a
new user gets added to your server.

If you are looking to automate step 4 then you bypass the password
prompt with a trust entry that is only for the machine or user that will
perform the operation. The other option is using a .pgpass file.


If you are at a point where you want to automate setting up a large
number of new systems and you don't know enough about *nix systems to
create these scripts yourself then you should take a few steps back and
learn more about *nix sysadmin before you move on.



--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz