PostgreSQL unable to access /tmp

seasoned_geek · 2021-02-03 00:22:29

For decades I’ve had this xpnsqt OpenSource project. Doesn’t matter if it was Ubuntu, OpenSuSE, Mint, whatever. I installed PostgreSQL, and ran my shell script to create all of the tax year databases then import the CSV files. Bada-bing bada-boom, all of my expense records going back to the beginning of time loaded into PostgreSQL and I was all set to enter information for the current tax year.

https://sourceforge.net/projects/xpnsqt/

No love on Manjaro.

I've been in IT over 30 years and knew this had to be a Arch specific bug. After the warm and friendly greeting to create an account I had to endure setting up an Arch VM just to prove this was an Arch bug they inherited. What is a 10 minute task with any other distro was an entire day with Arch . . . sigh

the import script is super simple. It copies all of the .csv backup files to the /tmp where PostgreSQL can get to them in every other distro.

declare -i tax_year=1992
declare -i current_year=$(date +%Y)+1


read -p "Postgress user name: " nm
read -s -p "Postgress user password: " answer

export PGUSER=$nm
export PGPASSWORD=$answer


echo "Current Year  $current_year"

#
#   With needed parameters setup and environment verified
#   delcare all functions which will be used later on.
#
function tax_db_open_error {
    echo "creating database"
    createdb  tax_$tax_year 'Tax information for $tax_year'
    psql  -q -d tax_$tax_year -f create_tax_tables.sql
    echo "database created"
}

echo ";;;;;"
echo ";;;;;"
echo ";;;;;         Copying csv files to /tmp"
echo ";;;;;"
echo ";;;;;"
cp -f -v ~/postgres_tax_backups/*.csv /tmp
chmod a+r /tmp/*.csv

while [ $tax_year -lt $current_year ]
do
    echo ";;;;;"
    echo ";;;;;"
    echo ";;;;;   Loading $tax_year"
    echo ";;;;;"
    echo ";;;;;"

    old_error_trace=$(set +o | grep errtrace)       # keep track of old error traps
    set -o errtrace
    trap tax_db_open_error ERR                       # setup trap for open error

    echo "creating tax tables"
    psql  -q -f create_tax_tables.sql tax_$tax_year

    trap - ERR                                      # reset ERR trap

    echo "copy payees from '/tmp/tax_${tax_year}_payees.csv' csv header;" > import_it.sql
    echo "copy categories from '/tmp/tax_${tax_year}_categories.csv' csv header;" >> import_it.sql
    echo "copy expenses from '/tmp/tax_${tax_year}_expenses.csv' csv header;" >> import_it.sql
    echo "   Importing ...  $tax_year"
    psql  -q -f import_it.sql tax_$tax_year
    psql  -q -d tax_$tax_year -c "select setval( 'expenses_tran_id_seq', (select max(tran_id) from expenses)+100);"

    let "tax_year += 1"
done

then it walks the year forward recreating the same import_it.sql file for every year until it runs out of csv files.

copy payees from '/tmp/tax_2021_payees.csv' csv header;
copy categories from '/tmp/tax_2021_categories.csv' csv header;
copy expenses from '/tmp/tax_2021_expenses.csv' csv header;

When run on Arch based distros

'/home/roland/postgres_tax_backups/tax_2019_payees.csv' -> '/tmp/tax_2019_payees.csv'
'/home/roland/postgres_tax_backups/tax_2020_categories.csv' -> '/tmp/tax_2020_categories.csv'
'/home/roland/postgres_tax_backups/tax_2020_expenses.csv' -> '/tmp/tax_2020_expenses.csv'
'/home/roland/postgres_tax_backups/tax_2020_payees.csv' -> '/tmp/tax_2020_payees.csv'
;;;;;
;;;;;
;;;;; Loading 1992
;;;;;
;;;;;
creating tax tables
Importing ... 1992
psql:import_it.sql:1: ERROR: could not open file "/tmp/tax_1992_payees.csv" for reading: No such file or directory
HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
psql:import_it.sql:2: ERROR: could not open file "/tmp/tax_1992_categories.csv" for reading: No such file or directory
HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
psql:import_it.sql:3: ERROR: could not open file "/tmp/tax_1992_expenses.csv" for reading: No such file or directory
HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy.
setval

It works everywhere else, but for some reason PostgreSQL on Arch based distros cannot access /tmp. I'm starting to see quite a few messages about this on the Internet. Something broke bad recently.

fukawi2 · 2021-02-03 00:41:36

So is this Manjaro or Arch?

seasoned_geek · 2021-02-03 00:47:37

This is an Arch bug Manjaro inherited.

Due to the warm and friendly greeting when creating an account I had to burn a day setting up an Arch VM (247.2-1-arch is what it shows during boot if that matters) before I could report it here.

Somehow, someone, somewhere suddenly decided to not let PostgreSQL access /tmp.

I'm sure they thought they were "adding security" but that was the only directory PostgreSQL could reliably access so backup and transfer scripts all over Linux-land use it.

The problem does not exist in the RPM world or the Debian world. I only find it in the Arch world. From some of the other posts I've stumbled into on other sites this seems to have broken rather recently.

Slithery · 2021-02-03 01:41:48

This isn't a bug, it's the expected behaviour and has been like this for 5+ years now. The postgresql service file that Arch uses enables PrivateTmp as not doing so makes vulnerabilities much more likely.
It isn't just Arch that does this BTW....
https://fedoraproject.org/wiki/Features … PrivateTmp

PS - Can you please restrict yourself to only posting relevant details, this is a technical support forum not a personal blog for your rants.

fukawi2 · 2021-02-03 02:14:38

seasoned_geek wrote:

Due to the warm and friendly greeting when creating an account I had to burn a day setting up an Arch VM...

In case you're not a native English speaker, this comes across as very sarcastic and derisive. That's not a great way to introduce yourself to community, especially if you're expecting us to help you.

seasoned_geek wrote:

... before I could report it here.

These forums are for support; if you truly consider it a bug (it's not) then it should be reporting via the bug tracker.

If you actually want support rather than complaining about the policies and expectations of our community, I'd suggest making a systemd unit override file and disabling the PrivateTmp setting.

seasoned_geek · 2021-02-03 12:36:11

fukawi2 wrote:

seasoned_geek wrote:
Due to the warm and friendly greeting when creating an account I had to burn a day setting up an Arch VM...
In case you're not a native English speaker, this comes across as very sarcastic and derisive. That's not a great way to introduce yourself to community

I am a native English speaker, born in America. It is a frank assessment of this:

https://www.logikalsolutions.com/wordpr … eeting.png

Which is not a good way to introduce a forum to the world.

fukawi2 wrote:

seasoned_geek wrote:
... before I could report it here.
These forums are for support; if you truly consider it a bug (it's not) then it should be reporting via the bug tracker.

Breaking something that has been relied on in production systems since the 1970s __is__ a bug. Whether it was done deliberately or not is irrelevant.

Bug trackers are the step one takes __after__ they have been to the user forum to see if anyone else has encountered this unique to Arch bug.

seasoned_geek · 2021-02-03 12:38:40

Slithery wrote:

This isn't a bug, it's the expected behaviour and has been like this for 5+ years now. The postgresql service file that Arch uses enables PrivateTmp as not doing so makes vulnerabilities much more likely.
It isn't just Arch that does this BTW....
https://fedoraproject.org/wiki/Features … PrivateTmp

This bug does not exist on Fedora 33. I tested on that system as well. Database successfully recreated from csv backup files.

a821 · 2021-02-03 13:40:44

Slithery wrote:

This isn't a bug, it's the expected behaviour and has been like this for 5+ years now. The postgresql service file that Arch uses enables PrivateTmp as not doing so makes vulnerabilities much more likely.

Out of curiosity, I can reproduce the issue if PrivateTmp=true (as shipped by arch). Commenting that line out in the service file makes the issue disappear... so no bug as said.

fukawi2 · 2021-02-03 21:53:02

@seasoned_geek I'm not going to get into a banal back-and-forth with you. Your opinions are yours, but opinions aren't necessarily fact. You seem to think this community owes you something over your perceived injustice, so I will remind you that the vast majority of the people on these forums are not developers or packagers, they are just other users who volunteer their time to help others, including you. If you wish to continue to avail yourself of their graciousness, then you need to improve the attitude for how you interact with our community.

Arch Linux

#1 2021-02-03 00:22:29

PostgreSQL unable to access /tmp

#2 2021-02-03 00:41:36

Re: PostgreSQL unable to access /tmp

#3 2021-02-03 00:47:37

Re: PostgreSQL unable to access /tmp

#4 2021-02-03 01:41:48

Re: PostgreSQL unable to access /tmp

#5 2021-02-03 02:14:38

Re: PostgreSQL unable to access /tmp

#6 2021-02-03 12:36:11

Re: PostgreSQL unable to access /tmp

#7 2021-02-03 12:38:40

Re: PostgreSQL unable to access /tmp

#8 2021-02-03 13:40:44

Re: PostgreSQL unable to access /tmp

#9 2021-02-03 21:53:02

Re: PostgreSQL unable to access /tmp

Board footer