I whipped up a quick dtrace probe for one of our servers to monitor connection
attempts. My goal was to monitor for any connection attempts from a specific
role within the database. Unfortunatly you can't set logging of connections
for a specific user, and logging all connections on that machine would be
quite the logfile bloater... enter dtrace. With the probe, I can do something
like this:
-bash-3.00$ /opt/csw/bin/sudo dtrace -n 'postgresql*:::connection
{printf(connection attempt: %...@%s\n,copyinstr(arg0),copyinstr(arg1)) }' |
grep robert
dtrace: description 'postgresql*:::connection ' matched 5 probes
2 18984ServerLoop:connection connection attempt: rob...@robert
2 16222ServerLoop:connection connection attempt: rob...@robert
1 16876ServerLoop:connection connection attempt: rob...@pagila
which can be piped to logfile or whatever. I'm attaching a patch against 8.4
as an idea of what I've implemented (actual implementation was against a
custom build) but should be close to working (don't have a working pg repo on
any solaris machines atm). Any feedback appreciated (mostly wondering about
probe name or location). TIA
--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com
diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c
index 3380b80..ddf23d8 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -118,6 +118,7 @@
#include utils/datetime.h
#include utils/memutils.h
#include utils/ps_status.h
+#include pg_trace.h
#ifdef EXEC_BACKEND
#include storage/spin.h
@@ -3142,6 +3143,8 @@ BackendInitialize(Port *port)
elog(FATAL, could not disable timer for authorization timeout);
PG_SETMASK(BlockSig);
+ TRACE_POSTGRESQL_CONNECTION_ATTEMPT(port-user_name, port-database_name);
+
if (Log_connections)
ereport(LOG,
(errmsg(connection authorized: user=%s database=%s,
diff --git a/src/backend/utils/probes.d b/src/backend/utils/probes.d
index f68a7d2..d8b418a 100644
--- a/src/backend/utils/probes.d
+++ b/src/backend/utils/probes.d
@@ -91,4 +91,6 @@ provider postgresql {
probe xlog__switch();
probe wal__buffer__write__dirty__start();
probe wal__buffer__write__dirty__done();
+
+ probe connection__attempt(char *, char *);
};
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
