Re: PGSQL 10, many Random named DB

On Wed, Jan 24, 2018 at 12:40 PM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

>
>
> On 01/24/2018 10:22 AM, Merlin Moncure wrote:
>
>> On Wed, Jan 24, 2018 at 4:52 AM, Durumdara <durumdara(at)gmail(dot)com> wrote:
>>
>>> Hello!
>>>
>>> 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!
>>>
>> You could be looking at a very serious situation. Random data stored
>> 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
>>
>> 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.
>
>
>
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;

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.