0

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.

Glauco Todesco
  • 15
  • 1
  • 6
  • Is that thing a script? If so, that won't work. All the commands after the first need to be fed into the shell opened by the first command. But as a script, that just waits for you to close the shell opened by the first command, then runs the rest of the commands after going back to your normal user. – jjanes Mar 10 '22 at 16:39

2 Answers2

1

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.

jjanes
  • 37,812
  • 5
  • 27
  • 34
0

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

Lohit Gupta
  • 1,045
  • 6
  • 11