Feedback-ID: ia2694551:Fastmail
Date: Wed, 24 Jul 2024 19:22:23 +0200
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: "Wetmore, Matthew  (CTR)" <Matthew.Wetmore@evernorth.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	Siraj G <tosiraj.g@gmail.com>, sagar jadhav <sagarjdhv5@gmail.com>,
	Wasim Devale <wasimd60@gmail.com>, Kashif Zeeshan <kashi.zeeshan@gmail.com>,
	Muhammad Imtiaz <imtiazpg712@gmail.com>,
 Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: [EXTERNAL] Re: Detect who ran DROP schema
Message-ID: <202407241722.yigc7p4tnajc@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <PH0PR12MB5499C8C2C4B5BECED962F686F5AA2@PH0PR12MB5499.namprd12.prod.outlook.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202407241722.yigc7p4tnajc%40alvherre.pgsql>
Precedence: bulk

On 2024-Jul-24, Wetmore, Matthew  (CTR) wrote:

> This is a major issue in the DBA world as enterprise management lawyers get more popular.
> 
> At a large company I was at, there was only one elevated user, (which several people had user/pass) and then our personal accounts cannot do much due to modern corporate governance.  This is how it was set up.
> 
> As the DBA I couldn’t even log into the linux box where postgres was installed.
> 
> I couldn’t even change any logging without a two day ticket to do the work.
> 
> Not specifically this issue, but this is more the norm now-a-days then not.

Yeah.  This is an important if there are any potential attackers at all,
which given today's Internet, you can be pretty sure is always the case.

A database where people are allowed to connect as superuser is a sure
way to get in trouble sooner rather than later.  Having layered security
is one of the first things you should be thinking about.

FWIW I think even that one elevated user to which several people have
user/pass is a bad idea; forensics would require to know who used the
password when.  It's better to have one elevated user _without login privs_,
to which people can SET ROLE when they require it.  This leaves a better
trail.

If you add something like pgAudit to the mix and direct its logs (or all
Postgres logs) to a remote server where they can't easily be tampered
with by attackers, you'll have a better trail of who did what, when,
with what credentials.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"I can't go to a restaurant and order food because I keep looking at the
fonts on the menu.  Five minutes later I realize that it's also talking
about food" (Donald Knuth)