Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WXXzK-0003eB-II for pgsql-admin@arkaria.postgresql.org; Tue, 08 Apr 2014 15:31:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WXXzK-0007Oz-1I for pgsql-admin@arkaria.postgresql.org; Tue, 08 Apr 2014 15:31:02 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WXXzI-0007NF-J1 for pgsql-admin@postgresql.org; Tue, 08 Apr 2014 15:31:00 +0000 Received: from mail-ie0-f171.google.com ([209.85.223.171]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WXXzD-0007P0-R2 for pgsql-admin@postgresql.org; Tue, 08 Apr 2014 15:30:59 +0000 Received: by mail-ie0-f171.google.com with SMTP id ar20so1082005iec.16 for ; Tue, 08 Apr 2014 08:30:54 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:date:from:to:message-id:in-reply-to:references :subject:mime-version:content-type; bh=Di+OkK/k7Twjo1BIDJlBjH1Zr8xvqsE3HWZUld/eRqU=; b=ORrYfDG8DL8bFozH1o4//LK2Xz3pZ7U1fmByBgO76fJ95/Z22il3UAhk/8PkaJdJbl GJG70MLEDptZ2G3p5eFSlZx4NUm1f0q/TPYqu+GjohkqUDhNF1Kx5pjkUjq3dedGSHHx FwlhG5SRixLXiLbm9+RacHr4r2a2Q80tiV3hdUcO2FWFizEq0uF746jb/zqiZu1udOSX MkDROyqWN+yIhF9UQosWKT6M5XF90/DquQDJArbyUlyLQZ6ADQCNRP/rU+uydBCIe5LB lqsO8sigp+gaXvX16Iw0hV1RO0hvzBY9egkJArnyiXP6rWY/OcKsFSwNReCHuisaMZQE ZWXw== X-Gm-Message-State: ALoCoQmp1dy1Z7ENHStVdvYBLDi/3hPTg2UEiQj7J2OgUAvuQmIKyEoFRgtb+bjJ+9MKldnSn7wf7xNaYNUMCb0tFbkz3lOnPZvJg4e9emV3/9iP2Ok/8oY= X-Received: by 10.50.43.170 with SMTP id x10mr4841280igl.20.1396971054077; Tue, 08 Apr 2014 08:30:54 -0700 (PDT) Received: from palos ([50.76.140.65]) by mx.google.com with ESMTPSA id zz2sm1205156igc.0.2014.04.08.08.30.52 for (version=TLSv1.2 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 08 Apr 2014 08:30:53 -0700 (PDT) Date: Tue, 8 Apr 2014 09:30:51 -0600 From: Jason Mathis To: Walter Hurry , pgsql-admin@postgresql.org Message-ID: In-Reply-To: References: <5342DA72.2030103@pinpointresearch.com> Subject: Re: How to find table creation time X-Mailer: Airmail (237) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="5344162b_327b23c6_16b" X-Pg-Spam-Score: -0.7 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --5344162b_327b23c6_16b Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Walter, Thanks for your feedback and suggestions. Although I believe you may have m= isunderstood or assumed a few key points on this thread. The original quest= ion was just to get the creation time of tables. I answered that question a= nd gave an example of different ideas on how the increased logging could be= nefit your infrastructure.=C2=A0 In terms of auditing ddl changes in the logs, yes I do want an email. We al= l have different rules and regulations we need to follow. Most of us have m= ore than one dba (or superuser) working in house. Or maybe you just started= at a new place and wanted to quickly get a handle on whats going on.=C2=A0 As for disaster recovery, I like to take the approach of =E2=80=9Cwhen it h= appens=E2=80=9D more than =E2=80=9Cit will never happen because I did xyz.= =E2=80=9D It will rain, lets be ready for it. I was not implying to forgo a= =E2=80=9Csecurity audit=E2=80=9D or give every user super permissions, tha= t will be just silly. In fact we just underwent a security review here but = I would still like to have this in place. This additional logging/parsing i= s a second line of defense. When it does go down (think junior dba or oops = I thought that was dev) at least you will know right away, have the exact s= econd it happened and a paper trail. Pretty nice uh? I bet the boy scouts w= ould be jealous about that:)=C2=A0 Embrace those logs, parse them out, send emails, alerts, whatever. =C2=A0No= body, system, or process is perfect. It will rain, how prepared are you goi= ng to be?=C2=A0 Hoping for many sunny days ahead! -jason=C2=A0 On April 7, 2014 at 6:26:38 PM, Walter Hurry (walterhurry@gmail.com) wrote: Jason Mathis wrote: =20 > someone =E2=80=9Csneaking in=E2=80=9D a change. Or even think about data = recovery, =E2=80=9Cwhat time did you drop that production table?=E2=80=9D = =20 Whaaat? You need to do a security review *now*. These possibilities indicat= e anarchy, and are a sure recipe for disaster. Someone has dropped a produc= tion table and you want it to be logged, or to be emailed about it? =20 You don't *grant* privileges to drop production tables to all and sundry. = =20 -- =20 Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) =20 To make changes to your subscription: =20 http://www.postgresql.org/mailpref/pgsql-admin =20 --=20 =20 This transmission contains confidential and privileged information intended= =20 solely for the party identified above. If you receive this message in=20 error, you must not use it or convey it to others. Please destroy it=20 immediately and contact the sender at (303) 386-3955 or by return e-mail to= =20 the sender. --5344162b_327b23c6_16b Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline =

Hi Walter,

Thanks for your feedback and suggestions. Alt= hough I believe you may have misunderstood or assumed a few key points on t= his thread. The original question was just to get the creation time of tabl= es. I answered that question and gave an example of different ideas on how = the increased logging could benefit your infrastructure.

In terms of auditi= ng ddl changes in the logs, yes I do want an email. We all have different r= ules and regulations we need to follow. Most of us have more than one dba (= or superuser) working in house. Or maybe you just started at a new place an= d wanted to quickly get a handle on whats going on.

As for disaster recovery= , I like to take the approach of =E2=80=9Cwhen it happens=E2=80=9D more tha= n =E2=80=9Cit will never happen because I did xyz.=E2=80=9D It will rain, l= ets be ready for it. I was not implying to forgo a =E2=80=9Csecurity audit= =E2=80=9D or give every user super permissions, that will be just silly. In= fact we just underwent a security review here but I would still like to ha= ve this in place. This additional logging/parsing is a second line of defen= se. When it does go down (think junior dba or oops I thought that was dev) = at least you will know right away, have the exact second it happened and a = paper trail. Pretty nice uh? I bet the boy scouts would be jealous about th= at:)

Embrace those logs, parse them out, send emails, alerts, whatever. &nbs= p;Nobody, system, or process is perfect. It will rain, how prepared are you= going to be?

Hoping for many sunny days ahead!

-jason

On April 7, 2014 at 6:26:38 PM, Walter Hurry (walterhurry@gmail.com) wrote:

Jason Mathis wrot= e:

> someone =E2=80=9Csneaking in=E2=80=9D a change. Or even think abou= t data recovery, =E2=80=9Cwhat time did you drop that production table?=E2= =80=9D

Whaaat? You need to do a security review *now*. These possibilities ind= icate anarchy, and are a sure recipe for disaster. Someone has dropped a pr= oduction table and you want it to be logged, or to be emailed about it?

You don't *grant* privileges to drop production tables to all and sundr= y.

-- =20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

This transmission contains confidential = and privileged information intended solely for the party identified above. If you receive = this message in error, you must not use it or convey it to others. Please destroy it immediately and contact the se= nder at (303) 386-3955 or by return e-mail to the sender.

--5344162b_327b23c6_16b--