1 #include "libzbxpgsql.h"
2 
3 /*
4  * Function: pg_get_databases
5  *
6  * Returns a null delimited list of database names which the connected
7  * PostgreSQL user is allowed to connect to (i.e. has been granted 'CONNECT').
8  *
9  * Returns: Multi-string E.g. "database1\0database2\0database3\0\0"
10  */
pg_get_databases(AGENT_REQUEST * request,AGENT_RESULT * result)11 static char *pg_get_databases(AGENT_REQUEST *request, AGENT_RESULT *result) {
12     const char  *__function_name = "pg_get_databases"; // Function name for log file
13 
14     PGconn      *conn = NULL;
15     PGresult    *res = NULL;
16 
17     char        *databases = NULL, *c = NULL;
18     int         rows = 0, i = 0, bufferlen = 0;
19 
20     zabbix_log(LOG_LEVEL_DEBUG, "In %s", __function_name);
21 
22     // connect to PostgreSQL
23     conn = pg_connect_request(request, result);
24     if (NULL == conn)
25         goto out;
26 
27     // get connectable databases
28     res = pg_exec(conn, "SELECT datname FROM pg_database WHERE datallowconn = 't' AND pg_catalog.has_database_privilege(current_user, oid, 'CONNECT');", NULL);
29     if(0 == PQntuples(res)) {
30         set_err_result(result, "Failed to enumerate connectable PostgreSQL databases");
31         goto out;
32     }
33 
34     rows = PQntuples(res);
35 
36     // iterate over each row to calculate buffer size
37     bufferlen = 1; // 1 for null terminator
38     for(i = 0; i < rows; i++) {
39         bufferlen += strlen(PQgetvalue(res, i, 0)) + 1;
40     }
41 
42     // allocate databases multi-string
43     databases = zbx_malloc(databases, sizeof(char) * bufferlen);
44     memset(databases, '\0', sizeof(char) * bufferlen);
45 
46     // iterate over each row and copy the results
47     c = databases;
48     for(i = 0; i < rows; i++) {
49         c = strcat2(c, PQgetvalue(res, i, 0)) + 1;
50     }
51 
52 out:
53     PQclear(res);
54     PQfinish(conn);
55 
56     zabbix_log(LOG_LEVEL_DEBUG, "End of %s", __function_name);
57 
58     return databases;
59 }
60 
61 /*
62  * Function: pg_get_discovery
63  *
64  * Executes a PostgreSQL Query using connection details from a Zabbix agent
65  * request structure and updates the agent result structure with the JSON
66  * discovery data for each returned row.
67  *
68  * Query parameters may be provided as a NULL terminated sequence of *char
69  * values in the ... parameter.
70  *
71  * Parameter [request]: Zabbix agent request structure.
72  *          Passed to pg_connect_request to fetch as valid PostgreSQL
73  *          server connection
74  *
75  * Parameter [result]:  Zabbix agent result structure
76  *
77  * Paramater [query]:   PostgreSQL query to execute. Query should column names
78  *           that match the desired discovery fields.
79  *
80  * Parameter [deep]:    Execute against all connectable databases
81  *
82  * Returns: SYSINFO_RET_OK or SYSINFO_RET_FAIL on error
83  */
pg_get_discovery(AGENT_REQUEST * request,AGENT_RESULT * result,const char * query,PGparams params)84  int    pg_get_discovery(AGENT_REQUEST *request, AGENT_RESULT *result, const char *query, PGparams params)
85  {
86     int         ret = SYSINFO_RET_FAIL;                 // Request result code
87     const char  *__function_name = "pg_get_discovery";  // Function name for log file
88 
89     struct      zbx_json j;                             // JSON response for discovery rule
90 
91     int         i = 0, x = 0, columns = 0, rows = 0;
92     char        *c = NULL;
93     char        buffer[MAX_STRING_LEN];
94 
95     PGconn      *conn = NULL;
96     PGresult    *res = NULL;
97 
98     zabbix_log(LOG_LEVEL_DEBUG, "In %s(%s)", __function_name, request->key);
99 
100     // Connect to PostreSQL
101     if(NULL == (conn = pg_connect_request(request, result)))
102         goto out;
103 
104     // Execute a query
105     res = pg_exec(conn, query, params);
106     if(PQresultStatus(res) != PGRES_TUPLES_OK) {
107         set_err_result(result, "PostgreSQL query error: %s", PQresultErrorMessage(res));
108         goto out;
109     }
110 
111     // count rows and columns
112     rows = PQntuples(res);
113     columns = PQnfields(res);
114 
115     // Create JSON array of discovered objects
116     zbx_json_init(&j, ZBX_JSON_STAT_BUF_LEN);
117     zbx_json_addarray(&j, ZBX_PROTO_TAG_DATA);
118 
119     // create discovery instance for each row
120     for(i = 0; i < rows; i++) {
121         zbx_json_addobject(&j, NULL);
122 
123         // add each row field as a discovery field
124         for(x = 0; x < columns; x++) {
125             // set discovery key name to uppercase column name
126             zbx_snprintf(buffer, sizeof(buffer), "{#%s}", PQfname(res, x));
127             for(c = &buffer[0]; *c; c++)
128                 *c = toupper(*c);
129 
130             zbx_json_addstring(&j, buffer, PQgetvalue(res, i, x), ZBX_JSON_TYPE_STRING);
131         }
132 
133         zbx_json_close(&j);
134     }
135 
136     // Finalize JSON response
137     zbx_json_close(&j);
138     SET_STR_RESULT(result, strdup(j.buffer));
139     zbx_json_free(&j);
140 
141     ret = SYSINFO_RET_OK;
142 
143 out:
144 
145     PQclear(res);
146     PQfinish(conn);
147 
148     zabbix_log(LOG_LEVEL_DEBUG, "End of %s(%s)", __function_name, request->key);
149     return ret;
150 }
151 
152 /*
153  * Function: pg_get_discovery_wide
154  *
155  * Executes a PostgreSQL Query on all accessible databases, using connection
156  * details from a Zabbix agent request structure and updates the agent result
157  * structure with the JSON discovery data for each returned row.
158  *
159  * Query parameters may be provided as a NULL terminated sequence of *char
160  * values in the ... parameter.
161  *
162  * Parameter [request]: Zabbix agent request structure.
163  *          Passed to pg_connect_request to fetch as valid PostgreSQL
164  *          server connection
165  *
166  * Parameter [result]:  Zabbix agent result structure
167  *
168  * Paramater [query]:   PostgreSQL query to execute. Query should column names
169  *           that match the desired discovery fields.
170  *
171  * Parameter [deep]:    Execute against all connectable databases
172  *
173  * Returns: SYSINFO_RET_OK or SYSINFO_RET_FAIL on error
174  */
pg_get_discovery_wide(AGENT_REQUEST * request,AGENT_RESULT * result,const char * query,PGparams params)175  int    pg_get_discovery_wide(AGENT_REQUEST *request, AGENT_RESULT *result, const char *query, PGparams params)
176  {
177     int         ret = SYSINFO_RET_FAIL;                     // Request result code
178     const char  *__function_name = "pg_get_discovery_wide"; // Function name for log file
179 
180     struct      zbx_json j;                                 // JSON response for discovery rule
181 
182     int         i = 0, x = 0, columns = 0, rows = 0;
183     char        *databases = NULL, *db = NULL, *c = NULL;
184     char        *connstring = NULL;
185     char        buffer[MAX_STRING_LEN];
186 
187     PGconn      *conn = NULL;
188     PGresult    *res = NULL;
189 
190     zabbix_log(LOG_LEVEL_DEBUG, "In %s(%s)", __function_name, request->key);
191 
192     // get a list of databases
193     databases = pg_get_databases(request, result);
194     if (NULL == databases)
195         goto out;
196 
197     // Create JSON array of discovered objects
198     zbx_json_init(&j, ZBX_JSON_STAT_BUF_LEN);
199     zbx_json_addarray(&j, ZBX_PROTO_TAG_DATA);
200 
201     // query each accessible database
202     for (db = databases; *db; db += strlen(db) + 1) {
203         // build connection string
204         zbx_free(connstring);
205         connstring = build_connstring(get_rparam(request, PARAM_CONN_STRING), db);
206 
207         // Connect to PostreSQL
208         if(NULL == (conn = pg_connect(connstring, result)))
209             goto out;
210 
211         // Execute a query
212         res = pg_exec(conn, query, params);
213         if(PQresultStatus(res) != PGRES_TUPLES_OK) {
214             set_err_result(result, "PostgreSQL query error: %s", PQresultErrorMessage(res));
215             goto out;
216         }
217 
218         // count rows and columns
219         rows = PQntuples(res);
220         columns = PQnfields(res);
221 
222         // create discovery instance for each row
223         for(i = 0; i < rows; i++) {
224             zbx_json_addobject(&j, NULL);
225 
226             // add each row field as a discovery field
227             for(x = 0; x < columns; x++) {
228                 // set discovery key name to uppercase column name
229                 zbx_snprintf(buffer, sizeof(buffer), "{#%s}", PQfname(res, x));
230                 for(c = &buffer[0]; *c; c++)
231                     *c = toupper(*c);
232 
233                 zbx_json_addstring(&j, buffer, PQgetvalue(res, i, x), ZBX_JSON_TYPE_STRING);
234             }
235 
236             zbx_json_close(&j);
237         }
238 
239         // clean up
240         zbx_free(connstring);
241         PQclear(res);
242         PQfinish(conn);
243         conn = NULL; // bypass 2nd PQfinish
244     }
245 
246     // Finalize JSON response
247     zbx_json_close(&j);
248     SET_STR_RESULT(result, strdup(j.buffer));
249     zbx_json_free(&j);
250 
251     ret = SYSINFO_RET_OK;
252 
253 out:
254     zbx_free(connstring);
255     zbx_free(databases);
256     if (NULL != conn)
257         PQfinish(conn);
258 
259     zabbix_log(LOG_LEVEL_DEBUG, "End of %s(%s)", __function_name, request->key);
260     return ret;
261 }
262 
263