AWS EC2 userdata script: How create PostGres User,Password and DataBase?-CodePudding

I am trying to create Postgres user, passWord and database using userdata field in AWS EC2.

My last attempt was:

sudo su postgres
psql -U postgres -c "CREATE ROLE postgre;"
psql -U postgres -c "ALTER ROLE  postgre  WITH LOGIN;"
psql -U postgres -c "ALTER USER  postgre  CREATEDB;"
psql -U postgres -c "ALTER USER  postgre  WITH ENCRYPTED PASSWORD '1234567';"
psql -U postgres -c "create database productdb';"

I got this error message for each 5 last lines above:

psql: error: FATAL: Peer authentication failed for user "postgres"

Any suggestion to create user, password and database using userdata field in EC2?

I found this link, but I dont know how use this solution in EC2 userdata script.

CodePudding user response：

You don't need sudo su because userdata script is already run as root. So you can become another user with just one of those commands, you don't need both.

There are several ways to do it, but this is how I generally do:

su - postgres <<'EOpostgres'
psql -U postgres -c "CREATE ROLE postgre;"
psql -U postgres -c "ALTER ROLE  postgre  WITH LOGIN;"
psql -U postgres -c "ALTER USER  postgre  CREATEDB;"
psql -U postgres -c "ALTER USER  postgre  WITH ENCRYPTED PASSWORD '1234567';"
psql -U postgres -c "create database productdb';"
EOpostgres

Note that all of those -U postgres are redundant, as they just specify what is already the default.

I use this in the userdata script on ubuntu. I assume it or something like it will work with amazon-linux, but I've already touched that hot stove once and don't want to try it again.

CodePudding user response：

Peer authentication needs hostname in the psql parameters. Try adding -h localhost or host IP in the psql command.