public inbox for [email protected]
help / color / mirror / Atom feedIs PgAudit extension database specific?
6+ messages / 5 participants
[nested] [flat]
* Is PgAudit extension database specific?
@ 2026-01-06 17:18 Siraj G <[email protected]>
2026-01-06 17:26 ` Re: Is PgAudit extension database specific? David G. Johnston <[email protected]>
2026-01-06 17:29 ` Re: Is PgAudit extension database specific? zaidagilist <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Siraj G @ 2026-01-06 17:18 UTC (permalink / raw)
To: Pgsql-admin <[email protected]>
Hello Experts!
Here is my observation:
Connected to postgres DB and ran:
CREATE EXTENSION pgaudit;
Within postgres DB, I ran this:
SELECT * FROM pg_catalog.pg_extension WHERE extname = 'pgaudit';
It does return a row.
I changed the DB and ran the same SQL, but it does not return any thing.
The PostgreSQL instance is a GCP cloud SQL running with postgres V14.19.
Would you help me understand this, please.
Regards
Siraj
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is PgAudit extension database specific?
2026-01-06 17:18 Is PgAudit extension database specific? Siraj G <[email protected]>
@ 2026-01-06 17:26 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2026-01-06 17:26 UTC (permalink / raw)
To: Siraj G <[email protected]>; +Cc: Pgsql-admin <[email protected]>
On Tue, Jan 6, 2026 at 10:19 AM Siraj G <[email protected]> wrote:
> Connected to postgres DB and ran:
> CREATE EXTENSION pgaudit;
>
>
"create extension" always executes strictly within the database you are
connected to. It may have effects that go beyond that database, depending
on what the extension does, but absent documentation to the contrary assume
that an extension only operates within the database it was installed into.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is PgAudit extension database specific?
2026-01-06 17:18 Is PgAudit extension database specific? Siraj G <[email protected]>
@ 2026-01-06 17:29 ` zaidagilist <[email protected]>
2026-01-06 17:55 ` Re: Is PgAudit extension database specific? Dharin Shah <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: zaidagilist @ 2026-01-06 17:29 UTC (permalink / raw)
To: Siraj G <[email protected]>; +Cc: Pgsql-admin <[email protected]>
Hello Siraj,
pgAudit is a coupled component build for a specific PG version, for each PG
version you are required to install separately. Same behavior as other PG
based extensions.
Regards,
Zaid
On Tue, Jan 6, 2026 at 10:19 PM Siraj G <[email protected]> wrote:
> Hello Experts!
>
> Here is my observation:
>
> Connected to postgres DB and ran:
> CREATE EXTENSION pgaudit;
>
> Within postgres DB, I ran this:
> SELECT * FROM pg_catalog.pg_extension WHERE extname = 'pgaudit';
>
> It does return a row.
>
> I changed the DB and ran the same SQL, but it does not return any thing.
>
> The PostgreSQL instance is a GCP cloud SQL running with postgres V14.19.
>
> Would you help me understand this, please.
>
> Regards
> Siraj
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is PgAudit extension database specific?
2026-01-06 17:18 Is PgAudit extension database specific? Siraj G <[email protected]>
2026-01-06 17:29 ` Re: Is PgAudit extension database specific? zaidagilist <[email protected]>
@ 2026-01-06 17:55 ` Dharin Shah <[email protected]>
2026-01-15 15:02 ` Re: Is PgAudit extension database specific? Pierre Forstmann <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Dharin Shah @ 2026-01-06 17:55 UTC (permalink / raw)
To: zaidagilist <[email protected]>; +Cc: Siraj G <[email protected]>; Pgsql-admin <[email protected]>
Hello,
This is standard for all pg extensions as mentioned above.
Just adding a minor doc patch to add clarification in the doc.
Thanks,
Dharin
On Tue, Jan 6, 2026 at 6:30 PM zaidagilist <[email protected]> wrote:
> Hello Siraj,
>
> pgAudit is a coupled component build for a specific PG version, for each
> PG version you are required to install separately. Same behavior as other
> PG based extensions.
>
> Regards,
> Zaid
>
> On Tue, Jan 6, 2026 at 10:19 PM Siraj G <[email protected]> wrote:
>
>> Hello Experts!
>>
>> Here is my observation:
>>
>> Connected to postgres DB and ran:
>> CREATE EXTENSION pgaudit;
>>
>> Within postgres DB, I ran this:
>> SELECT * FROM pg_catalog.pg_extension WHERE extname = 'pgaudit';
>>
>> It does return a row.
>>
>> I changed the DB and ran the same SQL, but it does not return any thing.
>>
>> The PostgreSQL instance is a GCP cloud SQL running with postgres V14.19.
>>
>> Would you help me understand this, please.
>>
>> Regards
>> Siraj
>>
>>
Attachments:
[application/octet-stream] v1-0001-doc-clarify-pg_extension-is-per-database-not-cluster.patch (1.1K, 3-v1-0001-doc-clarify-pg_extension-is-per-database-not-cluster.patch)
download | inline diff:
From 9f586cb039bcac40862db222a16a8550ec3cae42 Mon Sep 17 00:00:00 2001
From: Dharin Shah <[email protected]>
Date: Tue, 6 Jan 2026 18:45:44 +0100
Subject: [PATCH] doc: clarify pg_extension is per-database, not cluster-wide
---
doc/src/sgml/catalogs.sgml | 7 +++++++
1 file changed, 7 insertions(+)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2fc63442980..fa13f9a9d5c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -3964,6 +3964,13 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
for details about extensions.
</para>
+ <para>
+ Like most system catalogs, <structname>pg_extension</structname> is not
+ shared across databases: there is one copy of <structname>pg_extension</structname>
+ per database, not one per cluster. Consequently, extensions must be
+ installed separately in each database where they are needed.
+ </para>
+
<table>
<title><structname>pg_extension</structname> Columns</title>
<tgroup cols="1">
--
2.39.3 (Apple Git-146)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is PgAudit extension database specific?
2026-01-06 17:18 Is PgAudit extension database specific? Siraj G <[email protected]>
2026-01-06 17:29 ` Re: Is PgAudit extension database specific? zaidagilist <[email protected]>
2026-01-06 17:55 ` Re: Is PgAudit extension database specific? Dharin Shah <[email protected]>
@ 2026-01-15 15:02 ` Pierre Forstmann <[email protected]>
2026-01-15 15:10 ` Re: Is PgAudit extension database specific? Pierre Forstmann <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Pierre Forstmann @ 2026-01-15 15:02 UTC (permalink / raw)
To: Dharin Shah <[email protected]>; zaidagilist <[email protected]>; David G. Johnston <[email protected]>; +Cc: Siraj G <[email protected]>; Pgsql-admin <[email protected]>
I have just tested pgaudit 18 with PostgreSQL 18.1.
I have only installed pgaudit in postgres database and I have created 2
other databases db1 and db2.
If I run:
postgres@alma ~]$ psql db1
psql (18.1)
Type "help" for help.
db1=# \dx
List of installed extensions
Name | Version | Default version | Schema | Description
---------+---------+-----------------+------------+------------------------------
plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural
language
(1 row)
db1=# select * from t1;
x
---
(0 rows)
db1=#
\q
[postgres@alma ~]$ psql db2
psql (18.1)
Type "help" for help.
db2=# \dx
List of installed extensions
Name | Version | Default version | Schema | Description
---------+---------+-----------------+------------+------------------------------
plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural
language
(1 row)
db2=# select * from t2;
x
---
(0 rows)
db2=#
\q
[postgres@alma ~]$
I get in PG log:
2026-01-15 15:58:23.044 CET [47325] LOG: AUDIT:
SESSION,1,1,READ,SELECT,,,"SELECT e.extname AS ""Name"", e.extversion AS
""Version"", ae.default_version AS ""Default version"",n.nspname AS
""Schema"", d.description AS ""Description""
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description d ON
d.objoid = e.oid AND d.classoid =
'pg_catalog.pg_extension'::pg_catalog.regclass LEFT JOIN
pg_catalog.pg_available_extensions() ae(name, default_version, comment)
ON ae.name = e.extname
ORDER BY 1",<not logged>
2026-01-15 15:58:29.192 CET [47325] LOG: AUDIT:
SESSION,2,1,READ,SELECT,,,select * from t1,<not logged>
2026-01-15 15:58:39.620 CET [47332] LOG: AUDIT:
SESSION,1,1,READ,SELECT,,,"SELECT e.extname AS ""Name"", e.extversion AS
""Version"", ae.default_version AS ""Default version"",n.nspname AS
""Schema"", d.description AS ""Description""
FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description d ON
d.objoid = e.oid AND d.classoid =
'pg_catalog.pg_extension'::pg_catalog.regclass LEFT JOIN
pg_catalog.pg_available_extensions() ae(name, default_version, comment)
ON ae.name = e.extname
ORDER BY 1",<not logged>
2026-01-15 15:58:42.416 CET [47332] LOG: AUDIT:
SESSION,2,1,READ,SELECT,,,select * from t2,<not logged>
We can conclude that installing pgaudit extension works for all databases.
It seems also that pgaudit does not log database name (I have not
anything pgaudit documentation about database name).
Le 06/01/2026 à 18:55, Dharin Shah a écrit :
> Hello,
>
> This is standard for all pg extensions as mentioned above.
>
> Just adding a minor doc patch to add clarification in the doc.
>
> Thanks,
> Dharin
>
> On Tue, Jan 6, 2026 at 6:30 PM zaidagilist <[email protected]> wrote:
>
> Hello Siraj,
>
> pgAudit is a coupled component build for a specific PG version,
> for each PG version you are required to install separately. Same
> behavior as other PG based extensions.
>
> Regards,
> Zaid
>
> On Tue, Jan 6, 2026 at 10:19 PM Siraj G <[email protected]> wrote:
>
> Hello Experts!
>
> Here is my observation:
>
> Connected to postgres DB and ran:
> CREATE EXTENSION pgaudit;
>
> Within postgres DB, I ran this:
> SELECT*FROMpg_catalog.pg_extension WHEREextname ='pgaudit';
>
> It does return a row.
>
> I changed the DB and ran the same SQL, but it does not return
> any thing.
>
> The PostgreSQL instance is a GCP cloud SQL running with
> postgres V14.19.
>
> Would you help me understand this, please.
>
> Regards
> Siraj
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Is PgAudit extension database specific?
2026-01-06 17:18 Is PgAudit extension database specific? Siraj G <[email protected]>
2026-01-06 17:29 ` Re: Is PgAudit extension database specific? zaidagilist <[email protected]>
2026-01-06 17:55 ` Re: Is PgAudit extension database specific? Dharin Shah <[email protected]>
2026-01-15 15:02 ` Re: Is PgAudit extension database specific? Pierre Forstmann <[email protected]>
@ 2026-01-15 15:10 ` Pierre Forstmann <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Pierre Forstmann @ 2026-01-15 15:10 UTC (permalink / raw)
To: Dharin Shah <[email protected]>; zaidagilist <[email protected]>; David G. Johnston <[email protected]>; +Cc: Siraj G <[email protected]>; Pgsql-admin <[email protected]>
if parameter log_line_prefix uses %d parameter then you get database
name in log;
2026-01-15 16:09:05.784 CET [47861] db1 LOG: AUDIT:
SESSION,1,1,READ,SELECT,,,select * from t1,<not logged>
2026-01-15 16:09:11.502 CET [47864] db2 LOG: AUDIT:
SESSION,1,1,READ,SELECT,,,select * from t2,<not logged>
Le 15/01/2026 à 16:02, Pierre Forstmann a écrit :
>
> I have just tested pgaudit 18 with PostgreSQL 18.1.
>
> I have only installed pgaudit in postgres database and I have created
> 2 other databases db1 and db2.
>
> If I run:
>
> postgres@alma ~]$ psql db1
> psql (18.1)
> Type "help" for help.
>
> db1=# \dx
> List of installed extensions
> Name | Version | Default version | Schema | Description
> ---------+---------+-----------------+------------+------------------------------
> plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL
> procedural language
> (1 row)
>
> db1=# select * from t1;
> x
> ---
> (0 rows)
>
> db1=#
> \q
> [postgres@alma ~]$ psql db2
> psql (18.1)
> Type "help" for help.
>
> db2=# \dx
> List of installed extensions
> Name | Version | Default version | Schema | Description
> ---------+---------+-----------------+------------+------------------------------
> plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL
> procedural language
> (1 row)
>
> db2=# select * from t2;
> x
> ---
> (0 rows)
>
> db2=#
> \q
> [postgres@alma ~]$
>
>
> I get in PG log:
>
>
> 2026-01-15 15:58:23.044 CET [47325] LOG: AUDIT:
> SESSION,1,1,READ,SELECT,,,"SELECT e.extname AS ""Name"", e.extversion
> AS ""Version"", ae.default_version AS ""Default version"",n.nspname AS
> ""Schema"", d.description AS ""Description""
> FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n
> ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description d ON
> d.objoid = e.oid AND d.classoid =
> 'pg_catalog.pg_extension'::pg_catalog.regclass LEFT JOIN
> pg_catalog.pg_available_extensions() ae(name, default_version,
> comment) ON ae.name = e.extname
> ORDER BY 1",<not logged>
> 2026-01-15 15:58:29.192 CET [47325] LOG: AUDIT:
> SESSION,2,1,READ,SELECT,,,select * from t1,<not logged>
> 2026-01-15 15:58:39.620 CET [47332] LOG: AUDIT:
> SESSION,1,1,READ,SELECT,,,"SELECT e.extname AS ""Name"", e.extversion
> AS ""Version"", ae.default_version AS ""Default version"",n.nspname AS
> ""Schema"", d.description AS ""Description""
> FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n
> ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description d ON
> d.objoid = e.oid AND d.classoid =
> 'pg_catalog.pg_extension'::pg_catalog.regclass LEFT JOIN
> pg_catalog.pg_available_extensions() ae(name, default_version,
> comment) ON ae.name = e.extname
> ORDER BY 1",<not logged>
> 2026-01-15 15:58:42.416 CET [47332] LOG: AUDIT:
> SESSION,2,1,READ,SELECT,,,select * from t2,<not logged>
>
>
> We can conclude that installing pgaudit extension works for all
> databases.
>
> It seems also that pgaudit does not log database name (I have not
> anything pgaudit documentation about database name).
>
>
>
> Le 06/01/2026 à 18:55, Dharin Shah a écrit :
>> Hello,
>>
>> This is standard for all pg extensions as mentioned above.
>>
>> Just adding a minor doc patch to add clarification in the doc.
>>
>> Thanks,
>> Dharin
>>
>> On Tue, Jan 6, 2026 at 6:30 PM zaidagilist <[email protected]> wrote:
>>
>> Hello Siraj,
>>
>> pgAudit is a coupled component build for a specific PG version,
>> for each PG version you are required to install separately. Same
>> behavior as other PG based extensions.
>>
>> Regards,
>> Zaid
>>
>> On Tue, Jan 6, 2026 at 10:19 PM Siraj G <[email protected]> wrote:
>>
>> Hello Experts!
>>
>> Here is my observation:
>>
>> Connected to postgres DB and ran:
>> CREATE EXTENSION pgaudit;
>>
>> Within postgres DB, I ran this:
>> SELECT*FROMpg_catalog.pg_extension WHEREextname ='pgaudit';
>>
>> It does return a row.
>>
>> I changed the DB and ran the same SQL, but it does not return
>> any thing.
>>
>> The PostgreSQL instance is a GCP cloud SQL running with
>> postgres V14.19.
>>
>> Would you help me understand this, please.
>>
>> Regards
>> Siraj
>>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-01-15 15:10 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-06 17:18 Is PgAudit extension database specific? Siraj G <[email protected]>
2026-01-06 17:26 ` David G. Johnston <[email protected]>
2026-01-06 17:29 ` zaidagilist <[email protected]>
2026-01-06 17:55 ` Dharin Shah <[email protected]>
2026-01-15 15:02 ` Pierre Forstmann <[email protected]>
2026-01-15 15:10 ` Pierre Forstmann <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox