For the HelpCovid GPLv3+ project (C++17, Linux/x86-64, Debian/Buster, PostgreSQL 11 or 12) and its issue #27, we want to clear all the data in a given PostgreSQL 11 (or 12) database. We have today (April 6, 2020, git commit 2843184d9f589d51bd9) only tables and indexes there (see our documentation in DATABASE.md and our C++ file hcv_database.cc for details).
We just want to remove every table and index (that is "every data") from a given database (initialized by our generate-config.py python script).
We tried several approaches:
using the dynamic SQL suggested here.
But so far every attempt failed.
In commit cb982e1a57c9de81d the following debug output is observed (debug messages output with HCV_DEBUGOUT macro in C++ contains ΔBG!). Running ./helpcovid --clear-database -D -T 2 after ./generate-config.py:
./helpcovid[1393556]: HelpCovid cb982e1a57c9 start adding <?hcv confmsg, but --clear-database still does not work (issue#27) program arguments:
... ./helpcovid --clear-database -D -T 2
./helpcovid[1393556]: hcv_main.cc:573 - !! parsed 5 program arguments
./helpcovid[1393556]: hcv_main.cc:884 - !! start of ./helpcovid
version:github.com/bstarynk/helpcovid built Mon 06 Apr 2020 08:35:00 AM MEST
... gitcommit cb982e1a57c9 start adding <?hcv confmsg, but --clear-database still does not work (issue#27)
... md5sum 7f39a5002c3afc4a6b242015a9f856bb on rimski
at Mon Apr 6 08:35:15 2020 MEST on rimski
./helpcovid[1393556]: hcv_main.cc:626 - !! loading configuration file /home/basile/.helpcovidrc
./helpcovid[1393556]: hcv_main.cc:632 - !! helpcovid loaded configuration file /home/basile/.helpcovidrc
./helpcovid[1393556]: hcv_web.cc:76 - !! hcv_initialize_web: weburl='http://localhost:8089/', webroot='/home/basile/helpcovid/webroot/', opensslcert='', opensslkey=''
./helpcovid[1393556]: hcv_web.cc:114 - !! starting plain HTTP server using weburl http://localhost:8089/ and webroot /home/basile/helpcovid/webroot/ hcv_webserver@0x5622aefcb0d0
./helpcovid[1393556]: hcv_main.cc:964 - !! helpcovid debugging enabled
./helpcovid[1393556]: ΔBG!hcv_main.cc:965▪ 00.00 s‣ helpcovid is debugging
./helpcovid[1393556]: hcv_main.cc:1026 - !! helpcovid unable to write builtin pidfile /var/run/helpcovid.pid
-: Permission denied
./helpcovid[1393556]: hcv_database.cc:114 - !! using 'dbname=helpcovid_db user=helpcovid_usr password=passwd1234 hostaddr=127.0.0.1 port=5432' as PostGreSQL connection string.
./helpcovid[1393556]: hcv_database.cc:129 - !! hcv_initialize_database connstr=dbname=helpcovid_db user=helpcovid_usr password=passwd1234 hostaddr=127.0.0.1 port=5432
./helpcovid[1393556]: hcv_database.cc:133 - !! hcv_initialize_database for connstr=dbname=helpcovid_db user=helpcovid_usr password=passwd1234 hostaddr=127.0.0.1 port=5432 hcv_dbconn is 0x5622aefcb810
terminate called after throwing an instance of 'pqxx::insufficient_privilege'
what(): ERROR: must be owner of database helpcovid_db
zsh: abort (core dumped) ./helpcovid --clear-database -D -T 2
That HelpCovid program is supposed to be deployed using setuid techniques. I am unhappy, for cybersecurity reasons, with the idea of running any external command (using system(3), popen(3), or fork(2) + execve(2) + waitpid(2) ..) at this stage to clear the database.
Of course I am a PostgreSQL newbie.
You have two choices:
drop schemaIf everything is stored in a single schema and that schema is owned by your user, then use drop schema ... cascade. See the manual for details.
Note that the public schema is typically owned by the superuser postgres. You will need to transfer the ownership for that before you can do this.
drop ownedIf everything(!) you want to drop is owned by the current user, you can use drop owned ...
This will really drop everything (include views, functions, triggers, schemas, types, really: everything) that is owned by the user you specify.
Typically you would connect as the owner then run drop owned by current_user;.
See the manual for details