8

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?
Improve this question
1
  • 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, 2014 at 11:56

5 Answers 5

Reset to default
5

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.

Improve this answer
0
5

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'
Improve this answer
4
  • 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, 2011 at 20:20
  • @Legate have you sudo on your system?
    – ulidtko
    Jan 28, 2011 at 20:23
  • @ulidtko: Yes, I do.
    – Legate
    Jan 28, 2011 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, 2011 at 21:33
1

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)

Improve this answer
0

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?

Improve this answer
1
  • 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, 2011 at 20:22
0

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

Improve this answer
3
  • 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, 2011 at 11:41
  • You must be missing something. sudo su postgres -c "echo \$USER" prints postgres on my box.
    – alex
    Jan 29, 2011 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, 2011 at 10:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .