On 12/17/25 11:25, Matthias Leisi wrote:
>
>> pgaudit might satisfy your needs, since it would only log SELECT
>> statements on that one table. You'd still have to grep the log file,
>> so the information wouldn't be real-time, but that's /probably/ not
>> important.
>
> That’s a viable suggestion, thanks a lot. Real-time is indeed not
> necessary, a daily (or even a weekly) cleaning of unused data is
> sufficient. pgaudit was anyway on the table for some other use cases, so
> that would fit in nicely.
Possibly try using/abusing RLS?
8<-----------------
psql test
psql (19devel)
Type "help" for help.
create table t1(c1 int, c2 text);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(42,'zp');
grant select on table t1 to public;
create table a1(c1 int, t1 timestamptz);
create or replace function audit(int)
returns bool as
$$
insert into a1 values($1, now()) returning true
$$ security definer language sql;
create policy audit_t1 ON t1 for select using (audit(c1));
alter table t1 enable row level security;
create user joe;
set session authorization joe;
select * from t1 where c1=42;
c1 | c2
----+----
42 | zp
(1 row)
reset session authorization;
select * from a1;
c1 | t1
----+-------------------------------
42 | 2025-12-17 11:42:51.871843-05
(1 row)
8<-----------------
HTH,
--
Joe Conway
PostgreSQL Contributors Team
Amazon Web Services: https://aws.amazon.com
