How to create a user for Postgres from the command line for bash automation

Question

I am using Ubuntu 12.04 and Postgress 9.2.

I need to create this user with this password e.g.

postgres://admin:[email protected]:5432

How to do that from the command line? I need to automate with a bash script. I have a fresh install.

Jataka

Answer 1

This will create user admin with password test101 on localhost:

psql -c "CREATE USER admin WITH PASSWORD 'test101';"

Seidule

psql -c "CREATE USER khophi WITH PASSWORD 'passo';" psql: FATAL: role "khophi" does not exist That's the error message I got. Why? – Middling

@Rexford I am not using Postgresql anymore. I suppose that psql is trying to use your username to connect to database, but you did not create any such user in database. Is this fresh install? If you are on Ubuntu, try this (alternative setup) help.ubuntu.com/community/PostgreSQL – Seidule

Yeah its fresh install. I followed the postgresql install guide on Ubuntu wiki – Middling

For future reference. When running the command, postgres needs a user from which to execute the query. To set a user different than the logged user (in this case khophi), you should use the -U flag. For example psql -U postgres -c "...". After a fresh install, you have a default superuser named postgres with no password. In ubuntu you should have done the following: $>sudo su $>su postgres $>psql -c ".... # now running as postgres user – Bohannan

sudo su postgres . There is not need to go through the root state. – Immigrant

sudo -u postgres psql -c "SELECT 123;" – Caphaitien

Answer 2

To run it from any user just add the sudo -u postgres to it:

sudo -u postgres bash -c "psql -c \"CREATE USER vagrant WITH PASSWORD 'vagrant';\""

Unwarranted

@knocte you might be right. Have you tested it without it? sudo -u postgres "psql -c \"CREATE USER vagrant WITH PASSWORD 'vagrant';\"" seems that it would work just fine. – Unwarranted

If you are running from root, you might get an error like could not change directory to "/root": Permission denied so it's better to run sudo with -i like this: sudo -i -u postgres psql -c "CREATE USER myuser WITH PASSWORD 'myuser1234';" – Kaycekaycee

Answer 3

To run it on the original docker image for example you can use the su -c command with the postgres user in combination with psql:

su -c "psql -c \"CREATE ROLE my_user WITH LOGIN PASSWORD 'my_password' \"" postgres

Rexer