2

I am trying to automate the install of debian with postgreSQL but I'm running into issues with my script. The database import of schema.sql into the db1 doesn't seem to be working, and I'm not sure if I even created the database correctly.

This is the code I am using:

 # POSTGRES
apt-get install -y postgresql
echo "CREATE ROLE deploy LOGIN ENCRYPTED PASSWORD '$APP_DB_PASS';" | sudo -u postgres psql
su postgres -c "createdb db1 --owner deploy"
su postgres -c "createdb db2 --owner deploy"
service postgresql reload

# IMPORT SQL
psql --username=postgres spider < /etc/schema.sql

When I try to see if the database is created I get the following errors and the SQL import didn't seem to work.

root@li624-168:/etc/app# psql -U root spider                                                        
psql: FATAL:  role "root" does not exist

root@li624-168:/etc//app# psql -U deploy spider                                                      
psql: FATAL:  Peer authentication failed for user "deploy"

Can anyone tell me please where I have gone wrong?

Improve this question

2 Answers 2

Reset to default
1

Firstly, make sure you check result codes when executing commands. You can abort your bash script by adding set -e at the top. If any single command fails it will stop immediately.

Secondly, take another look at the error message:

Peer authentication failed for user "deploy"

You're trying to login as "deploy" and it seems to recognize the user-name. However, your operating-system user is not called "deploy", so peer auth fails. It looks like you want to login using a password, so set up your pg_hba.conf file to allow that.

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

Comments

0

Postgres databases are owned by Linux users. So, you need to create an user in postgres tha have the same name of your Linux user. then, you have to use the new user to create your db. Example:

My linux account is razcor

sudo su postgres -c 'createuser -d -E -R -S razcor'

this creates a postgres user

sudo su razcor -c "createdb db1 --owner razcor"

this creates my db

result:

razcor@ubuntu:~$ psql -U razcor db1

psql (8.4.17)
Type "help" for help.

db1=>

In your case create a user named: root

@Richard Huxton: yes, I agree.

Improve this answer

5 Comments

Thank you for your reply, I appreciate it. Are you able to provide some code example, since I am having a hard time getting my head around it.
Sorry @razcor, but you're wrong about the db belonging to postgres.
Ok, I will try to explain better: Postgres databases are owned by Linux users. So, you need to create an user in postgres tha have the same name of your Linux user. then, you have to use the new user to create your db. Example: My linux account is razcor sudo su postgres -c 'createuser -d -E -R -S razcor' this creates a postgres user sudo su razcor -c "createdb db1 --owner razcor" this creates my db result: razcor@ubuntu:~$ psql -U razcor db1 psql (8.4.17) Type "help" for help. db1=> In your case create a user named root @Richard Huxton: yes, I agree, i failed to explain.
"Postgres databases are owned by Linux users" - no, they're not. They're owned by database uesrs (roles). If you choose to, you can use peer auth to match PostgreSQL users to O.S. users though.
Yes, but, practically, the Postgres "default configuration" let the local access only to postgres users with the same name of the Linux user. Since the op is using a script that install a fresh Postgres, with the default configuration, it seems reasonable to simplify.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.