MIME-Version: 1.0
References: <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>
 <CAPnRvGvHAC8HjLCZ2uUBCRZ7=fMJnkTPd3cBOchJu-Vn7G2MHg@mail.gmail.com>
 <CAEzWdqfUWE=fuR5k1rd1iQx4K1-LceW3razY8f_tb9YONAZRMQ@mail.gmail.com>
 <DBAPR01MB6840804A91ABB765B0A07EACF29C2@DBAPR01MB6840.eurprd01.prod.exchangelabs.com>
 <b683944c-63f2-456f-a423-efe743fc0769@aklaver.com> <242ee502-4b8e-49b5-b2f9-ffba6c678ca2@gmail.com>
In-Reply-To: <242ee502-4b8e-49b5-b2f9-ffba6c678ca2@gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 14 Nov 2024 11:32:15 -0500
Message-ID: <CAKAnmm+jC-a_z_UBC5bjw5=gGc4uf3CRvQ4EOgOhO0XOvt95og@mail.gmail.com>
Subject: Re: question on audit columns
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000006167110626e20055"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BjC-a_z_UBC5bjw5%3DgGc4uf3CRvQ4EOgOhO0XOvt95og%40mail.gmail.com>
Precedence: bulk

--0000000000006167110626e20055
Content-Type: text/plain; charset="UTF-8"

As far as the application being able to change those fields itself, you can
prevent that via column permissions, by leaving out the four audit columns
and doing something like:

GRANT INSERT (email, widget_count), UPDATE (email, widget_count) ON TABLE
foobar TO PUBLIC;

That way, inserts are guaranteed to use the default values of
current_timestamp() and current_user. And a BEFORE UPDATE trigger ensures
it changes the other two fields via the trigger function only.

Cheers,
Greg

P.S. Also check out https://www.pgaudit.org/ (PGAudit) as an alternative
approach, which puts the information into your Postgres logs, rather than
in the tables themselves.

--0000000000006167110626e20055
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">As far as the application being able to change those field=
s itself, you can prevent that via column permissions, by leaving out the f=
our audit columns and doing something like:<div><br></div><div>GRANT INSERT=
 (email, widget_count), UPDATE (email, widget_count) ON TABLE foobar TO PUB=
LIC;</div><div><br></div><div>That way, inserts are guaranteed to use the d=
efault=C2=A0values of current_timestamp() and current_user. And a BEFORE UP=
DATE trigger ensures it changes the other two fields via the trigger functi=
on only.</div><div><br></div><div>Cheers,</div><div>Greg</div><div><br></di=
v><div>P.S. Also check out=C2=A0<a href=3D"https://www.pgaudit.org/">https:=
//www.pgaudit.org/</a> (PGAudit) as an alternative approach, which puts the=
 information into your Postgres logs, rather than in the tables themselves.=
=C2=A0=C2=A0</div></div>

--0000000000006167110626e20055--