Shell: parallel command to execute as user postgres via root

Question

I use this parallel call to do something as user postgres via passwordless ssh access of user root:

parallel -q -j0 ssh {} -l root "sudo -u postgres  -i psql -tAc 
    \"select current_user, current_database()\" -d \$(echo {}| cut -d@ -f1) "
     ::: db_foo@host1 db_bar@host2 ...

... it works except for hosts running older linux systems.

I get this message from old system:

psql: warning: extra command-line argument "current_database()" ignored
psql: FATAL:  Peer authentication failed for user "current_user,"

Versions:

GNU bash, Version 4.1.10(1)
Sudo version 1.7.6p2

How to get the quoting right to get this working on old linux systems?

Update

With the quoting from the answer of Paul A Jungwirth, this result happens:

===> parallel -q -j0 ssh {} -l root "echo \$BASH_VERSION; rpm -qf /usr/bin/sudo; sudo -u postgres  -i psql -tAc '\"select current_user, current_database()\"' -d \$(echo {}| cut -d@ -f1); echo " ::: ...

4.2.53(1)-release
sudo-1.8.6p3-3.13.1.x86_64

ERROR:  syntax error at or near ""select current_user, current_database()""
ZEILE 1: "select current_user, current_database()"
         ^
4.1.10(1)-release
sudo-1.7.6p2-0.16.1.x86_64

psql: warning: extra command-line argument "current_database()"" ignored
psql: FATAL:  Peer authentication failed for user "current_user,"

.... I guess I will give up and use two loops. The first to scp a script, the second to call this script.

Trying to pass multiple levels of quoting through an ssh connection invariably leads to sadness, and combining that with parallel is just going to make things worse. Consider putting your command into a script instead so that you can avoid needing to muck about with quotes on the command line. — larsks
– larsks, Commented Jun 6, 2016 at 15:08
If you have root access to the box, I would recommend configuring sudo to allow a regular user to run the desired psql command without a password, rather than logging in as root just to avoid typing a password for the sudo command. — chepner
– chepner, Commented Jun 6, 2016 at 15:27
Or better yet, configure the PostGres database to accept remote connections: parallel -q -j0 psql -h {} '...'. — chepner
– chepner, Commented Jun 6, 2016 at 15:29
@chepner yes, configuring postgres to accept remote connections could help. But then I would need to care about passwordless logins. That's why I prefer to use ssh via root. — guettli
– guettli, Commented Jun 7, 2016 at 5:30

Paul A Jungwirth · Accepted Answer · 2016-06-09 18:03:49Z

1

I think this will do it:

parallel -q -j0 ssh {} -l root "sudo -u postgres  -i psql -tAc 
    '\"select current_user, current_database()\"' -d \$(echo {}| cut -d@ -f1) "
     ::: db_foo@host1 db_bar@host2 ...

With your original version, the inner double-quotes are interpreted when you call sudo, so that sudo gets these arguments:

1: -u
2: postgres
3: -i
4: psql
5: -tAc
6: select current_user, current_database()

In more recent versions of sudo, it will automatically escape non-alphanumeric characters in "command" arguments to keep things together, but you need to do that yourself for older versions. So newer versions will basically run this:

psql -tAc select\ current_user\,\ current_database\(\)

But older versions aren't that helpful. For those, you want to make sure that when sudo runs its command, the query is still one big argument to psql. By wrapping it in single quotes, you can make sure that sudo gets this:

1: -u
2: postgres
3: -i
4: psql
5: -tAc
6: "select current_user, current_database()"

So then it will run

psql -tAc "select current_user, current_database()"

rather than

psql -tAc select current_user, current_database()

Btw I found this commit to sudo that seems related (from 2010), although in theory it should be part of 1.7.6 (tagged in 2011). Maybe there was another change later.

Unfortunately I think this new version will now break recent versions of sudo! Sorry about that..... I'm not sure yet how to write one command that works on both.

Edit: Btw just for fun a wrote a little utility to help see what each command is receiving as its arguments.

edited Jun 9, 2016 at 18:03

answered Jun 9, 2016 at 16:54

Paul A Jungwirth

24.9k16 gold badges79 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

guettli Over a year ago

Thank you very much for you trying to find a solution. I updated the question. It still does not work. .... I guess I will give up and use two loops. The first to scp a script, the second to call this script.

Paul A Jungwirth Over a year ago

If doing things in two steps bothers you, you could also (1) use su instead of sudo, since you are starting as the root user, or (2) put this into a local script: sudo -u postgres -i psql -tAc "select current_user, current_database()" -d $hostname and then run cat do-psql.sh | parallel -q -j0 ssh {} -l root "env hostname=\$(echo {} | cut -d@ f1) sh" ::: ..... Personally I would do (1).

Collectives™ on Stack Overflow

Shell: parallel command to execute as user postgres via root

Update

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related