Hooks in PostgreSQL
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 20 | |
| Author | ||
| Contributors | ||
| License | CC Attribution - NonCommercial - ShareAlike 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal and non-commercial purpose as long as the work is attributed to the author in the manner specified by the author or licensor and the work or content is shared also in adapted form only under the conditions of this | |
| Identifiers | 10.5446/19022 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language | ||
| Producer |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
|
PGCon 20123 / 20
1
3
6
7
9
11
12
14
15
16
19
20
00:00
VideoconferencingDivision (mathematics)Metropolitan area networkCompilerEmailInformationOperator (mathematics)Data Encryption StandardPort scannerField extensionRight angleTranslation (relic)Functional (mathematics)Field extensionSoftware developerOperator (mathematics)Theory of relativityHookingProjective planeWave packetType theoryFormal languagePhysical systemForcing (mathematics)Grass (card game)Line (geometry)WordXMLUMLComputer animation
02:05
Interior (topology)Interrupt <Informatik>Chi-squared distributionExecutive information systemMetropolitan area networkAutomorphismPrice indexPointer (computer programming)Function (mathematics)Set (mathematics)System callHookingLibrary (computing)Functional (mathematics)Process (computing)Pointer (computer programming)Information securityProfil (magazine)PasswordField extensionBuffer solutionStatement (computer science)Object (grammar)Extension (kinesiology)Query languagePhysical systemRow (database)AuthenticationInformationConnected spaceCore dumpSheaf (mathematics)BitClient (computing)Module (mathematics)Shared memoryPlanningDirectory serviceRule of inferencePlug-in (computing)Structural loadUtility softwareDatabaseFormal languageSoftware developerCondition numberDebuggerCASE <Informatik>FunktionspunktmethodeCategory of beingInternet service providerEnterprise architectureType theoryArithmetic progressionFamilyServer (computing)Scaling (geometry)Sound effectSemiconductor memorySystem callPlotterPhase transitionXML
10:30
Function (mathematics)Pointer (computer programming)System callMetropolitan area networkValue-added networkInsertion lossInclusion mapArmArithmetic logic unitBeta functionEvent horizonComputer iconSample (statistics)AutomorphismModul <Datentyp>Parameter (computer programming)CodeGroup actionParameter (computer programming)Library (computing)Group actionFigurate numberINTEGRALFunctional (mathematics)Data structureProjective planeSound effectWorkstation <Musikinstrument>Traffic reportingMultiplication signClient (computing)MereologyConnected spaceRow (database)PredictabilityAuthenticationCodeConfiguration spaceConjugacy classPasswordHookingGeodesicBitSet (mathematics)Maxima and minimaNumberEvent horizonGame controllerError messageRemote procedure callDivisorDirectory serviceField extensionComputer fileMessage passingContext awarenessSystem identificationFunktionspunktmethodeSubject indexingProduct (business)DatabaseInstance (computer science)Limit (category theory)Extension (kinesiology)Pointer (computer programming)Arithmetic progressionIntegerSystem callPoint (geometry)Dependent and independent variablesRight anglePrice indexReverse engineeringFile systemIP addressChainEmailDeclarative programmingSource codeModule (mathematics)Level (video gaming)LoginType theoryFerry CorstenLatent heatComputer animation
18:56
CountingSystem callMetropolitan area networkStatisticsStatement (computer science)Queue (abstract data type)LogarithmProcess capability indexFunction (mathematics)Parameter (computer programming)AutomorphismValue-added networkAttribute grammarExt functorInclusion mapPasswordMathematicsSemiconductor memoryField extensionMultiplication signQuery languagePlanningModule (mathematics)Functional (mathematics)Ferry CorstenPasswordBitInheritance (object-oriented programming)HookingSource codeView (database)Extension (kinesiology)Row (database)InformationSystem callStatement (computer science)Type theoryGame controllerConfiguration spaceStructural loadVirtual machineLibrary (computing)EncryptionMathematicsStatisticsEnterprise architectureRule of inferenceHistogramData dictionaryPointer (computer programming)Computer fileBuffer solutionCountingParameter (computer programming)TupleString (computer science)Subject indexingData structureCommunications protocolShared memoryFormal grammarIntegerElectronic visual displayInsertion lossPRINCE2OntologyBridging (networking)Network topologyPurchasingText editorMereologyReduction of orderDirection (geometry)TheoryOffice suiteState of matterComputer animation
27:21
PasswordFunction (mathematics)Data typeMountain passParameter (computer programming)Metropolitan area networkIntegrated development environmentHigher-order logicValue-added networkOvalBlock (periodic table)LogarithmBlu-ray DiscChi-squared distributionArmUser profileModul <Datentyp>State of matterMaxima and minimaInclusion mapBoltzmann equationVarianceArc (geometry)Structural loadParameter (computer programming)Error messageHookingSource codeComputer fileGastropod shellPasswordData loggerArithmetic meanMultiplication signLibrary (computing)Functional (mathematics)DebuggerFile systemInstallation artInformationLine (geometry)Enterprise architectureData storage deviceGame controllerState of matterWordPlug-in (computing)Data structureModule (mathematics)Statement (computer science)Row (database)TimestampValidity (statistics)Profil (magazine)WritingEqualiser (mathematics)Right angleBit rateClassical physicsGroup actionObject (grammar)Directory serviceMereologyPlotterScripting languageDivisorType theoryAsynchronous Transfer ModeGrass (card game)Semiconductor memoryPressureHTTP cookieSparse matrixLie groupExtension (kinesiology)HoaxConvex functionConservation lawCompilerComputer animation
35:46
LogarithmPasswordMetropolitan area networkOvalMaxima and minimaStructural loadStatement (computer science)Length of stayFunction (mathematics)Newton's law of universal gravitationTotal S.A.Drop (liquid)Message passingServer (computing)Information systemsMathematical analysisParsingDensity of statesStatisticsPhysical systemSample (statistics)Latent heatEvent horizonDependent and independent variablesElectric generatorSlide ruleFunctional (mathematics)Gastropod shellHookingCodeStatement (computer science)Drop (liquid)Point (geometry)Library (computing)Message passingCASE <Informatik>Context awarenessQuery languagePasswordDebuggerExtension (kinesiology)Real numberNormal (geometry)Pointer (computer programming)Multiplication signDatabaseObject (grammar)LoginServer (computing)Computer fileStructural loadInformationFerry CorstenClient (computing)Inheritance (object-oriented programming)EncryptionFilter <Stochastik>Physical systemMathematical analysisArithmetic meanSampling (statistics)State of matterWeb pageParsingGroup actionGrass (card game)Procedural programmingAreaConnected spaceKey (cryptography)Scripting languageAbsolute valuePattern languageFrame problemMereologyHoaxLevel (video gaming)OntologyDifferent (Kate Ryan album)XML
44:12
Physical systemOvalSample (statistics)PasswordLength of stayLogarithmMaxima and minimaInclusion mapStatement (computer science)Arithmetic meanWrapper (data mining)Functional (mathematics)Local ringEnterprise architectureMultiplication signPasswordHookingBitPersonal digital assistantProfil (magazine)MathematicsSphereDatabaseLibrary (computing)WindowPointer (computer programming)DebuggerPrice indexSlide ruleComputer fileExtension (kinesiology)Frame problemHacker (term)Installation artComputer animation
48:59
Physical systemOvalSample (statistics)RootOffice suiteSlide ruleOpen setComputer animation
Transcript: English(auto-generated)
00:05
I'm just waiting for the guys to...
00:20
Okay. Hello everybody. This talk will present you a quite unknown feature of Postgres, and this feature is its hook system. But first, a few words about me. My name is Guillaume Lallard.
00:42
I'm one of the translators of the Postgres manual. We translate the Postgres manual in French since the 7.4 release. I'm one of the developers of PgAdmin, and I do a lot of other stuff on Postgres
01:01
and related tools on Postgres. For work, I work at Delibaud, which is a PostgreSQL company. We do a lot of stuff on Postgres, mostly trainings, support, and auditing.
01:22
Well, enough about me. Postgres is really well known for its extensibility. Many people know that you can add your own user types, you can add functions that handle user types, you can add operators which use those functions,
01:44
and you can do lots of other stuff. You can have many languages, for example, to code your functions. Actually, the extensibility of Postgres is so important to the project
02:01
that one of the most interesting features of 9.1 is its extensions object. The extension object helps you to add new features, new user types, new functions to your Postgres database. So with all this going on on the extensibility,
02:23
it's rather strange, rather weird that the hook system in itself is really less known. What's a hook? A hook, the aim of hooks is to interrupt and modify the usual behavior of Postgres.
02:45
This allows the developer to add new features without having to add it to the core of Postgres. It's not a well-known feature because it's actually quite recent.
03:01
The first hook appeared in 8.3. In 8.3, you have five hooks available. In 8.4, you got eight more hooks. In 9.0, you got two more hooks. And in 9.1, five more. So right now, you have around 20 hooks available
03:23
to modify the behavior of Postgres. But actually, the biggest issue is that it's not at all explained in the documentation. You can search through all the documentation. You have nothing about the hooks
03:41
and how to cut functions that use the hooks available in Postgres. You have four kind of hooks. Yeah, four kind of hooks. Hooks on the planner, hooks on the executor,
04:02
hooks specific to security and permissions, and hooks for the PLPGSQL language. So right now, we will see all these hooks. First, on the planner hooks, so you have eight hooks available for the planner.
04:25
It's mostly used by some plugins, such as the Plan Tuner and the Planian Steer, which allows you to have a way to change the behavior of the planner.
04:47
On the executor hooks, I believe that everyone in this room already used, at least known about a plugin called Pgstat statements. Pgstat statements is a country module
05:01
that is able to get all the queries executed by Postgres and get a few statistics on it. And how does Pgstat statements works? It use all these hooks. So for example, the executor start, run, finish,
05:21
and to get information about which queries are executed, how many rows they read, how many buffers they used, et cetera. The process utility hook is also used by the Pgstat statements country module.
05:42
It's also used by an extension written by Dimitri Fontaine, which is called Pgextwlist, which is an extension that allows someone to have a white list of extensions that you can instill.
06:03
You've got also hooks on security and permissions. So for example, you have the check password hook. It's a simple hook that allows you to check passwords according to your enterprise rules. So it's a hook that is called when a user is created
06:21
or when a user, definition of a user is changed. The client authentication hook is a hook which is called when someone tries to connect. So it's used by many modules such as the odd delay, which is another country module,
06:42
which allows you to wait a bit if the last authentication failed. SRPG SQL also use it to add more checks to a lower denial connection. And you have another bunch of hooks
07:02
usually used by the SCPG SQL plugin. PLPG SQL, you have five hooks available for PLPG SQL. You have the hook that is called when you do the declare section of a function,
07:23
of a PLPG SQL function. You have the frank underscore beg hook, which is called when you start executing the begin of a function, frank end for the end of the function, stmt underscore beg for the beginning of a statement, and stmt underscore end for the end of a statement.
07:44
It's used by three modules, as far as I know, which are the debugger, the profiler provided by Enterprise DB, and by another tool that I wrote called log underscore functions.
08:03
And you get another one. I said there was only four kind of hooks. Actually, there is another one kind of hooks which I couldn't put it in a specific category, which is the shmem startup hook. It's a hook that is called when Postgres
08:22
needs to ask for shared memory to the system. It's used by the pgstatstatements module to record every queries executed in shared memory. I didn't speak about the initial release in all those hooks, but as you can see, it starts with a.3,
08:45
and we've got more and more of them to use in i.1. So how do they work inside Postgres? Hooks consist of global function pointers.
09:01
It's initially set to null, and when PostgresQL may have to execute it, it checks if the global function pointer is still set to null, and if it isn't, it executes the function pointer. So all you have to do to execute a function
09:21
is to set this global function pointer and provide a function that will be executed. How do we set the function pointer? Actually, a hook function will be available through a shared library. A shared library is a .so or .dll file
09:44
that is instilled in the lib directory of Postgres. When Postgres has to load a shared library, it first loads it into memory, and then it executes a function inside this shared library called __pg__init.
10:01
And this functions needs to set the pointer. It may save the previous one to be able to call it if he wants to do it, but the main function, the main work the __pg__init function needs to do
10:24
is to set the global function pointer. How do we unset the function pointer? We unset it at unload time. At unload time, Postgres calls another function of the shared library called __pg__init,
10:44
and this function will unset its pointer. Usually, it simply restore the previous one so that the previous one can still be called. So here is an example of how it is
11:00
put in Postgres SQL source code. So this is an extract of the __h file in Postgres. You have a type declaration which shows you how you should write this function. You need to write a function that will have two arguments, two parameters,
11:25
a structure, a port, and an integer. So this is how it is declared in Postgres, and then in the Postgres SQL source file,
11:41
there is a declaration of a variable called client authentication underscore hook, which is set to null right now. When you install the function with the __h__init, do any of our hooks chain call? So for example, if somebody had already defined them,
12:02
do we run our code and then can we call them, or is it, we don't need that. But Postgres doesn't do it itself. It's the responsibility of the extension of the shared library. When it calls its own function, it should check if it has a previous hook,
12:21
and then calls it. Well, well-written hook will do that. So this is the variable we will need to change when the __pg__init function is called.
12:45
When Postgres will have done the authentication on its part, it will check if the hook is set to something other than null and calls it.
13:02
So now we will write a few hooks. So we will go into much better details on how to write hooks, and we will do so not on every hooks available. We will just grab this one, the client authentication,
13:21
the executor hook, the check password, and the __pg__hook. So we will explain how useful they are. We will list already available extensions using them, and we will see how to write a shared library that uses this hook. So first we start with the client authentication hook.
13:44
The client authentication hook gets control just after client authentication, but it gets it before the user is informed, which means that it's really useful to do yourself an authentication on something else,
14:06
to record login events, or to insert a delay after a failed authentication. So we already have a few modules that use these hooks. The odd delay, odd delay is a contrived module
14:22
available with PostgreSQL source code. It has a configurable delay that allows you to, that helps you to avoid those attacks. So you have your authentication. After the authentication, a failed authentication,
14:42
a delay is inserted so that you cannot, right away, try to connect again. SOPG SQL requires some specific SFINEX context to allow connection. So these two are available in the PostgreSQL source file in the contrived subdirectory of its source files.
15:04
And then you have another one, which is connection underscore limits written by Thomas Von Tra. It's available on GitHub. Connection limits give you more control on the number of connections available than what you have with PostgreSQL. With PostgreSQL, you only have a GUK,
15:22
MAX, and OSCAR connections. That allows you to limit the number of connections per instance, per cluster. But with connection limits, you can have a limit and connections per user, per database, or per IP address.
15:43
The client authentication hook function, as we said previously, has two parameters. The first one, POT, is a complete structure described in a header file of Postgres where you can get the remote host, the remote host name, the remote port,
16:01
the database name, the user name, GUK options, and a few other things. The status integer is a status code, which basically say if the connection is accepted or denied.
16:21
So this example will show you how you can add, write a client authentication hook. What we will try to do is to forbid, to deny a connection if a file is present on your file system. So we will need two functions. We will need one function to install the hook.
16:40
It's the OSCAR-PG, OSCAR-UNIT function that we will set. And in this function, what we will do is set the client authentication hook global function pointer. The second one is there to check the availability, the presence of the file,
17:01
and if the file is present or deny the connection. So first, we need to initialize the hook. The initialization of the hook happens in the OSCAR-PG, OSCAR-UNIT function. So we first have the record of the previous
17:23
client authentication hook. So we save it here. And then, on the client authentication hook, we set it with our own functions, which is available here. So this function is quite simple. There are the two parameters that we talked about before.
17:43
If there was a previous hook, we first call it. We call it before because if this hook denies the connections, we don't want to do our checks. So we first execute the previous hook.
18:04
If the previous hook allows the connection, we will check if the file we want is available or not. So here, we simply check if we have the slash tmp slash connection.stop file on the file system.
18:21
If it is here, we do a year report with a fatal log level. And we simply throw this error message in the log. So if the file is present, all you have is a fatal error which denies the connection.
18:44
And if it isn't, you simply exit the functions and the connection is allowed. We will see a bit later the use of this hook.
19:02
On the executor hooks, there are four hooks for the executor. The executor start, run, finish, and end. Executor start hook is executed at the beginning of the execution of a query plan. The executor run hook may be called more than once.
19:22
It accepts directions and counts. So it's called with, it depends on how many tuples you will have when you execute a query. The executor finish hook is called after the last executor run call.
19:40
And the executor end is executed at the end of the query plan. There are a lot of contrib modules that use the executor hooks. That's probably the hooks that are the most used. So these kind of hooks are really interesting
20:02
to get informations on executed queries. And the module you probably all have already, already know is gstat-statements. gstat-statements is a contrib module that will get a bit of shared memory. And each time a query is executed,
20:21
it gets the executed query, a string of the executed queries. It gets a few informations on the executed query, its duration, how many buffers it used, that kind of things, and put them into memory. And then you have a view that allows you to get this information from this module.
20:44
Auto-explain is another module, quite interesting, also available in the PrestoQL source code. This module will log automatically the explain plan of each query executed.
21:04
Pglogs user queries is how I find about hooks. I was working at a customer office, and this customer wanted to be able to log every query executed by super users.
21:23
And it only wanted these kind of queries, because he wanted to be able to do auditing of the super users. So Pglog user queries is a specific module that you can get on GitHub,
21:40
which only log queries according to some specific configuration. Query histogram and query recorder are two other extensions written by Thomas Van Dra. Query histogram is used to build a duration,
22:03
histogram of all queries executed. And query recorder allows you to log queries executed in different log files. And the executor hand hook function, which will be the one we will use for the example,
22:22
we have only one parameter, which is a structure, query desk, which allows you to get information on the command type. Is it an insert, is it an update, is it a create extension, et cetera. On the query, on the query string,
22:45
on the instrumentation structure, which gives you lots of information on statistics gathered during the execution of the query, et cetera.
23:03
So if we want to write an executor hand hook, this example will allow you to log queries executed only by super user, super user users. So we need, we will need three functions at this time. We will need one to install the hook,
23:22
or we will set the global function pointer. We need one to uninstall the hook, and the last one to do the log. So first, install the hook. Exactly the same thing as before. We save the previous executor hand hook,
23:44
and then we set the executor hand hook with our own function. Our own function will simply check if the user has the super user attributes, log another query, depending on this information,
24:03
and fire the next hook of the previous one. So this is our function. There is the parameter that we won't use this time, but it's available. We check if the user is a super user,
24:22
and if it is a super user, we will simply call elog to log the query executed. Actually, we don't log only the query. We log all this, super user, the name of the super user, fire this query, and the query.
24:41
And then we call the previous, the previous hook. Super user, get user name from ID, get user ID, come straight from Postgres. I didn't write it, it was already available.
25:02
To uninstall the hook, the only thing we have to do is to set the global function pointer to its previous value. Quite easy. Sorry? Okay.
25:22
The anoscopy-genoscopy function are called either by when you exit your session or when Postgres is stopped. You have no way to, you have no way to unload machine libraries.
25:54
Then we have the check password hook details. Check password is a hook which enables an extension
26:02
to get control when a user is created or when a user is modified. So when you use the create user statement or the alter user statement. But it gets control before committing the statement.
26:26
So it's really useful if you want to check the password according to some of your enterprise rules. For example, you need to have password with at least eight characters, no less. Or you need to check the password against a dictionary.
26:42
That's the kind of use of this hook. Other possibilities, log change of passwords. Or simply disallow plain text passwords. You have two type of password you can add on the create or alter user statements.
27:00
Encrypted and an encrypted password. But there is one major issue with this hook. It's really, really less effective if you use an encrypted password. If you use an encrypted password, you always have the same size of the password. So you cannot check the size of the real password.
27:23
You cannot check it against a dictionary. It takes more time to check that kind of things. So this hook is used by a country module available in the PostgreSQL source code,
27:40
which is password check. Password check is a country module that will do a few checks to make sure that the password is not too weak. Just a few words on password check. Be sure to read the source code of password check
28:00
before using it so that you can change it and modify it to really stick to your enterprise rules. You can also use cracklib with this module. You just have a few lines to uncomment to be able to use it.
28:22
The check password hook functions take five parameters. The username, the password, the password type, which simply says if it is a plain text password or an MD5 password, an encrypted password, and some information on the validity timestamp of the user.
28:43
So we will write a check password hook function, and this hook will deny the use of plain text passwords. So, once again, we need two functions, mainly. One to install our hook, and one to check
29:03
if the user tries to use a plain text password or an encrypted password. To install the hook, really, exactly the same thing as before. In this example, I don't record the previous hook, which is bad, but we already see some example doing it.
29:26
And the hook itself. I have my function with these five parameters, and all I do is check if the password is a plain text password. If it is a plain text function,
29:41
I call the e-report function to report an error to the user saying that it's not allowed to use non-encrypted passwords. We will see later how to use this hook
30:04
in the example at the end of the talk. Funkel Oskar Begg. Funkel Oskar Begg is a hook called when Postgres gets to the begin statement
30:21
of a PLPG SQL functions. So it can be used in many ways. It can be used to start to log the start of each function. It can be used to profile functions. It can be used to debug functions. So the three hooks I know that use it
30:41
is the PL debugger, which is a debugger for PLPG SQL that you can use with PgAdmin. It's written by Enterprise DB. You have the PL profiler, which gives you all the details on every statement executed on every PLPG SQL functions.
31:02
And you have another one, which is log on Oskar functions, which do quite the same thing that the PL profiler, but instead of storing it into shared memory, it put them in the log file. It's actually quite more efficient, quite more,
31:22
really quicker than PL profiler. So the funkel Oskar Begg functions takes two parameters. One is a structure that gives you information on the execution state of the function. And the second one gives information on the function being executed,
31:41
meaning you have information on which function you execute, its name, its ID, and a few other things. So we'll just simply write funkel Oskar Begg hook function, which logs each function executed. So we need first to install the hook.
32:02
So the first function to install the hook, another function to log the function name. Initialize the hook, but we've already seen that. We simply set the hooks, a funkel Oskar Begg hook,
32:20
and the function which is executed. This function we simply call elog on the log level, and store execute function, the name of the function in your log file.
32:43
How do we compile hooks? Well, this is the usual make file. You have the possibility to use PG Oskar, PG-XS to compile it, which is certainly the preferred way to compile hooks,
33:05
to compile a shell library, or you can put it in the contrib subdirectory of your PostgreSQL source code to compile it. So to compile it, you just need to use make. If you want to use make with PG-XS,
33:24
you will use this variable, this parameter. Be careful that you cannot use PG-XS with PLPG SQL plugins. It will be possible in 9.2,
33:41
thanks to some work from Heike, but it isn't possible in the previous race. If you want to compile the PL debugger or the PL profiler, you have to copy the source code in the contrib subdirectory
34:00
of the PostgreSQL source code file. To install, you just need to add the install keywords after make, and what it will do is simply copy the .so file in the lib subdirectory of Postgres.
34:24
If you can use PG-XS, it's better. It's better because it's really simpler to do, and you can have a much simpler make file to do it. It's not always possible for PLPG SQL hooks.
34:41
You cannot use that yet. So you have two ways to use hooks. First one is to declare them in the shell preload library gerks. So you first install on the file system in the lib subdirectory of Postgres, your shell library, so your .so or .dll file,
35:03
and then in the PostgreSQL.conf file, you will set the shell preload library's gerk. Some functions allows you to have other gerk. PGstat statements, for example,
35:20
give you some control of its execution, but mostly the most important thing to do is to set the shell preload libraries. You can have many libraries set. You just need to separate them with commas.
35:42
So you can have, for example, shell preload libraries equal PGstat statements, comma, pglog user queries, and then you have to restart Postgres. So for example, if we try to use the hook we've seen with the check password hook,
36:04
which I called only uncreated passwords, I install the hook, the .so file in my system, then in the PostgreSQL.conf file, I set the shell libraries, the shell preload libraries gerk to only encrypted passwords,
36:21
and I restart Postgres. When I restart Postgres, I will have this message, which tells me that he found the shell library, and he loaded it. So at this time, the global function pointer of this shell library is set.
36:43
How do we use it? We don't have to call it, really. It's during the use of Postgres. For example, if I have a user that execute this statement, create user U1 password super secret, the hook will deny the execution of this statement.
37:04
It will say that the password is not encrypted, and so I cannot create this user. If I use an encrypted password, just like this statement, it will work. My hook allows the use of encrypted password.
37:24
It works for create user, it works also for outer user. If I try an outer user with an unencrypted password, it's denied, and if I try to use it with an unencrypted password, it works.
37:41
So this is an example of using hooks with the shell preload libraries gerk. But you don't need, absolutely, to set this, you don't need to set this gerk. If you set this gerk at start time,
38:04
Postgres will set the global function pointer. But sometimes you don't want to use the hook every time. For example, on the PL debugger or the, or on some other case, you don't want to pay the price
38:21
of having the hook already in statement. You just want to use it at some point. So you still have to install the shell libraries in your system, and then you can use a statement called load, which allows you to load the shell library at a specific time.
38:45
So we'll try with the code we see previously about the log of PLPG SQL functions. So I create a PLPG SQL function, which does not match.
39:05
I set my client messages gerk to the log lever, and then I execute the functions. As my hook is not yet installed, I don't have any more information.
39:21
If I load my hook, just like that, and I execute my function, then I see the message coming from my hook functions. I don't have the message here. I have it when I do the load.
39:40
And if I execute my functions more than once, just like this, select f1 from generate series, then I have the log multiple times. So this helps me to know which functions I executed, and I can set it only when I want to use it.
40:02
I have a load statement. I don't have an unload statement. You don't unload a shell library. When you exit, in my case here, I was in PSQL. When I exit PSQL, when I close a connection, the hook is unload.
40:28
So we saw that we have around 20 hooks from an 8.3 to 9.1. In 9.2, we have two more hooks.
40:41
We have a logging hook and another planner hook. We also have really interesting support for PLPG SQL hooks with PGXS. And we have an old hook which has enhanced capability.
41:03
It's the object access hook. The object access hook, you couldn't use it with a drop statement. There was no drop statement support with the object access hook. It's used by SOPG SQL to allow you to give a solenix context
41:20
to allow or deny to drop an object. The logging hook. The logging hook was written by Martin Pilak. The real name of the hook is emit on a skull log on a skull hook. Its aim is to intercept messages
41:40
before they are sent to the server log, whatever the server log is. If it is event log, if it is syslog, if it is a postgreSQL logging collector, doesn't matter. It will intercept the message and it will allow hooks to do custom log filtering.
42:01
For example, we'll be able to do one log file per database. So you can have an extension that will be able to write your log messages in different log files. It's already used by an extension called PG on the scroll now
42:22
which is available on pgxn. The other hook is the planner hook written by Peter Jogan. Its real name is post-post-analyze hook. It's what will allow query normalization within pgstat statements in 9.2.
42:48
And I think I'm about, it's about everything I have. So hooks are a really interesting feature of Postgres. It allows you to go behind to do more things
43:01
than just what Postgres is capable to do. You can change its behavior in specific ways. You need to be cautious about which hooks you install. As we've seen before, it's the hook responsibility
43:21
to save the previous hook registered and to fire them when the hook is fired. So you need to be cautious about which hook you install. You need to be cautious to not install many hooks because, of course, the more you had hooks,
43:41
the more you have problems with performance. So only install the hooks that you really need. All the examples and all the slides are available on GitHub or the examples we've seen. You have a normally working code in it
44:05
that you can try and use. And that's about it. Do you have any questions? Yes.
44:25
I don't and I wanted to work on this. This is the second time I do this talk. I did it at FOSDEM. There was David already there. Already asked the same thing. I wanted to work on this for 9.2 but I didn't find the time to do it.
44:42
I don't really know why. I suspect it's because it really changes the behavior of Postgres and it may be pretty bad if you do it, if you do bad things with it. Maybe it's as good as that.
45:05
Yeah. You have documentation on how to write a frame that have wrappers. And you don't have that on how to write hooks.
45:20
So yes, it's something we need to do. Actually, there was a discussion on this on BGSQL hackers at some time when Peter wanted to add a new hook. But he didn't get any documentation on it. Yes.
45:49
For example, if you do, if I get back to this example, on the SHAPTILO libraries here, I just have one hook.
46:02
But I can set it to many hooks. I can say, for example, an encrypted password is a specific one. But if I say pgstat statements and, which one is it, what to explain? Which use the same hooks.
46:22
Then it's pgstat statements that will, pgstat statements will simply set the global function pointer. And then what to explain, we'll have is pg underscore init function fired.
46:40
It will see the pgstat statements function. So it need to save it so that we can use the two at the same time.
47:09
The debugger, well, I find it quite interesting to use. You need to first install the hook. So you need to install the .so file or the .dll file.
47:24
The .dll file, as far as I know, is already available with the one click installer of Enterprise DB. I don't know if there is any Linux packages that allows you to put it on Linux. I guess you will have to compile the debugger yourself.
47:44
Once you've done that, you need to add the debugger extension on the database where you want to debug functions. And then you use pgadmin, which will show you a nice
48:04
window with everything in it to do the debugging. And you will be able to do debugging on usual functions and on trigger functions also. Actually, I really like this tool.
48:24
The profiler provided by Enterprise DB is, I think, less interesting because it really, really slows down the execution of functions. So that's why I wrote the log-on-score-functions module,
48:42
which is really, really much quicker. But the profiler, yes, it's a really fine tool. Yes? Can you put the last slide up? Sorry, the? Can you put the final slide up? Yes. So this one?
49:07
I didn't put the slide yet on the pgcon website, but I will do it at the end of the talk. But if you want to find it, it's already on GitHub. It's open office format.
49:25
Another question? Well, thank you.