PostgreSQL - query from bash script as database user 'postgres'

I have a table in my PostgreSQL database which has 3 columns - c_uid, c_defaults and c_settings. c_uid simply stores the name of a user and c_defaults is a long piece of text which contains a lot of data w.r.t that user.

I have to execute a statement from a bash script which selects the value of the c_defaults column based on the c_uid value and this needs to be done by the database user 'postgres'.

On the CLI I can do the following:

[mymachine]# su postgres
bash-4.1$psql
postgres=#\c database_name
You are now connected to database "database_name" as user "postgres".
database_name=#SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser';

However, how do I achieve this through a bash script?

The aim is to get the information from that column, edit it and write it back into that column - all through a bash script.

279948 次浏览
小开

Once you're logged in as postgres, you should be able to write:

psql -t -d database_name -c $'SELECT c_defaults FROM user_info WHERE c_uid = \'testuser\';'

to print out just the value of that field, which means that you can capture it to (for example) save in a Bash variable:

testuser_defaults="$(psql -t -d database_name -c $'SELECT c_defaults FROM user_info WHERE c_uid = \'testuser\';')"

To handle the logging in as postgres, I recommend using sudo. You can give a specific user the permission to run

sudo -u postgres /path/to/this/script.sh

so that they can run just the one script as postgres.

小开
最佳答案

Try this one:

#!/bin/bash
psql -U postgres -d database_name -c "SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser'"

Or using su:

#!/bin/bash
su -c "psql -d database_name -c \"SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser'\"" postgres

And also sudo:

#!/bin/bash
sudo -u postgres -H -- psql -d database_name -c "SELECT c_defaults  FROM user_info WHERE c_uid = 'testuser'"
小开

if you are planning to run it from a separate sql file. here is a good example (taken from a great page to learn how to bash with postgresql http://www.manniwood.com/postgresql_and_bash_stuff/index.html

#!/bin/bash
set -e
set -u
if [ $# != 2 ]; then
echo "please enter a db host and a table suffix"
exit 1
fi


export DBHOST=$1
export TSUFF=$2
psql \
-X \
-U user \
-h $DBHOST \
-f /path/to/sql/file.sql \
--echo-all \
--set AUTOCOMMIT=off \
--set ON_ERROR_STOP=on \
--set TSUFF=$TSUFF \
--set QTSTUFF=\'$TSUFF\' \
mydatabase


psql_exit_status = $?


if [ $psql_exit_status != 0 ]; then
echo "psql failed while trying to run this sql script" 1>&2
exit $psql_exit_status
fi


echo "sql script successful"
exit 0
小开

You can connect to psql as below and write your sql queries like you do in a regular postgres function within the block. There, bash variables can be used. However, the script should be strictly sql, even for comments you need to use -- instead of #:

#!/bin/bash
psql postgresql://<user>:<password>@<host>/<db> << EOF
<your sql queries go here>
EOF
小开

The safest way to pass commands to psql in a script is by piping a string or passing a here-doc.

The man docs for the -c/--command option goes into more detail when it should be avoided.

   -c command
--command=command
Specifies that psql is to execute one command string, command, and then exit. This is useful in shell scripts. Start-up files (psqlrc and ~/.psqlrc)
are ignored with this option.


command must be either a command string that is completely parsable by the server (i.e., it contains no psql-specific features), or a single
backslash command. Thus you cannot mix SQL and psql meta-commands with this option. To achieve that, you could pipe the string into psql, for
example: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator meta-command.)


If the command string contains multiple SQL commands, they are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands
included in the string to divide it into multiple transactions. This is different from the behavior when the same string is fed to psql's standard
input. Also, only the result of the last SQL command is returned.


Because of these legacy behaviors, putting more than one command in the -c string often has unexpected results. It's better to feed multiple
commands to psql's standard input, either using echo as illustrated above, or via a shell here-document, for example:


psql <<EOF
\x
SELECT * FROM foo;
EOF
小开

To ans to @Jason 's question, in my bash script, I've dome something like this (for my purpose): 

dbPass='xxxxxxxx'
.....
## Connect to the DB
PGPASSWORD=${dbPass} psql -h ${dbHost} -U ${myUsr} -d ${myRdb} -P pager=on --set AUTOCOMMIT=off

The another way of doing it is:

psql --set AUTOCOMMIT=off --set ON_ERROR_STOP=on -P pager=on \
postgresql://${myUsr}:${dbPass}@${dbHost}/${myRdb}

but you have to be very careful about the password: I couldn't make a password with a ' and/or a : to work in that way. So gave up in the end.

-S

小开 
#!/bin/bash
password='complex=!password'
PGPASSWORD=$(echo $password) psql -h example.com -U example_user -d example_db -t -c "select * from example_table1" -o example_out.txt
小开

In my case the best solution including the requirement for authentication is:

username="admin"
new_password="helloworld"


PGPASSWORD=DB_PASSWORD \
psql -h HOSTNAME -U DB_USERNAME -d DATABASE_NAME -c \
"UPDATE user SET password = '$new_password' WHERE username = '$username'"

This command will update a password of a user e.g. for recovery case.

Info: The trade-off here is that you need to keep in mind that the password will be visible in the bash history. For more information see here.

Update: I'm running the databse in a docker container and there I just need the commmand: docker exec -i container_name psql -U postgres -d postgres -c "$SQL_COMMAND"


提交答案