Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

2025-04-16 Thread Ron Johnson
pgaudit is statement-level, not transaction-level; that's its nature.  This
is the same as log_statement.

On Wed, Apr 16, 2025 at 5:10 AM Achilleas Mantzios - cloud <
a.mantz...@cloud.gatewaynet.com> wrote:

> On 4/15/25 12:14, KENAN ÇİFTÇİ wrote:
>
> Hi,
>
> You can use pgaudit and pgauditlogtofile extension (
> https://github.com/fmbiete/pgauditlogtofile) together to write audit logs
> in a separate file.
>
> One issue we have with pgaudit is that it prints AUDIT records even if the
> xaction gets rollbacked, how do you alleviate that ?
>
>
> yours,
>
> Kenan Çiftçi
>
> On Tue, Apr 15, 2025 at 1:44 PM vijay patil 
> wrote:
>
>> Hi All,
>>
>> We are exploring auditing solutions for our PostgreSQL database and are
>> considering using pgaudit for this purpose. However, we have a few
>> questions:
>>
>>1.
>>
>>*What is the best tool for auditing PostgreSQL databases?*
>>-
>>
>>   We are specifically looking for a solution that offers detailed
>>   auditing capabilities and is compatible with our setup.
>>   2.
>>
>>*Can we store the audit information separately from PostgreSQL logs
>>if we decide to use pgaudit?*
>>-
>>
>>   We would prefer to keep the audit logs in a separate file or
>>   location for easier management and analysis.
>>
>>
>> We appreciate any help or suggestions!
>>
>>
>> Thanks
>>
>> Vijay
>>
>

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!


Re: Fwd: Identify system databases

2025-04-16 Thread Laurenz Albe
On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote:
> On Wed, Apr 16, 2025 at 9:32 AM Laurenz Albe  wrote:
> > On Tue, 2025-04-15 at 17:24 -0700, Adrian Klaver wrote:
> > But then you fortunately cannot drop all databases, because you cannot
> > drop the database you are connected to.
> > 
> > Still, a cluster that is missing "postgres" will give beginners trouble
> 
> Right. Given shared "cluster-wide" objects like roles, databases, etc...
> I'm always found it weird that one must know "a priori" the name of one
> database to connect to, to do anything with PostgreSQL.

It is documented, and it is the default database if you are connecting
with the default database user "postgres", so it is not that bad.

Also, tools like "createdb" connect to that database by default.

> Not being able to drop the DB one's connected to is also a pita.
> If one have the right to do it, then it should just be done, and the
> connection closed.

No, it is a life saver, at least in my opinion.

> Authentication is cluster-wide, not DB specific, so I'd welcome a way to 
> connect
> to the cluster, not a specific DB, and introspect shared-objects,
> including databases
> I'm allowed to connect to, which could be an empty list.

It is deep in the DNA of PostgreSQL that you always have to connect to
a database, unless you establish a replication connection.
I am surprised that you perceive that as a problem or limitation.

> FWIW. And a little OT. And implicit cluster-level mini-DB given access
> just to a subset of catalogs, or at least some limited queries like
> my databases and my roles, nothing else, would be very welcome IMHO. --DD

Well, that's the "postgres" database.

Yours,
Laurenz Albe




Re: Fwd: Identify system databases

2025-04-16 Thread Dominique Devienne
On Wed, Apr 16, 2025 at 4:39 PM Tom Lane  wrote:
> Laurenz Albe  writes:
> > On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote:

So in a way, you guys are saying one should never REVOKE CONNECT ON
DATABASE FROM PUBLIC?

All my DBs are not PUBLIC-accessible.
And inside my DBs, I try to revoke everything from PUBLIC
(USAGE ON TYPES, EXECUTE ON ROUTINES).
Nor do I use the public schema.
And I never use the "built-in" postgres database.
Basically I want all GRANTs to be explicit.

Given the above, I'd want to not provide access to the postgres DB too.
Yet have a way to discover which DBs I can connect to, from the "cluster only".
Naively.

Sounds like you are saying use the "postgres" DB for that, and move on. --DD

D:\>ppg -c acme -d postgres database_ --acls
Connected OK (postgresql://ddevienne@acme/postgres); with SSL
|--|--|---|---|
| Grantor  | Grantee  | Privilege | Grantable |
|--|--|---|---|
| postgres | PUBLIC   | TEMPORARY |NO |
| postgres | PUBLIC   | CONNECT   |NO |
| postgres | postgres | CREATE|NO |
| postgres | postgres | TEMPORARY |NO |
| postgres | postgres | CONNECT   |NO |
|--|--|---|---|
5 ACLs to 2 Grantees from 1 Grantor




Re: Cannot turn track_counts on

2025-04-16 Thread Adrian Klaver

On 4/16/25 02:27, Anton Shepelev wrote:

Hello, all.

I have a Postgres sever with the 'track_counts' setting
stuck in 'off'.  I cannot seem to enable it with either of
   a. ALTER SYSTEM,
   b. ALTER DATABASE,
   c. ALTER USER,
   d. or plain SET.

pg_settings shows:

   name| track_counts
   setting | on
   source  | override


This shows a setting of 'on' not the 'off' you mention in the first 
paragraph.




What does 'override' mean in the 'source' column?  How can I
find where in the system this setting is overridden?



--
Adrian Klaver
adrian.kla...@aklaver.com





Re: Cannot turn track_counts on

2025-04-16 Thread Tom Lane
Anton Shepelev  writes:
> pg_settings shows:

>   name| track_counts
>   setting | on
>   source  | override

> What does 'override' mean in the 'source' column?  How can I
> find where in the system this setting is overridden?

I am fairly certain that there is nothing in core Postgres that
would do that.  PGC_S_OVERRIDE is used to lock down the values
of certain variables that shouldn't be allowed to change, but
track_counts surely isn't one of those.  And a quick grep
through the code finds nothing applying PGC_S_OVERRIDE to it.

What extensions do you have installed?

regards, tom lane




Re: Fwd: Identify system databases

2025-04-16 Thread Tom Lane
"David G. Johnston"  writes:
> But feel free to work out a design and add it to the ToDo list for the v4
> protocol.  The use case seems reasonable and doable (on the basis of the
> replication protocol works).

No, the replication protocol isn't a precedent.  Physical replication
needn't connect to a particular database because it does no catalog
accesses (and hence can't run SQL).  All it's able to do is suck out
the WAL stream.  Logical replication can do SQL --- but it has to
connect to a specific database.

regards, tom lane




Re: Fwd: Identify system databases

2025-04-16 Thread Tom Lane
Laurenz Albe  writes:
> On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote:
>> Authentication is cluster-wide, not DB specific, so I'd welcome a way to 
>> connect
>> to the cluster, not a specific DB, and introspect shared-objects,
>> including databases
>> I'm allowed to connect to, which could be an empty list.

> It is deep in the DNA of PostgreSQL that you always have to connect to
> a database, unless you establish a replication connection.
> I am surprised that you perceive that as a problem or limitation.

That isn't going to change, and here's why not: a lot of the critical
catalogs are per-database not shared.  You aren't going to get
anywhere "introspecting shared objects" when you don't have a copy of
pg_class with which to find the shared catalogs, nor a copy of pg_proc
with which to look up index access method support procedures, etc etc.

You could imagine making up some mini-database that is somehow
forbidden from gaining any user-defined objects and then using
that, but I fail to see why that's a better idea than the
approach we use now.  Not being able to use any user-defined
functions or views seems like a pretty huge handicap.  And this
hypothetical new mini-database *would* be a special snowflake
in a way that none of the existing ones are, in that the system
would have to prevent actions that are perfectly okay in any
other one.  I don't perceive that as a good thing.

(You can, of course, speculate about some major rearchitecting
of the system catalogs that would make this situation different.
I doubt that's going to happen at this point, though.  There's
too much stuff that's dependent on how things are now.)

regards, tom lane




Re: Cannot turn track_counts on

2025-04-16 Thread Daniel Gustafsson
> On 16 Apr 2025, at 16:53, Tom Lane  wrote:
> 
> Anton Shepelev  writes:
>> pg_settings shows:
> 
>>  name| track_counts
>>  setting | on
>>  source  | override
> 
>> What does 'override' mean in the 'source' column?  How can I
>> find where in the system this setting is overridden?
> 
> I am fairly certain that there is nothing in core Postgres that
> would do that.  PGC_S_OVERRIDE is used to lock down the values
> of certain variables that shouldn't be allowed to change, but
> track_counts surely isn't one of those.  And a quick grep
> through the code finds nothing applying PGC_S_OVERRIDE to it.
> 
> What extensions do you have installed?

Also, is this by any chance a managed instance like Amazon RDS or Azure, or is
it a local database under your control?

--
Daniel Gustafsson





Re: Fwd: Identify system databases

2025-04-16 Thread David G. Johnston
On Wed, Apr 16, 2025 at 8:07 AM Dominique Devienne 
wrote:

> On Wed, Apr 16, 2025 at 4:39 PM Tom Lane  wrote:
> > Laurenz Albe  writes:
> > > On Wed, 2025-04-16 at 10:09 +0200, Dominique Devienne wrote:
>
> So in a way, you guys are saying one should never REVOKE CONNECT ON
> DATABASE FROM PUBLIC?
>
> All my DBs are not PUBLIC-accessible.
> And inside my DBs, I try to revoke everything from PUBLIC
> (USAGE ON TYPES, EXECUTE ON ROUTINES).
> Nor do I use the public schema.
> And I never use the "built-in" postgres database.
> Basically I want all GRANTs to be explicit.
>
> Given the above, I'd want to not provide access to the postgres DB too.
>


> Yet have a way to discover which DBs I can connect to, from the "cluster
> only".
>

Kinda surprised you don't consider this a feature...give all of your
databases UUID names and ensure that non-superusers must be told the
databases they are allowed to connect to.

But feel free to work out a design and add it to the ToDo list for the v4
protocol.  The use case seems reasonable and doable (on the basis of the
replication protocol works).

https://wiki.postgresql.org/wiki/Todo#Wire_Protocol_Changes_.2F_v4_Protocol

David J.


Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

2025-04-16 Thread Achilleas Mantzios

On 16/4/25 15:36, Ron Johnson wrote:



pgaudit is statement-level, not transaction-level; that's its nature.  
This is the same as log_statement.

ok, but log_statement prints ROLLBACKs/COMMITs, but pgaudit not.


On Wed, Apr 16, 2025 at 5:10 AM Achilleas Mantzios - cloud 
 wrote:


On 4/15/25 12:14, KENAN ÇİFTÇİ wrote:


Hi,

You can use pgaudit and pgauditlogtofile extension
(https://github.com/fmbiete/pgauditlogtofile) together to write
audit logs in a separate file.

One issue we have with pgaudit is that it prints AUDIT records
even if the xaction gets rollbacked, how do you alleviate that ?


yours,

Kenan Çiftçi

On Tue, Apr 15, 2025 at 1:44 PM vijay patil
 wrote:

Hi All,

We are exploring auditing solutions for our PostgreSQL
database and are considering using |pgaudit| for this
purpose. However, we have a few questions:

1.

*What is the best tool for auditing PostgreSQL databases?*

 *

We are specifically looking for a solution that
offers detailed auditing capabilities and is
compatible with our setup.

2.

*Can we store the audit information separately from
PostgreSQL logs if we decide to use |pgaudit|?*

 *

We would prefer to keep the audit logs in a separate
file or location for easier management and analysis.


We appreciate any help or suggestions!


Thanks

Vijay




--
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

2025-04-16 Thread Ron Johnson
On Wed, Apr 16, 2025 at 3:42 PM Nico Williams  wrote:

> On Wed, Apr 16, 2025 at 02:43:59PM -0400, Ron Johnson wrote:
> > You'll have to bring that up with the PgAudit maintainer.  Note, though,
> > that the purpose of PgAudit is not "recreate the database from audit
> logs";
> > it's "what Auditors care about".  In my experience, auditors do not care
> > about COMMIT and ROLLBACK statements.
>
> I would care about what happened.  To know what happened I'd have to see
> either only audit logs for committed transactions, or also see the
> COMMIT and ROLLBACK statements.
>

Decode the logical replication stream?

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!


Re: Fwd: Identify system databases

2025-04-16 Thread Laurenz Albe
On Wed, 2025-04-16 at 17:06 +0200, Dominique Devienne wrote:
> So in a way, you guys are saying one should never REVOKE CONNECT ON
> DATABASE FROM PUBLIC?

No, not at all.

> All my DBs are not PUBLIC-accessible.
> And inside my DBs, I try to revoke everything from PUBLIC
> (USAGE ON TYPES, EXECUTE ON ROUTINES).
> Nor do I use the public schema.
> And I never use the "built-in" postgres database.
> Basically I want all GRANTs to be explicit.
> 
> Given the above, I'd want to not provide access to the postgres DB too.
> Yet have a way to discover which DBs I can connect to, from the "cluster 
> only".
> Naively.
> 
> Sounds like you are saying use the "postgres" DB for that, and move on.

It is just fine to restrict access to databases as much as you want.

You just need access to a database if you want to run SQL statements,
be that SELECT or CREATE DATABASE.  It's as simple as that.

Restricting access is not wrong per se, but if you randomly restrict
access to everything, that's not so much a security measure as a way
to make the database unusable.

I see no value in restricting USAGE on the data type "text" or EXECUTE
on the function "upper()".  Modifying system objects is not recommended
and can lead to trouble.

Yours,
Laurenz Albe




Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

2025-04-16 Thread Nico Williams
On Wed, Apr 16, 2025 at 03:53:53PM -0400, Ron Johnson wrote:
> On Wed, Apr 16, 2025 at 3:42 PM Nico Williams  wrote:
> > I would care about what happened.  To know what happened I'd have to see
> > either only audit logs for committed transactions, or also see the
> > COMMIT and ROLLBACK statements.
> 
> Decode the logical replication stream?

Yes, or use Debezium or the like which... do just that.




Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

2025-04-16 Thread Ron Johnson
You'll have to bring that up with the PgAudit maintainer.  Note, though,
that the purpose of PgAudit is not "recreate the database from audit logs";
it's "what Auditors care about".  In my experience, auditors do not care
about COMMIT and ROLLBACK statements.

On Wed, Apr 16, 2025 at 1:35 PM Achilleas Mantzios <
a.mantz...@cloud.gatewaynet.com> wrote:

> On 16/4/25 15:36, Ron Johnson wrote:
>
>
> pgaudit is statement-level, not transaction-level; that's its nature.
> This is the same as log_statement.
>
> ok, but log_statement prints ROLLBACKs/COMMITs, but pgaudit not.
>
>
> On Wed, Apr 16, 2025 at 5:10 AM Achilleas Mantzios - cloud <
> a.mantz...@cloud.gatewaynet.com> wrote:
>
>> On 4/15/25 12:14, KENAN ÇİFTÇİ wrote:
>>
>> Hi,
>>
>> You can use pgaudit and pgauditlogtofile extension (
>> https://github.com/fmbiete/pgauditlogtofile) together to write audit
>> logs in a separate file.
>>
>> One issue we have with pgaudit is that it prints AUDIT records even if
>> the xaction gets rollbacked, how do you alleviate that ?
>>
>>
>> yours,
>>
>> Kenan Çiftçi
>>
>> On Tue, Apr 15, 2025 at 1:44 PM vijay patil 
>> wrote:
>>
>>> Hi All,
>>>
>>> We are exploring auditing solutions for our PostgreSQL database and are
>>> considering using pgaudit for this purpose. However, we have a few
>>> questions:
>>>
>>>1.
>>>
>>>*What is the best tool for auditing PostgreSQL databases?*
>>>-
>>>
>>>   We are specifically looking for a solution that offers detailed
>>>   auditing capabilities and is compatible with our setup.
>>>   2.
>>>
>>>*Can we store the audit information separately from PostgreSQL logs
>>>if we decide to use pgaudit?*
>>>-
>>>
>>>   We would prefer to keep the audit logs in a separate file or
>>>   location for easier management and analysis.
>>>
>>>
>>> We appreciate any help or suggestions!
>>>
>>>
>>> Thanks
>>>
>>> Vijay
>>>
>>
>
> --
> Death to , and butter sauce.
> Don't boil me, I'm still alive.
>  lobster!
>
>

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!


Re: Fwd: Identify system databases

2025-04-16 Thread Dominique Devienne
On Wed, Apr 16, 2025 at 9:32 AM Laurenz Albe  wrote:
> On Tue, 2025-04-15 at 17:24 -0700, Adrian Klaver wrote:
> But then you fortunately cannot drop all databases, because you cannot
> drop the database you are connected to.
>
> Still, a cluster that is missing "postgres" will give beginners trouble

Right. Given shared "cluster-wide" objects like roles, databases, etc...
I'm always found it weird that one must know "a priori" the name of one
database to connect to, to do anything with PostgreSQL.

Not being able to drop the DB one's connected to is also a pita.
If one have the right to do it, then it should just be done, and the
connection closed.

Authentication is cluster-wide, not DB specific, so I'd welcome a way to connect
to the cluster, not a specific DB, and introspect shared-objects,
including databases
I'm allowed to connect to, which could be an empty list.

FWIW. And a little OT. And implicit cluster-level mini-DB given access
just to a subset of catalogs, or at least some limited queries like
my databases and my roles, nothing else, would be very welcome IMHO. --DD




Cannot turn track_counts on

2025-04-16 Thread Anton Shepelev
Hello, all.

I have a Postgres sever with the 'track_counts' setting
stuck in 'off'.  I cannot seem to enable it with either of
  a. ALTER SYSTEM,
  b. ALTER DATABASE,
  c. ALTER USER,
  d. or plain SET.

pg_settings shows:

  name| track_counts
  setting | on
  source  | override

What does 'override' mean in the 'source' column?  How can I
find where in the system this setting is overridden?

-- 
()  ascii ribbon campaign -- against html e-mail
/\  www.asciiribbon.org   -- against proprietary attachments




Re: Fwd: Identify system databases

2025-04-16 Thread Laurenz Albe
On Tue, 2025-04-15 at 17:24 -0700, Adrian Klaver wrote:
> I know I can use --template=template with createdb, but that still means 
> there has to be a database existing in the initial cluster to use as a 
> template. You can't get around that fact.

Right.

But then you fortunately cannot drop all databases, because you cannot
drop the database you are connected to.

Still, a cluster that is missing "postgres" will give beginners trouble,
a cluster that is missing "template1" is annoying, and a cluster without
"template0" is possibly in serious trouble.

Yours,
Laurenz Albe




Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

2025-04-16 Thread Achilleas Mantzios - cloud

On 4/15/25 12:14, KENAN ÇİFTÇİ wrote:


Hi,

You can use pgaudit and pgauditlogtofile extension 
(https://github.com/fmbiete/pgauditlogtofile) together to write audit 
logs in a separate file.
One issue we have with pgaudit is that it prints AUDIT records even if 
the xaction gets rollbacked, how do you alleviate that ?


yours,

Kenan Çiftçi

On Tue, Apr 15, 2025 at 1:44 PM vijay patil  
wrote:


Hi All,

We are exploring auditing solutions for our PostgreSQL database
and are considering using |pgaudit| for this purpose. However, we
have a few questions:

1.

*What is the best tool for auditing PostgreSQL databases?*

 *

We are specifically looking for a solution that offers
detailed auditing capabilities and is compatible with our
setup.

2.

*Can we store the audit information separately from PostgreSQL
logs if we decide to use |pgaudit|?*

 *

We would prefer to keep the audit logs in a separate file
or location for easier management and analysis.


We appreciate any help or suggestions!


Thanks

Vijay


Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

2025-04-16 Thread Achilleas Mantzios

On 16/4/25 21:43, Ron Johnson wrote:



You'll have to bring that up with the PgAudit maintainer. Note, 
though, that the purpose of PgAudit is not "recreate the database from 
audit logs"; it's "what Auditors care about". In my experience, 
auditors do not care about COMMIT and ROLLBACK statements.
In my experience auditors care a lot about a statement that happened 
versus a statement that didn't happen.


On Wed, Apr 16, 2025 at 1:35 PM Achilleas Mantzios 
 wrote:


On 16/4/25 15:36, Ron Johnson wrote:



pgaudit is statement-level, not transaction-level; that's its
nature.  This is the same as log_statement.

ok, but log_statement prints ROLLBACKs/COMMITs, but pgaudit not.


On Wed, Apr 16, 2025 at 5:10 AM Achilleas Mantzios - cloud
 wrote:

On 4/15/25 12:14, KENAN ÇİFTÇİ wrote:


Hi,

You can use pgaudit and pgauditlogtofile extension
(https://github.com/fmbiete/pgauditlogtofile) together to
write audit logs in a separate file.

One issue we have with pgaudit is that it prints AUDIT
records even if the xaction gets rollbacked, how do you
alleviate that ?


yours,

Kenan Çiftçi

On Tue, Apr 15, 2025 at 1:44 PM vijay patil
 wrote:

Hi All,

We are exploring auditing solutions for our PostgreSQL
database and are considering using |pgaudit| for this
purpose. However, we have a few questions:

1.

*What is the best tool for auditing PostgreSQL
databases?*

 *

We are specifically looking for a solution that
offers detailed auditing capabilities and is
compatible with our setup.

2.

*Can we store the audit information separately from
PostgreSQL logs if we decide to use |pgaudit|?*

 *

We would prefer to keep the audit logs in a
separate file or location for easier management
and analysis.


We appreciate any help or suggestions!


Thanks

Vijay




-- 
Death to , and butter sauce.

Don't boil me, I'm still alive.
 lobster!




--
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

2025-04-16 Thread Nico Williams
On Wed, Apr 16, 2025 at 02:43:59PM -0400, Ron Johnson wrote:
> You'll have to bring that up with the PgAudit maintainer.  Note, though,
> that the purpose of PgAudit is not "recreate the database from audit logs";
> it's "what Auditors care about".  In my experience, auditors do not care
> about COMMIT and ROLLBACK statements.

I would care about what happened.  To know what happened I'd have to see
either only audit logs for committed transactions, or also see the
COMMIT and ROLLBACK statements.

Nico
-- 




Re: Best Tool for PostgreSQL Auditing and Storing Audit Logs Separately

2025-04-16 Thread Ron Johnson
On Wed, Apr 16, 2025 at 3:15 PM Achilleas Mantzios <
a.mantz...@cloud.gatewaynet.com> wrote:

> On 16/4/25 21:43, Ron Johnson wrote:
>
>
> You'll have to bring that up with the PgAudit maintainer.  Note, though,
> that the purpose of PgAudit is not "recreate the database from audit logs";
> it's "what Auditors care about".  In my experience, auditors do not care
> about COMMIT and ROLLBACK statements.
>
> In my experience auditors care a lot about a statement that happened
> versus a statement that didn't happen.
>

The SELECT statement happened.  The DELETE statement successfully
completed.  That it was rolled back is great, but the DELETE statement
still successfully completed.

If you don't like that ROLLBACK and COMMIT are not captured by PgAudit,
open a Request For Feature issue: https://github.com/pgaudit/pgaudit/issues.
Or write a patch then submit a pull request.


>
> On Wed, Apr 16, 2025 at 1:35 PM Achilleas Mantzios <
> a.mantz...@cloud.gatewaynet.com> wrote:
>
>> On 16/4/25 15:36, Ron Johnson wrote:
>>
>>
>> pgaudit is statement-level, not transaction-level; that's its nature.
>> This is the same as log_statement.
>>
>> ok, but log_statement prints ROLLBACKs/COMMITs, but pgaudit not.
>>
>>
>> On Wed, Apr 16, 2025 at 5:10 AM Achilleas Mantzios - cloud <
>> a.mantz...@cloud.gatewaynet.com> wrote:
>>
>>> On 4/15/25 12:14, KENAN ÇİFTÇİ wrote:
>>>
>>> Hi,
>>>
>>> You can use pgaudit and pgauditlogtofile extension (
>>> https://github.com/fmbiete/pgauditlogtofile) together to write audit
>>> logs in a separate file.
>>>
>>> One issue we have with pgaudit is that it prints AUDIT records even if
>>> the xaction gets rollbacked, how do you alleviate that ?
>>>
>>>
>>> yours,
>>>
>>> Kenan Çiftçi
>>>
>>> On Tue, Apr 15, 2025 at 1:44 PM vijay patil 
>>> wrote:
>>>
 Hi All,

 We are exploring auditing solutions for our PostgreSQL database and are
 considering using pgaudit for this purpose. However, we have a few
 questions:

1.

*What is the best tool for auditing PostgreSQL databases?*
-

   We are specifically looking for a solution that offers detailed
   auditing capabilities and is compatible with our setup.
   2.

*Can we store the audit information separately from PostgreSQL logs
if we decide to use pgaudit?*
-

   We would prefer to keep the audit logs in a separate file or
   location for easier management and analysis.


 We appreciate any help or suggestions!


 Thanks

 Vijay

>>>
>>
>> --
>> Death to , and butter sauce.
>> Don't boil me, I'm still alive.
>>  lobster!
>>
>>
>
> --
> Death to , and butter sauce.
> Don't boil me, I'm still alive.
>  lobster!
>
>

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!


Re: Cannot turn track_counts on

2025-04-16 Thread Adrian Klaver

On 4/16/25 09:25, Anton Shepelev wrote:

Adrian Klaver:

Anton Shepelev:




This shows a setting of 'on' not the 'off' you mention in
the first paragraph.


I beg pardon.  Having no immediate access to the system in
question, I confess to having mocked up those results from
an analogous query on our reference system.  Here is the
actual result from the affected server (db name changed):

db=# select * from pg_settings where name = 'track_counts';
-[ RECORD 1 ]---+--
name| track_counts
setting | off
unit|
category| Statistics / Query and Index Statistics Collector
short_desc  | Collects statistics on database activity.
extra_desc  |
context | superuser
vartype | bool
source  | override
min_val |
max_val |
enumvals|
boot_val| on
reset_val   | off
sourcefile  |
sourceline  |
pending_restart | f

It was very wrong of me so to misinform you.



Per post from Daniel Gustafsson:

"Also, is this by any chance a managed instance like Amazon RDS or 
Azure, or is it a local database under your control?"


And from Tom Lane:

"What extensions do you have installed?"


--
Adrian Klaver
adrian.kla...@aklaver.com





Re: Help with PhD Dissertation

2025-04-16 Thread Bruce Momjian
On Tue, Apr 15, 2025 at 05:28:40PM +0200, Karsten Hilbert wrote:
> Am Tue, Apr 15, 2025 at 11:26:35AM -0400 schrieb Osmel Brito-Bigott:
> 
> > I'm not collecting emails in the survey
> 
> Don't get me wrong, I am not questioning your good faith or intent.

Agreed.  The email said:

The survey takes only about 5-10 minutes and is completely
anonymous.

and it clearly is not "completely anonymous".  You can say you are not
collecting email addresses, but they certainly are being collected as
part of this process.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  Do not let urgent matters crowd out time for investment in the future.