I have the following problem: On every machine running Postgresql there is a special user postgres. This user has administrative access to the database server.

Now I want to write a Bash script that executes a database command with psql as user postgres (psql shall execute as user postgres, not the script). So far, that wouldn't be a problem: I could just run the script as user postgres.

However, I want to write the output of psql to a file in a directory where postgres has no write access.

How can I do that?

I thought about changing EUIDs in the script itself, however:

  1. I couldn't find a way to change the EUID in a Bash script
  2. How can I change the EUID when using something like
    psql -U postgres -c "<command>" > file?
  • how can I change the password postgres user in subshell (su -c 'psql -U postgres -c "ALTER USER forip PASSWORD \'password\';"' postgres) – Fndiaz Sep 11 '14 at 11:56

Use a subshell: (su -c 'psql -U postgres -c "<command>"' postgres) > file

Inside the subshell you can drop permissions to do your work, but output is redirected to your original shell which still has your original permissions.


You may want to use this trick:

{ anycommand } | su -c 'tee file' user

tee(1) is POSIX utility, so you may rely on its availability.

Or, with sudo:

{ anycommand } | sudo -u user 'tee file'
  • Thank you for your idea. However, I'm running into two problems: su does not have a -u option, at least on my machine. Running it correctly (su -c command user) leads to another problem, namely that su must be run from a terminal. It probably wouldn't work anyway as su opens a new subshell with that command. – Legate Jan 28 '11 at 20:20
  • @Legate have you sudo on your system? – ulidtko Jan 28 '11 at 20:23
  • @ulidtko: Yes, I do. – Legate Jan 28 '11 at 20:52
  • 1
    then just use sudo -u postgres psql -c '...' > file and edit sudoers to disable password prompts for this command – gelraen Jan 30 '11 at 21:33

You can run the shell script with user has better write permission (such as root), and when you output the data should write into a folder that database user postgres can write to (such as /tmp)

after data write finish, move it to the directory that your shell script has permission to write (like root user can write in anywhere)


If you are coming up with tricky ways to circumvent security restrictions, you better be asking yourself if your objective is really wise. I know nothing about postgresql - do you really need to be logged in with the admin account to do what you're trying to do, or is there some way you can grant read-only permissions for whatever it is to a normal user account?

  • Thank you for your answer. However, there's no problem in running the script as root, therefore no circumvention of security restrictions occurs. – Legate Jan 28 '11 at 20:22

Why don't you just do it like this: sudo su postgres -c "psql ..." >/path/to/file?

  • Thanks for you answer. However, a quick test with sudo su postgres -c "echo test" reveals that such a command does not print anything. Switching to root with sudo -s and then executing su postgres -c "echo test" does print test, so it seems that it is sudo that somehow eats the output. I've no idea why though, my guess is that sudo and su -c use subshells whose stdout is not dragged through to my own shell. – Christoph Wurm Jan 29 '11 at 11:41
  • You must be missing something. sudo su postgres -c "echo \$USER" prints postgres on my box. – alex Jan 29 '11 at 16:43
  • Well, it works on my Ubuntu notebook, but not on the Debian server where the script has to run. – Christoph Wurm Feb 1 '11 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.