How to write bash & sql files to setup users for postgres?

Question

I am trying to create bash and sql files to create a user and a database..

Here is what I have done so far..

Setup_postgres.sql

Create USER dummbyuser;
 ALTER USER dummbyuser with superuser;
 ALTER USER dummbyuser with LOGIN;
/q

Setup_postgres.sh

sudo su - postgress
psql -f setup_postgres.sql;
sh setup_postgres.sql
logout

My problem [SOLVED]

I tell bash to run the Setup_postgres.sh however it hangs after the
sudo su - postgres line The setup_postgres.sql does not run at all.

Any one have any ideas on what I am doing wrong ?
Is this the right approach ?
I have very little experience writing bash files.

Is it possible for the script prompt users for the name of "dummbyuser"

Bohuslav Burghardt · Accepted Answer · 2014-11-16 23:57:39Z

3

You are starting a new login shell for the postgres user using sudo su - postgres. After you exit from that shell the rest of your script will run, but not with postgres user privileges.

You could modify your script like this to get it working:

#!/bin/bash

sudo -iu postgres psql < setup_postgres.sql

This will run the specified psql command with postgres user privileges.

answered Nov 16, 2014 at 23:57

Bohuslav Burghardt

35.1k7 gold badges130 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rstack Over a year ago

Thank you!!! Out of curoisity what does the #!/bin/bash mean (I know it s a comment however I have seen that around) ...Also does -iu do ?...Thanks again!

Bohuslav Burghardt Over a year ago

The #!/bin/bash means that when you run the script bash should be used as interpreter for that script. -iu are sudo options. -i tells sudo to start login shell and -u tells it to run as specified user. You can read more details in sudo help (just type man sudo in the terminal).

Doon · Accepted Answer · 2014-11-17 01:52:23Z

0

Also you don't need to sudo over to psql you can just run psql -U psql < setup_postgresql.sql as the default is to trust the username passed. If not is passed it will use the account name it is running under.

Or you can do something like this.

#!/bin/bash
createuser -U psql -s dummbyuser 
createdb -U psql -O dummbyuser dummbyuser

or even using a shell here doc

psql -U psql <<EOF 
 Create USER dummbyuser;
 ALTER USER dummbyuser with superuser;
 ALTER USER dummbyuser with LOGIN;
 EOF

---answering question from comments---

you can use positional arguments to such as $1 and $2

so for example if your script was called build_sql.sh you could right it like this

#!/bin/bash
createuser -U psql -s $1 
createdb -U psql -O $1 $2

and then call it like ./build_sql.sh dummbyuser dummbybd

which would replace $1 with dummbyuser and $2 with dummbydb

or you can prompt the user for input and capture it using the read command

so the same build_sql.sh script could become

#!/bin/bash
echo "please enter username followed by [enter]" 
read user 
echo "please enter db_name followed by [enter]" 
read dbname 
createuser -U psql -s $user 
createdb -U psql -O $user $dbname

then if you want to be really fancy you can combine them

#!/bin/bash
if [ "$1" != "" ]; then
      user=$1
else
  echo "please enter username followed by [enter]"
  read user
fi
if [ "$2" != "" ]; then
  dbname=$2
else
  echo "please enter db_name followed by [enter]"
  read dbname
fi

 createuser -U psql -s $user 
 createdb -U psql -O $user $dbname  

 echo "created new user $user, and gave them a db called $dbname"

edited Nov 17, 2014 at 1:52

answered Nov 17, 2014 at 0:02

Doon

20.3k3 gold badges42 silver badges44 bronze badges

1 Comment

Rstack Over a year ago

Awesome Thankyou! I got it to run perfectly. One question I have for you is do you know how to ask for user input for the "dummbyuser" ?....My group is going to run this script and I would like them to enter there user name without editing the file. Thanks Again!!

Collectives™ on Stack Overflow

How to write bash & sql files to setup users for postgres?

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related