Обсуждение: Implementing pgaudit extension on Microsoft Windows

Niels Jespersen <NJN@dst.dk> writes:
> Copied pgaudit.dll to C:\Program Files\PostgreSQL\11\lib
> Copied  pgaudit.control and pgaudit--1.3.sql to C:\Program Files\PostgreSQL\11\share\extension
> Set shared_preload_libraries = 'pgaudit'
> Restart PostgreSQL
> Run "CREATE EXTENSION pgaudit;"
> alter system set pgaudit.log = 'all';
> select pg_reload_conf();

> Nothing enters the log-file from pgaudit. Creating tables (relations, sorry). Selecting from tables, nothing in the
log.

Hm, what you describe above looks right.

> I can see that alter system set pgaudit.logx = 'all';  complains about "unrecognized configuration parameter", so
somethingis known about pgaudit. 

No, that means the postmaster *doesn't* have pgaudit loaded.

The fact that CREATE EXTENSION worked implies that you've got the library
correctly built, so I think this must boil down to the
"shared_preload_libraries" setting not having taken.  You could
cross-check that by seeing what "SHOW shared_preload_libraries"
prints.

I wonder if you forgot to remove the comment marker (#) on the
shared_preload_libraries line?

            regards, tom lane

Thanks Tom

alter system set pgaudit.log = 'all'; -- Works, it results in 2019-02-12 08:51:49.109 CET [13560] LOG:  parameter
"pgaudit.log"changed to "all" after select pg_reload_conf(); 
alter system set pgaudit.logx = 'all'; -- Notice spelling error logx: Fails, it results immediately in 2019-02-12
08:53:04.412CET [12856] ERROR:  unrecognized configuration parameter "pgaudit.logx" 2019-02-12 08:53:04.412 CET [12856]
STATEMENT: alter system set pgaudit.logx = 'all'; 

pgaudit is loaded, as show by:

localhost postgres@postgres#show shared_preload_libraries;
 shared_preload_libraries
--------------------------
 pgaudit
(1 row)

localhost postgres@postgres#

pgaudit.dll is locked py postgres.exe, if I try to remove it from the lib folder.

All looks normal, except no logs from auditing.

Regards Niels


-----Oprindelig meddelelse-----
Fra: Tom Lane <tgl@sss.pgh.pa.us>
Sendt: 11. februar 2019 15:44
Til: Niels Jespersen <NJN@dst.dk>
Cc: 'pgsql-general@lists.postgresql.org' <pgsql-general@lists.postgresql.org>
Emne: Re: Implementing pgaudit extension on Microsoft Windows

Niels Jespersen <NJN@dst.dk> writes:
> Copied pgaudit.dll to C:\Program Files\PostgreSQL\11\lib Copied
> pgaudit.control and pgaudit--1.3.sql to C:\Program
> Files\PostgreSQL\11\share\extension
> Set shared_preload_libraries = 'pgaudit'
> Restart PostgreSQL
> Run "CREATE EXTENSION pgaudit;"
> alter system set pgaudit.log = 'all';
> select pg_reload_conf();

> Nothing enters the log-file from pgaudit. Creating tables (relations, sorry). Selecting from tables, nothing in the
log.

Hm, what you describe above looks right.

> I can see that alter system set pgaudit.logx = 'all';  complains about "unrecognized configuration parameter", so
somethingis known about pgaudit. 

No, that means the postmaster *doesn't* have pgaudit loaded.

The fact that CREATE EXTENSION worked implies that you've got the library correctly built, so I think this must boil
downto the "shared_preload_libraries" setting not having taken.  You could cross-check that by seeing what "SHOW
shared_preload_libraries"
prints.

I wonder if you forgot to remove the comment marker (#) on the shared_preload_libraries line?

            regards, tom lane

Niels Jespersen <NJN@dst.dk> writes:
> Same result from
> pgaudit.log = 'all'
> in postgresql.conf and after both select pg_reload_conf(); and after service restart.
> No entries in the log from audit.

Hm.  I don't know much about pgaudit, but just from scanning its
documentation, it doesn't seem like there's anything else that
has to be set.

I wonder whether pgaudit actually works on Windows?  It might have
some weird dependency on children being spawned with fork not exec,
for instance.  You probably ought to contact the authors and ask.

            regards, tom lane

Hi Tom (and others)

Thank you for input. I think my next steps will be approcching the problem from differenct angles:

Implement on Linux as Proof of Concept.
Dive into the source for pgaudit and try to understand how it works, perhaps getting a small example extension working
alongthe way.  
Perhaps contacting the authors of pgaudit to have them confirm that there  exists working implementations of pgaudit on
Windows(and if so if they are on PostgreSQL 11).  

I will report how this turns out.

Regards Niels

-----Oprindelig meddelelse-----
Fra: Tom Lane <tgl@sss.pgh.pa.us>
Sendt: 12. februar 2019 15:14
Til: Niels Jespersen <NJN@dst.dk>
Cc: 'Pavel Stehule' <pavel.stehule@gmail.com>; pgsql-general@lists.postgresql.org
Emne: Re: SV: Implementing pgaudit extension on Microsoft Windows

Niels Jespersen <NJN@dst.dk> writes:
> Same result from
> pgaudit.log = 'all'
> in postgresql.conf and after both select pg_reload_conf(); and after service restart.
> No entries in the log from audit.

Hm.  I don't know much about pgaudit, but just from scanning its documentation, it doesn't seem like there's anything
elsethat has to be set. 

I wonder whether pgaudit actually works on Windows?  It might have some weird dependency on children being spawned with
forknot exec, for instance.  You probably ought to contact the authors and ask. 

            regards, tom lane

On 2/13/19 3:32 AM, Niels Jespersen wrote:
> Dive into the source for pgaudit and try to understand how it works,
> perhaps getting a small example extension working along the way. 
> Perhaps contacting the authors of pgaudit to have them confirm that
> there  exists working implementations of pgaudit on Windows (and if
> so if they are on PostgreSQL 11).

pgaudit definitely *should* work on Windows. If you can post your other
log related conf settings I will fire up a Windows VM and try to
duplicate the issue.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On 2/13/19 9:17 AM, Joe Conway wrote:
> On 2/13/19 3:32 AM, Niels Jespersen wrote:
>> Dive into the source for pgaudit and try to understand how it works,
>> perhaps getting a small example extension working along the way.
>> Perhaps contacting the authors of pgaudit to have them confirm that
>> there  exists working implementations of pgaudit on Windows (and if
>> so if they are on PostgreSQL 11).
>
> pgaudit definitely *should* work on Windows. If you can post your other
> log related conf settings I will fire up a Windows VM and try to
> duplicate the issue.

I just compiled pgaudit on a Win10 VM and loaded it into a Postgres 11
Win64 build downloaded from the PGDG community download. Works perfectly
for me. Will definitely need to understand more details about your
installation. Probably not appropriate to do that on this list, so
please submit an issue on github.com/pgaudit/pgaudit

Thanks,

Joe


--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Hi Joe

That is great. 

Log related settings are here:

log_destination = 'stderr'
logging_collector = on
log_file_mode = 0640
log_timezone = 'Europe/Brussels'
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all'

show shared_preload_libraries; 

and 

show pgaudit.log; 

confirms. 

Pgaudit is 1.3.0, compiled with Vsiual Studio Professional 2017. PostgreSQL is PostgreSQL 11.0, compiled by Visual C++
build1914, 64-bit (not by me). 
 

Regards Niels

PS: Enjoyed your PostgreSQL security talk in Brussels recently. 

-----Oprindelig meddelelse-----
Fra: Joe Conway <mail@joeconway.com> 
Sendt: 13. februar 2019 15:18
Til: Niels Jespersen <NJN@dst.dk>; 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'Pavel Stehule' <pavel.stehule@gmail.com>; pgsql-general@lists.postgresql.org
Emne: Re: SV: SV: Implementing pgaudit extension on Microsoft Windows

On 2/13/19 3:32 AM, Niels Jespersen wrote:
> Dive into the source for pgaudit and try to understand how it works, 
> perhaps getting a small example extension working along the way.
> Perhaps contacting the authors of pgaudit to have them confirm that 
> there  exists working implementations of pgaudit on Windows (and if so 
> if they are on PostgreSQL 11).

pgaudit definitely *should* work on Windows. If you can post your other log related conf settings I will fire up a
WindowsVM and try to duplicate the issue.
 

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development

Will do. 

-----Oprindelig meddelelse-----
Fra: Joe Conway <mail@joeconway.com> 
Sendt: 14. februar 2019 00:34
Til: Niels Jespersen <NJN@dst.dk>; 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'Pavel Stehule' <pavel.stehule@gmail.com>; pgsql-general@lists.postgresql.org
Emne: Re: SV: SV: Implementing pgaudit extension on Microsoft Windows

On 2/13/19 9:17 AM, Joe Conway wrote:
> On 2/13/19 3:32 AM, Niels Jespersen wrote:
>> Dive into the source for pgaudit and try to understand how it works, 
>> perhaps getting a small example extension working along the way.
>> Perhaps contacting the authors of pgaudit to have them confirm that 
>> there  exists working implementations of pgaudit on Windows (and if 
>> so if they are on PostgreSQL 11).
> 
> pgaudit definitely *should* work on Windows. If you can post your 
> other log related conf settings I will fire up a Windows VM and try to 
> duplicate the issue.

I just compiled pgaudit on a Win10 VM and loaded it into a Postgres 11
Win64 build downloaded from the PGDG community download. Works perfectly for me. Will definitely need to understand
moredetails about your installation. Probably not appropriate to do that on this list, so please submit an issue on
github.com/pgaudit/pgaudit

Thanks,

Joe


--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development