Debian Question posted in
The oficial documentation can be found here.

how to clear a PostgreSQL database programmatically with libpqxx – Debian

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:

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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).

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search