Re: PGSQL 10, many Random named DB

[Merlin Moncure]

On Thu, Jan 25, 2018 at 3:38 AM, Durumdara  wrote:
> Dear Members!
>
> Thank you for the suggestions.
> Yes, the sysadmin allowed incoming connections from net.
> We will check your list when we are there.

Don't bother.  We have a confirmed attack, time to take immediate remediation.

1. Disconnect machine from network _immediately_ and move to DMZ
2. Create new server and dump/restore data to the new machine
3. Determine if hacker was able to compromise to the rest of your
network. Warning signs:
  *) installed extensions you didn't install dblink, plsh, plperl, etc
  *) strange errors in database log
  *) starnge errors syslog (is this linux/unix?)
4. Poor network security (hands free ps access to other boxes, etc)

Put short, you have to assume your network is compromised and only
change that assumption when disproven.  These days, if hacker has
shell access you pretty much have reinstall the box from the ground
up.  Root escalation is a thing (there are probably meltdown related
escalations out there) and once the hacker has root it's game over.
In fact, in some cases you have to throw out the hardware too.

merlin

Re: PGSQL 10, many Random named DB

[Laurenz Albe]

Durumdara wrote:
> 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.

Must be aliens.

Yours,
Laurenz Albe

Re: PGSQL 10, many Random named DB

[Durumdara]

Dear Members!

Thank you for the suggestions.
Yes, the sysadmin allowed incoming connections from net.
We will check your list when we are there.

Thank you again!

Best wishes

dd

Re: PGSQL 10, many Random named DB

[Melvin Davidson]

On Wed, Jan 24, 2018 at 12:40 PM, Rob Sargent  wrote:

>
>
> On 01/24/2018 10:22 AM, Merlin Moncure wrote:
>
>> On Wed, Jan 24, 2018 at 4:52 AM, Durumdara  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.

Re: PGSQL 10, many Random named DB

[Vincenzo Romano]

2018-01-24 12:18 GMT+01:00 Condor :
> On 24-01-2018 12:52, Durumdara 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!
>>
>> Best regards
>>dd
>
>
>
>
> I bet on 2.
> probably did not setup pg_hba.conf file.
>
> Regards,
> HC
>

The logs are your friends.
Maybe you can track down those "CREATE DATABASE" commands with
timestamps and IP addresses.
There could be a (local rogue) piece of software that does this trick.
Again, check with logs.

-- 
Vincenzo Romano - NotOrAnd.IT
Information Technologies
--
NON QVIETIS MARIBVS NAVTA PERITVS

Re: PGSQL 10, many Random named DB

[Condor]

On 24-01-2018 12:52, Durumdara 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!

Best regards
   dd




I bet on 2.
probably did not setup pg_hba.conf file.

Regards,
HC

Re: PGSQL 10, many Random named DB

[Adam Tauno Williams]

> 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?

Are their connections to the server?  What does "netstat" tell you?

-- 
Meetings Coordinator, Michigan Association of Railroad Passengers
537 Shirley St NE Grand Rapids, MI 49503-1754 Phone: 616.581.8010
E-mail: awill...@whitemice.org GPG#D95ED383 Web: http://www.marp.org

PGSQL 10, many Random named DB

[Durumdara]

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!

Best regards
   dd