Content-Type: multipart/alternative;
 boundary="------------IOQv8BqSQf0tXbXMCKjX2br8"
Message-ID: <ddf201bf-303f-4469-941d-12055ea05c9f@shaw.ca>
Date: Wed, 24 Jul 2024 13:45:36 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: [EXTERNAL] Re: Detect who ran DROP schema
Content-Language: en-US
To: Alvaro Herrera <alvherre@alvh.no-ip.org>
References: <202407241722.yigc7p4tnajc@alvherre.pgsql>
Cc: pgsql-admin@lists.postgresql.org
From: George Weaver <gweaver@shaw.ca>
Organization: Cleartag Software, Inc.
In-Reply-To: <202407241722.yigc7p4tnajc@alvherre.pgsql>
Archived-At: 
 <https://www.postgresql.org/message-id/ddf201bf-303f-4469-941d-12055ea05c9f%40shaw.ca>
Precedence: bulk

This is a multi-part message in MIME format.
--------------IOQv8BqSQf0tXbXMCKjX2br8
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

 >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

--------------IOQv8BqSQf0tXbXMCKjX2br8
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>&gt;<span style="white-space: pre-wrap">It's better to have one elevated user </span><span
      class="moz-txt-underscore" style="white-space: pre-wrap;"><span
      class="moz-txt-tag">_</span>without login privs<span
      class="moz-txt-tag">_</span></span><span
      style="white-space: pre-wrap">, </span><span
      style="white-space: pre-wrap">to which people can SET ROLE when they require it.</span></p>
    <p><span style="white-space: pre-wrap">This sounds interesting, but I'm not sure how to do it.  Would you mind sharing an example?</span></p>
    <p><span style="white-space: pre-wrap">Thanks,</span></p>
    <p><span style="white-space: pre-wrap">George
</span></p>
    <div class="moz-cite-prefix">On 24/07/2024 12:22 p.m., Alvaro
      Herrera wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:202407241722.yigc7p4tnajc@alvherre.pgsql">
      <pre class="moz-quote-pre" wrap="">On 2024-Jul-24, Wetmore, Matthew  (CTR) wrote:

</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">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.
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">
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.

</pre>
    </blockquote>
    <pre class="moz-signature" cols="72">-- 
972 McMillan Avenue
Winnipeg, MB
R3M 0V7
(204) 284-9839 phone/cell</pre>
  </body>
</html>

--------------IOQv8BqSQf0tXbXMCKjX2br8--