Skip to main content
Unix & Linux

Return to Answer

typos, grammar
Source Link
Lambart
Lambart
  • 393
  • 3
  • 9

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 :

  1. 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 anyway. ButHowever, if you use interactive psql, anyone else with sudo privileges on the machine willwill 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.

  1. Run psql as a less-privileged user, e.g.

     $ psql dev-dbdev_db -hlocalhost corey_dev -W < setup_dev.dbsetup_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 areyou're not, here), if you see a message like could not change directory to "/home/corey/scripts": Permission denied message ****, psql isis 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.

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 :

  1. 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 anyway. But 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.

  1. 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 are 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.

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 :

  1. 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.

  1. 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.

Source Link
Lambart
Lambart
  • 393
  • 3
  • 9

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 :

  1. 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 anyway. But 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.

  1. 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 are 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.