0

I've seen a few related questions here and tried pretty much all the solutions but there's obviously something silly I'm still missing. I'm trying to bypass the need to log in to PostgreSQL using a script. Its a pretty basic script to copy a text file on the server into a table in my database, and works fine except it prompts me to log in to PostgreSQL every time I run it. I intend to write a cron job that performs this task daily so the process needs to be automatic. Here's the script.

export PGPORT=5432
export PGDATABASE=db_name
export PGUSER=user
export PGPASSWORD=my_password

psql -h host_name -p 5432 -U user -W db_name -c "\COPY schema.table_name (col1, col2, col3, col4)       
from path_to_txt_file with DELIMITER '^'"

I also went down the ".pgpass file" route to no avail. I saved it in /home/usr/.pgpass, and gave it the following credentials

*:*:*:user:my_password

saved it and then gave it permissions as follows

sudo chmod 600 .pgpass

I'm not sure if this is relevant but what I have as "usr" in my file path to the .pgpass file is different to my database username; what I have here as "user". Also the script I am running is in a completely different directory on the server to the .pgpass file. These are all novice points i'm sure but for the sake of being complete I thought I'd add them. If there was a way to modify the existing script so that it didn't prompt me for a password that would be great, otherwise if anyone has any guidance on what I might be doing wrong with the .pgpass file I'd appreciate it. Thanks in advance

Improve this question

4 Answers 4

Reset to default
1

I think the issue is option "-W" in the document of PostgreSQL, "-W" means "Force psql to prompt for a password before connecting to a database."

I suggest you to use this

export PGPORT=5432
export PGDATABASE=db_name
export PGUSER=user
export PGPASSWORD=my_password
psql -h host_name -p $PGPORT -U $PGUSER -d $PGDATABASE -c "\COPY schema.table_name(col1, 
col2, col3, col4) from path_to_txt_file with DELIMITER '^'"
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Hi Mabu, thanks alot for your reply. I wont be back in the office until the morning but I will try that as soon as I get in and let you know if it works. I suspect your probably correct. Thanks again
Unfortunately that didn't work. When I used the above modifications I get the following error "psql: FATAL: password authentication failed for user "user" "FATAL: password authentication failed for user "user"... This username is definitely set up for access to this database. I'll continue to work at it, and thanks for the input.
@tasslebear: I'll try again tomorrow for you case.
1

In addition to what Mabu said:

Postgresql has all it takes to automate logins and to be able to keep the connection parameters out of your code.

In addition to the .pgpass file for the password you can define all your connection parameters in a service file

Improve this answer

Comments

0

I think the issue here is that my pg_hba.conf file needed to be edited to include the username that I am trying to access the database with. Unfortunately the database sits on an AWS RDS server instance, where the pg_hba.conf file is not editable. As well as manually going into the instance and trying to edit it without success, there is also mention of this here: https://serverfault.com/questions/560596/how-do-a-i-edit-conf-file-for-a-postgres-aws-rds. It will probably come down to building an EC2 instance where these configuration files are accessable.

Improve this answer

Comments

0

I stand corrected with my answer above. The .pgpass file was just located in the wrong directory. I log into my linux server with the user name ubuntu, so after moving the .pgpass file into /home/ubuntu and deleting the PGPASSWORD line from the script above (the .pgpass file will be ignored if this is left in the script..) it now works perfectly. Looking at it now, it all seems quite obvious. I hope this might save someone a bit of stress in the future.

Improve this answer

Comments

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.