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