Implementing pgaudit extension on Microsoft Windows

po 11. 2. 2019 v 14:23 odesílatel Niels Jespersen <NJN@dst.dk> napsal:

Hello all

Recent Oracle convert here.

Running PostgreSQL 11 on Windows (10 and 2016).

Please advise, if I should direct this to another mailinglist or forum.

I want to implement detailed object auditing on PostgreSQL. This is what
PGAudit extension does. However most advice on this is directed at Linux
and friends, which is fair enough. From Googleing around, it seems it
should be possible to compile on Windows, though.

And it is. I have compiled pgaudit.dll from the source out of pgaudit
version 1.3.

And this:

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.

I can see that alter system set pgaudit.logx = 'all'; complains about
“unrecognized configuration parameter”, so something is known about
pgaudit.

I can see that postgres.exe has locked pgaudit.dll, so postgreSQL knows it
is there, but nothing in the log-file.

I need some advice regarding the next steps in my troubleshooting.

probably you should to use

alter system set "pgaudit.log" = 'all';

Regards

Pavel

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 something is 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

po 11. 2. 2019 v 15:37 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

po 11. 2. 2019 v 14:23 odesílatel Niels Jespersen <NJN@dst.dk> napsal:

Hello all

Recent Oracle convert here.

Running PostgreSQL 11 on Windows (10 and 2016).

Please advise, if I should direct this to another mailinglist or forum.

I want to implement detailed object auditing on PostgreSQL. This is what
PGAudit extension does. However most advice on this is directed at Linux
and friends, which is fair enough. From Googleing around, it seems it
should be possible to compile on Windows, though.

And it is. I have compiled pgaudit.dll from the source out of pgaudit
version 1.3.

And this:

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.

I can see that alter system set pgaudit.logx = 'all'; complains about
“unrecognized configuration parameter”, so something is known about
pgaudit.

I can see that postgres.exe has locked pgaudit.dll, so postgreSQL knows
it is there, but nothing in the log-file.

I need some advice regarding the next steps in my troubleshooting.

probably you should to use

alter system set "pgaudit.log" = 'all';

but alter system set pgaudit.log = 'all'; should to work if pgaudit is
loaded.

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.412 CET [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 something is 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

út 12. 2. 2019 v 8:57 odesílatel Niels Jespersen <NJN@dst.dk> napsal:

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.412 CET [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.

and it doesn't work too if you use postgresql.conf?

Pavel

út 12. 2. 2019 v 8:59 odesílatel Pavel Stehule <pavel.stehule@gmail.com>
napsal:

út 12. 2. 2019 v 8:57 odesílatel Niels Jespersen <NJN@dst.dk> napsal:

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.412 CET [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.

and it doesn't work too if you use postgresql.conf?

don't forget reload

select pg_reload_conf();

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.

Regards Niels

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

út 12. 2. 2019 v 8:59 odesílatel Pavel Stehule <pavel.stehule@gmail.com<mailto:pavel.stehule@gmail.com>> napsal:

út 12. 2. 2019 v 8:57 odesílatel Niels Jespersen <NJN@dst.dk<mailto:NJN@dst.dk>> napsal:
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.412 CET [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.

and it doesn't work too if you use postgresql.conf?

don't forget reload

select pg_reload_conf();

Pavel

Regards Niels

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

Niels Jespersen <NJN@dst.dk<mailto: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 something is 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

út 12. 2. 2019 v 10:34 odesílatel Niels Jespersen <NJN@dst.dk> napsal:

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.

It is strange - can you try same setup on linux?

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 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).

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 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

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++ build 1914, 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 Windows VM 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 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

Joe, and others

I did not submit an issue. Instead, I read your input on another existing issue. In that, you recommended to place pgaudit source in contrib in a PostgreSQL source tree and build the whole thing.

I did just that this morning using 11.2 (my first compile of PostgreSQL (which succeeeded)). The resulting pgaudit.dll works fine. So far at least, I haven't tried all features, but pgaudit.log = 'all' generates lots of log.

I also pulled the commands to build pgaudit from the build log and can now compile pgaudit to a working dll against only a binary installation.

All is good.

Perhaps my experiences could be added to the project. I am willing to write a short markdown document describing this, if there is interest.

Regards Niels

-----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 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

On 2/20/19 5:58 AM, Niels Jespersen wrote:

I did not submit an issue. Instead, I read your input on another
existing issue. In that, you recommended to place pgaudit source in
contrib in a PostgreSQL source tree and build the whole thing.

I was hoping you would see that -- it looked pretty much the same as
your issue.

I did just that this morning using 11.2 (my first compile of
PostgreSQL (which succeeeded)). The resulting pgaudit.dll works fine.
So far at least, I haven't tried all features, but pgaudit.log =
'all' generates lots of log.

Awesome -- thanks for the follow up!

I also pulled the commands to build pgaudit from the build log and
can now compile pgaudit to a working dll against only a binary
installation.

Oh, I didn't even know that was possible. Can you describe in a little
more detail how that is done? I only know the minimum possible to get by
with the Windows build system...

Perhaps my experiences could be added to the project. I am willing to
write a short markdown document describing this, if there is
interest.

That would be awesome, although I bet it applies to more than just
pgaudit. I have had people report similar issues over the years with
plr.dll, for example.

Joe

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