agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedFrom: George Weaver <gweaver@shaw.ca>
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Detect who ran DROP schema
Date: Wed, 24 Jul 2024 13:45:36 -0500
Message-ID: <ddf201bf-303f-4469-941d-12055ea05c9f@shaw.ca> (raw)
In-Reply-To: <202407241722.yigc7p4tnajc@alvherre.pgsql>
References: <202407241722.yigc7p4tnajc@alvherre.pgsql>
>It's better to have one elevated user _without login privs_, to which
people can SET ROLE when they require it.
This sounds interesting, but I'm not sure how to do it. Would you mind
sharing an example?
Thanks,
George
On 24/07/2024 12:22 p.m., Alvaro Herrera wrote:
> 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.
>
--
972 McMillan Avenue
Winnipeg, MB
R3M 0V7
(204) 284-9839 phone/cell
view thread (15+ messages) latest in thread
Message-ID: <ddf201bf-303f-4469-941d-12055ea05c9f@shaw.ca>
Permalink: ../ddf201bf-303f-4469-941d-12055ea05c9f@shaw.ca/
Also on: postgresql.org/message-id/ddf201bf-303f-4469-941d-12055ea05c9f@shaw.ca
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gweaver@shaw.ca, alvherre@alvh.no-ip.org, pgsql-admin@lists.postgresql.org
Subject: Re: [EXTERNAL] Re: Detect who ran DROP schema
In-Reply-To: <ddf201bf-303f-4469-941d-12055ea05c9f@shaw.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox