Thread: PGSQL 10, many Random named DB
On Wed, Jan 24, 2018 at 12:40 PM, Rob Sargent <robjsargent@gmail.com> wrote:
Step one for me would be to unplug the ethernet cable. If it in some unaccessible place shut it down, make it accessible, then go with Merlins list.
On 01/24/2018 10:22 AM, Merlin Moncure wrote:On Wed, Jan 24, 2018 at 4:52 AM, Durumdara <durumdara@gmail.com> wrote:Hello!You could be looking at a very serious situation. Random data stored
Somewhere the system administrator (who don't know the PG really) installed
a PGSQL server (10.x) with a database.
He couldn't manage the server well.
Yesterday my colleague saw 21 databases in this server with random names.
He checked it with built in PGAdmin IV.
Today we checked it again, and we saw 33 databases.
The first name is "ahucli" for example - like an aztec king... :-).
The server OS is Windows, the PGSQL is 10.x.
What can cause this strange thing?
1.) PGAdmin IV bug?
2.) Their server is hacked/cracked from outside?
3.) A wrong configured tool, or an automation?
4.) "Alien invasion", etc.
Did you see same thing anywhere?
Thank you for any advice in this theme!
without your knowledge can be symptom of a hack or simple bug.
Figuring out which is which is a very urgent consideration. You may
want to consider:
*) poke around created database and try to determine if the created
databases point to something you created or more suspicious things.
this is URGENT
*) review firewall and network configuration
*) review pg_hba.conf
*) generally check logs everywhere, be advised hackers are often smart
and covert tracks
*) log all connections. adjust logging to also capture client ip and
pid if not already
*) log all queries (also with ajustments above). this is expensive,
so be prepared to turn off when problem is found
merlin
In addition to all previous suggestions, you might also want to consider the following.
1. Look at the schemas of the existings and new databases. Are they similar? If so,
this is possibly a condition of poor application design, where each new client/customer gets their own database.
2. Are the existing / new databases growing in size, or they becoming stable?
Use the following queries to check their sizes daily (or hourly).
SELECT oid,
datname,
pg_size_pretty(pg_database_size(datname))as size_pretty,
pg_database_size(datname) as size,
(SELECT pg_size_pretty (SUM( pg_database_size(datname))::bigint)
FROM pg_database) AS total,
((pg_database_size(datname) / (SELECT SUM( pg_database_size(datname))
FROM pg_database) ) * 100)::numeric(6,3) AS pct
FROM pg_database
ORDER BY datname;
SELECT n.nspname as schema,
c.relname as table,
a.rolname as owner,
c.relfilenode as filename,
c.reltuples::bigint,
pg_size_pretty(pg_relation_size(n.nspname|| '.' || c.relname)) as size,
pg_size_pretty(pg_total_relation_size(n.nspname|| '.' || c.relname)) as total_size,
pg_relation_size(n.nspname|| '.' || c.relname) as size_bytes,
pg_total_relation_size(n.nspname|| '.' || c.relname) as total_size_bytes,
CASE WHEN c.reltablespace = 0
THEN 'pg_default'
ELSE (SELECT t.spcname
FROM pg_tablespace t
WHERE (t.oid = c.reltablespace) )
END as tablespace
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE n.nspname NOT LIKE 'pg_%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;
--
END as tablespace
FROM pg_class c
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.oid = c.relowner )
WHERE n.nspname NOT LIKE 'pg_%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind IN ('r')
ORDER BY total_size_bytes DESC, 1, 2;
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.