PostgreSQL - query from bash script as database user postgres

Question

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

User · Answer

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  lt  lt EOF             x            SELECT   FROM foo             EOF

User · Answer

In my case the best solution including the requirement for authentication is  username  quot admin quot  new password  quot helloworld quot   PGPASSWORD DB PASSWORD     psql -h HOSTNAME -U DB USERNAME -d DATABASE NAME -c      quot UPDATE user SET password     new password  WHERE username     username  quot   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  quot  SQL COMMAND quot

User · Answer

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 gt  amp 2      exit  psql exit status    fi     echo  sql script successful  exit 0

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    lt user gt   lt password gt   lt host gt   lt db gt   lt  lt  EOF         lt your sql queries go here gt  EOF

[sql] PostgreSQL - query from bash script as database user 'postgres'

Examples related to sql

Examples related to bash

Examples related to postgresql