I get the error message...
could not change directory to "/home/corey/scripts": Permission denied ... when I run the following script ...
#!/bin/bash sudo -u postgres psql < setup_dev_db.sql read -rsp $'Press any key to continue...\n' -n 1 key ... the contents of setup_dev_db.sql are executed without any issues, but the error is annoying.
Can I get rid of it?
Postgres wants to create a $HOME/.psql_history file, where it will store all your queries and commands from the psql client. It may well want to do something else at $HOME, but I don't see any evidence in the form of other hidden files. And it won't actually create the history file unless you're using psql interactively, which you're not.
I had this exact same problem and found this question, but the accepted answer wasn't acceptable to me -- I shouldn't have to grant postgres permission to leave a trail of my queries in whatever directory I happen to be in when I run a script!
@Corey, the solution you mentioned in your comment (cd /tmp before calling sudo...) is probably the best. psql won't create this file in /tmp (I'm sure that's deliberate, because it could allow unprivileged users to read the file).
There are two other solutions I can think of :
Run psql in a login shell by adding -i to your command
sudo -i -u postgres psql < setup_dev_db.sql This will set $HOME to postgres's HOME directory, listed in /etc/passwd. For Ubuntu, that's /var/lib/postgres. But since you're piping in commands, it won't create a .psql_history file. However, if you use interactive psql, anyone else with sudo privileges on the machine will have access to your command history.
I'm not sure if there are any other negative consequences to running a login shell in this situation.
Run psql as a less-privileged user, e.g.
$ psql dev_db -hlocalhost corey_dev -W < setup_dev_db.sql If this is a problem because you leave postgres user creation to your setup_dev_db.sql script, and you don't have any users yet, just add a createuser command in your script first, something like this:
$ sudo -u postgres createuser corey_dev -P and perhaps ...
$ sudo -u postgres createdb dev_db "Dev database" NOTE: When using the psql client interactively (which you're not, here), if you see a message like could not change directory to "/home/corey/scripts": Permission denied message ****, psql is going to write to /var/lib/postgres/.psql_history (or wherever its $HOME is)! If you've ever seen that warning when using interactive psql, go look--you'll probably find a hidden history file.
sudo -u postgres -i psql -U .... because sudo -u -i postgres psql -U and sudo -ui postgres psql -U ... didn't work. psql wanting to write to $HOME/.psql_history is the cause of this issue. In support of this suspicion I would like to offer the output of sudo -u postgres bash -c 'echo $HOME', which is /var/lib/postgresql; so writing to .psql_history should just work. No, for some reason,psql seems to insist on doing a chdir first to whatever the current working directory is, which for a non-interactive sudo will be the working directory of the invoker; hence the "could not change directory to /home/corey/scripts" output, which is unlikely to be someone's homedir. --set-home flag to sudo will definitely print the homedir of the postgres user; but I will bet that, all other things considered equal, sudo --set-home -u postgres psql will still print the "could not change directory to ..." error. To change to a directory, a user must have the 'x' permission for that directory.
I assume you are running the script from '/home/corey/scripts'. When 'sudo -u postgres' changes the current user to 'postgres' it attempts to set the working directory for 'postgres' to the working directory it was called from generating the error you're seeing.
Make sure that the user 'postgres' has permission 'x' for '/home/corey/scripts'.
TL;DR: There is discussion about this issue in Postgres mailing list and it has been fixed in PostgreSQL 16.
Reason for the error:
While @lambart's solutions are correct, the reason for the error is not since it has nothing to do with .psql_history file. The
could not change directory to "/home/corey/scripts": Permission denied error is coming from resolve_symlinks() function which resolves symlinks using chdir and finally tries to chdir back to the original directory which fails if the user executing psql does not have permissions to do so. The resolve_symlinks() call comes from find_my_exec() which in turn is called by set_pglocale_pgservice() (which sets PGSYSCONFDIR and PGLOCALEDIR).
Solutions for older versions:
For versions prior 16, the error is fixed by giving the user executing psql command x permission to the working directory where the command is issued. This can be done in several ways:
Add -i to sudo command to use login shell which changes the working directory to the home directory of the postgres user before executing the psql command (postgres user naturally has the x permission to its own home directory):
sudo -i -u postgres psql < setup_dev_db.sql Give postgres user x permission to the current working directory by adding it to the group owning the working directory:
# Get group owning the current directory stat -c "%G" . # Add postgres user to that group sudo usermod -aG <group above here> postgres Give postgres user x permission to the current working directory by giving x permission to "others" to the working directory. This can be handy e.g., in single user VM-based systems meant for development, such as WSL2 where simple chmod o+x /home/$USER command will fix the error.
Run the psql using current user which of course have permissions to the current working directory.
Before executing the command, change working directory to a directory where postgres user has x permission, e.g., cd /tmp.
You may use sudo -u postgres -c your_command.bash or su postgres -c your_command.bash. You could also use,
su - postgres your_command.bash This will ensure that you acquired all the postgres account enviroment.
For me this did the trick, pay attention to quotes (')
sudo -Hiu postgres 'pg_dump --column-inserts --data-only --table=someTable entities_db > /var/backups/anywhere/$(date +%Y%m%d_%H%M%S)_someTable.sql' Note the -Hiufor sudo, or use su - postgres
you can also put that in a cronjob for root with crontab -e
You can save the current dir to the stack with pushd then move to the directory with permissions, change the user to postgres, and when return, move to the last directory using popd.
pushd /tmp; sudo runuser -u postgres -- psql -d custom_db; popd 
sudo -u postgres, do you get the same error?postgresuser can't read, and the error would go away if the script was run from, say,/tmp.