We have an old Postgres DB, serving confluence wiki behind IIS. From time to time, all tables in DB happen to be deleted.
Can anybody give me a clue to help me understand the problem? seems to be it can be a security problem.
I found nothing in IIS logs (did expect an injection attack). Nothing special found in windows events list (application + security)
the only clear point - in postgres db logs we see that the operation was performed (before, a call to get all tables available is also visible).
all software is about 10 years old, except Windows Server 2022.
Postgres version 9.2
some entries in log: 2023-12-20 12:01:02 PST ERROR: table "contentlock" does not exist 2023-12-20 12:01:02 PST STATEMENT: drop table CONTENTLOCK 2023-12-20 12:01:02 PST ERROR: current transaction is aborted, commands ignored until end of transaction block 2023-12-20 12:01:02 PST STATEMENT: SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' ELSE NULL END ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') WHERE c.relnamespace = n.oid AND c.relname LIKE 'PROBABLYNOT' AND (false OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) ) ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME 2023-12-20 12:01:06 PST ERROR: relation "cwd_app_dir_group_mapping" does not exist 2023-12-20 12:01:06 PST STATEMENT: alter table cwd_app_dir_group_mapping drop constraint fk_app_dir_group_app
After changing all passwords...
Assuming your web software uses a library or ORM to make database queries, you have one single place where you can intercept all queries. So you need to put some code in there to detect the queries done by this hacker, and when they are detected, first don't do the query, and second log absolutely everything you can. You should also configure IIS for better logging. Then perhaps you can use that information to find the vulnerability in your code and patch it.
If your code is sprayed with "pg_query(...)" in random places then you probably have SQL injection somewhere, since quoting parameters "by hand" is a recipe for bugs.
That said it may not be a SQL injection, it could also be a remote code execution, remote shell, etc.
If you're using any outdated web app (php forum or whatever) then it needs to be patched and updated.
Assuming your IIS server uses a database user "webuser" to connect to the database.
Create a new database user, say "user2" who will have all permissions. The idea is to restrict webuser as much as possible. The goal is to prevent the bad stuff from happening, while also raising an error so you can trace it and patch the hole. These are not permanent fixes, but ways to get more information.
First, if your app doesn't need to list tables or do anything with information_schema, just revoke all privileges on it and the system tables so webuser can't list tables.
Webuser is probably the owner of the tables, which means it can do anything to them including DROP. In order to prevent that, you can grant ownership of all tables to user2, and revoke ownership of everything (database, table, etc) from webuser. Then selectively grant only the needed privileges to webuser (ie, select, update, delete).