how to clear a PostgreSQL database programmatically with libpqxx

Question

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 DROP DATABASE with a pqxx::nontransaction
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.

"Failed" without a description is too little - as a coder you should know that. Why don't you drop and re-create the schemas? — Laurenz Albe, Apr 06 '20 at 06:19
Because I am a PostGreSQL newbie and don't know how to do that. This is *precisely* what I am asking. — Basile Starynkevitch, Apr 06 '20 at 06:20
Then you will find it in [the documentation](https://www.postgresql.org/docs/current/sql-dropschema.html). — Laurenz Albe, Apr 06 '20 at 06:22
If everything you want to drop is owned by the same (regular) user, then look into [drop owned](https://www.postgresql.org/docs/current/sql-drop-owned.html) — , Apr 06 '20 at 06:24
Here you got the complete debugging output. Anything else is required? — Basile Starynkevitch, Apr 06 '20 at 06:36
I tried to add everything relevant. What is missing in the improved question? — Basile Starynkevitch, Apr 06 '20 at 06:45
You either need to run DROP DATABASE as *database owner* or to run DROP SCHEMA as *schema owner* (of course you could also use a superuser account if really needed). — pifor, Apr 06 '20 at 06:47
But these notions are the ones I don't understand. Any URLs? I thought a database connection has a single PostGreSQL user, defined at connection time. — Basile Starynkevitch, Apr 06 '20 at 06:49
Either run `drop schema ... cascade` as pifor suggested, or if you have multiple schemas and everything is owned by the same user, use `drop owned` — , Apr 06 '20 at 06:49
Please make than an answer with URL to documentation. Thanks — Basile Starynkevitch, Apr 06 '20 at 06:50
The name of the product is either PostgreSQL or simply Postgres. There is no capital G in the name. — , Apr 06 '20 at 06:56

score 1 · Accepted Answer · answered Apr 06 '20 at 06:54

You have two choices:

`drop schema`

If 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 owned`

If 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

@BasileStarynkevitch: then you are probably using the `public` schema. I would go for `drop owned` in that case. — , Apr 06 '20 at 07:04

score 1 · Answer 2 · answered Apr 06 '20 at 06:56

The database owner is the user that has run CREATE DATABASE: https://www.postgresql.org/docs/12/manage-ag-createdb.html

The schema owner is the user that has run CREATE SCHEMA: https://www.postgresql.org/docs/12/ddl-schemas.html#DDL-SCHEMAS-CREATE

A database has different user accounts (just like you have different users on Linux): https://www.postgresql.org/docs/12/sql-createrole.html

The user connecting from the application to the database is usually different from the database owner and from the schema owner (just like on Linux root is the admin. user but you don't use it most of the time unless you are system administrator).

how to clear a PostgreSQL database programmatically with libpqxx

2 Answers2

drop schema

drop owned

`drop schema`

`drop owned`