How can one batch create many PostgreSQL databases and users, one database for each user?
I have a list of linux usernames, and I need to create a new postgres user for each linux username, and then an exclusive database for the new user in the home folder which no other users can see except the root.
I know how to use CREATE DATABASE but am not very familiar with loops etc. in PostgreSQL. Any pointers are appreciated.
One way is to make a shell script like db.sh
#!/bin/sh
users="foo bar baz"
for user in $users
do
PGUSER=pguser createdb $user
done
Then do a
chmod 0755 db.sh
And execute it with
./db.sh
You may have to change PGUSER to your environment.
Shell loops: http://www.tutorialspoint.com/unix/unix-loop-control.htm
Do it from the shell, it will be a lot less work:
$ for user in $(cat usernames.txt); do
createuser -e $user
createdb -e -O $user $user
done;
Incidentally, for random Postgres commands, you can use psql -c to run SQL from the command line non-interactively.
