Why permission denied error with flags password/locahost in PostgreSQL?

Question

The database detector exists, verified by \l in psql. The user postgres exists verified by SELECT * FROM pg_user;.

#1 test [solved]

I run

sudo -u postgres psql detector -c "SELECT * FROM pg_user"

but get

could not change directory to "/home/masi/det": Permission denied

while getting also the right output of the user table.

Solution in Fabricio's answer and comments i.e. restart shell; previously run the flag --password causes the strange behavior.

#2 test [solved]

Other strange test. I run

sudo -u postgres psql detector --password -h localhost -c 'SELECT * FROM pg_user;'

which gives the following although I am the localhost.

psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

Solution in Fabricio's answer and comments i.e. restart shell and do not use the flag --password with sudo. Do not use the flag -h localhost with sudo.

Why here is the permission denied error?

Fabrizio Mazzoni · Accepted Answer · 2015-07-13T16:22:18.453

2

You're in /home/masi/det_78_2015 when launching the psql command. Since the user launching the command is postgres, it is not allowed to view the current home directory you are in. This is why the command is successful but it is throwing an error regarding the permissions of the directory.

Does thepostgres user have a home directory in the system?

edited Jul 13 '15 at 16:22

answered Jul 13 '15 at 16:20

Fabrizio Mazzoni

1,831
2
24
46

Sorry just typo in the body. I am at the right location. Where should the home directory be? I have not set up anything. – Léo Léopold Hertz 준영 Jul 13 '15 at 16:22
It doesn't really affect the query. It's just telling you that once the command is executed being postgres it cannot change to the current directory – Fabrizio Mazzoni Jul 13 '15 at 16:23
What system are you running on? – Fabrizio Mazzoni Jul 13 '15 at 16:23
Debian 8.1, similar to Ubuntu. – Léo Léopold Hertz 준영 Jul 13 '15 at 16:25
As per question you are having 2 separate problems. Te directory issue is not much. The problem lies in the authentication. – Fabrizio Mazzoni Jul 13 '15 at 16:25
Can you explain how to proceed here, please? – Léo Léopold Hertz 준영 Jul 13 '15 at 16:26
Yes, you are right. I previously used the flag `--password` and running the command without it afterwards, results into such permission denied messages. Now, restarting the shell, I do not get anymore the permission denied. This is strange behavior! The flag `-h localhost` thing, still, I do not understand. – Léo Léopold Hertz 준영 Jul 13 '15 at 16:35
That flag specifies that you are connecting to the database on your local machine. – Fabrizio Mazzoni Jul 13 '15 at 16:41
Yes, I am connecting to the database on my local machine but it is not working. I think just do not use the flag `-h localhost` with sudo is the solution. I presented the main problem as the test case #3. The situation from which I developed the tests 1 and 2. – Léo Léopold Hertz 준영 Jul 13 '15 at 16:46
The thread got too extensive. We managed to solve the challenges with the flags `--password` and `-h localhost` but not with `-c/-f` about which I opened a new thread here http://stackoverflow.com/q/31399266/54964 – Léo Léopold Hertz 준영 Jul 14 '15 at 06:30

Why permission denied error with flags password/locahost in PostgreSQL?

#1 test [solved]

#2 test [solved]

1 Answers1