1#!/usr/local/bin/python3.8 2# -*- coding: utf-8 -*- 3 4# Copyright: Ansible Project 5# GNU General Public License v3.0+ (see COPYING or https://www.gnu.org/licenses/gpl-3.0.txt) 6 7from __future__ import absolute_import, division, print_function 8__metaclass__ = type 9 10DOCUMENTATION = r''' 11--- 12module: postgresql_ext 13short_description: Add or remove PostgreSQL extensions from a database 14description: 15- Add or remove PostgreSQL extensions from a database. 16options: 17 name: 18 description: 19 - Name of the extension to add or remove. 20 required: true 21 type: str 22 aliases: 23 - ext 24 db: 25 description: 26 - Name of the database to add or remove the extension to/from. 27 required: true 28 type: str 29 aliases: 30 - login_db 31 schema: 32 description: 33 - Name of the schema to add the extension to. 34 type: str 35 session_role: 36 description: 37 - Switch to session_role after connecting. 38 - The specified session_role must be a role that the current login_user is a member of. 39 - Permissions checking for SQL commands is carried out as though the session_role were the one that had logged in originally. 40 type: str 41 state: 42 description: 43 - The database extension state. 44 default: present 45 choices: [ absent, present ] 46 type: str 47 cascade: 48 description: 49 - Automatically install/remove any extensions that this extension depends on 50 that are not already installed/removed (supported since PostgreSQL 9.6). 51 type: bool 52 default: no 53 login_unix_socket: 54 description: 55 - Path to a Unix domain socket for local connections. 56 type: str 57 ssl_mode: 58 description: 59 - Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server. 60 - See U(https://www.postgresql.org/docs/current/static/libpq-ssl.html) for more information on the modes. 61 - Default of C(prefer) matches libpq default. 62 type: str 63 default: prefer 64 choices: [ allow, disable, prefer, require, verify-ca, verify-full ] 65 ca_cert: 66 description: 67 - Specifies the name of a file containing SSL certificate authority (CA) certificate(s). 68 - If the file exists, the server's certificate will be verified to be signed by one of these authorities. 69 type: str 70 aliases: [ ssl_rootcert ] 71 version: 72 description: 73 - Extension version to add or update to. Has effect with I(state=present) only. 74 - If not specified, the latest extension version will be created. 75 - It can't downgrade an extension version. 76 When version downgrade is needed, remove the extension and create new one with appropriate version. 77 - Set I(version=latest) to update the extension to the latest available version. 78 type: str 79 trust_input: 80 description: 81 - If C(no), check whether values of parameters I(ext), I(schema), 82 I(version), I(session_role) are potentially dangerous. 83 - It makes sense to use C(no) only when SQL injections via the parameters are possible. 84 type: bool 85 default: yes 86 version_added: '0.2.0' 87seealso: 88- name: PostgreSQL extensions 89 description: General information about PostgreSQL extensions. 90 link: https://www.postgresql.org/docs/current/external-extensions.html 91- name: CREATE EXTENSION reference 92 description: Complete reference of the CREATE EXTENSION command documentation. 93 link: https://www.postgresql.org/docs/current/sql-createextension.html 94- name: ALTER EXTENSION reference 95 description: Complete reference of the ALTER EXTENSION command documentation. 96 link: https://www.postgresql.org/docs/current/sql-alterextension.html 97- name: DROP EXTENSION reference 98 description: Complete reference of the DROP EXTENSION command documentation. 99 link: https://www.postgresql.org/docs/current/sql-droppublication.html 100notes: 101- Supports C(check_mode). 102- The default authentication assumes that you are either logging in as 103 or sudo'ing to the C(postgres) account on the host. 104- This module uses I(psycopg2), a Python PostgreSQL database adapter. 105- You must ensure that C(psycopg2) is installed on the host before using this module. 106- If the remote host is the PostgreSQL server (which is the default case), 107 then PostgreSQL must also be installed on the remote host. 108- For Ubuntu-based systems, install the C(postgresql), C(libpq-dev), 109 and C(python-psycopg2) packages on the remote host before using this module. 110- Incomparable versions, for example PostGIS ``unpackaged``, cannot be installed. 111requirements: [ psycopg2 ] 112author: 113- Daniel Schep (@dschep) 114- Thomas O'Donnell (@andytom) 115- Sandro Santilli (@strk) 116- Andrew Klychkov (@Andersson007) 117extends_documentation_fragment: 118- community.postgresql.postgres 119 120''' 121 122EXAMPLES = r''' 123- name: Adds postgis extension to the database acme in the schema foo 124 community.postgresql.postgresql_ext: 125 name: postgis 126 db: acme 127 schema: foo 128 129- name: Removes postgis extension to the database acme 130 community.postgresql.postgresql_ext: 131 name: postgis 132 db: acme 133 state: absent 134 135- name: Adds earthdistance extension to the database template1 cascade 136 community.postgresql.postgresql_ext: 137 name: earthdistance 138 db: template1 139 cascade: true 140 141# In the example below, if earthdistance extension is installed, 142# it will be removed too because it depends on cube: 143- name: Removes cube extension from the database acme cascade 144 community.postgresql.postgresql_ext: 145 name: cube 146 db: acme 147 cascade: yes 148 state: absent 149 150- name: Create extension foo of version 1.2 or update it if it's already created 151 community.postgresql.postgresql_ext: 152 db: acme 153 name: foo 154 version: 1.2 155 156- name: Assuming extension foo is created, update it to the latest version 157 community.postgresql.postgresql_ext: 158 db: acme 159 name: foo 160 version: latest 161''' 162 163RETURN = r''' 164query: 165 description: List of executed queries. 166 returned: always 167 type: list 168 sample: ["DROP EXTENSION \"acme\""] 169 170''' 171 172import traceback 173 174from distutils.version import LooseVersion 175 176try: 177 from psycopg2.extras import DictCursor 178except ImportError: 179 # psycopg2 is checked by connect_to_db() 180 # from ansible.module_utils.postgres 181 pass 182 183from ansible.module_utils.basic import AnsibleModule 184from ansible_collections.community.postgresql.plugins.module_utils.database import ( 185 check_input, 186) 187from ansible_collections.community.postgresql.plugins.module_utils.postgres import ( 188 connect_to_db, 189 get_conn_params, 190 postgres_common_argument_spec, 191) 192from ansible.module_utils._text import to_native 193 194executed_queries = [] 195 196 197# =========================================== 198# PostgreSQL module specific support methods. 199# 200 201def ext_exists(cursor, ext): 202 query = "SELECT * FROM pg_extension WHERE extname=%(ext)s" 203 cursor.execute(query, {'ext': ext}) 204 return cursor.rowcount == 1 205 206 207def ext_delete(cursor, ext, cascade): 208 if ext_exists(cursor, ext): 209 query = "DROP EXTENSION \"%s\"" % ext 210 if cascade: 211 query += " CASCADE" 212 cursor.execute(query) 213 executed_queries.append(query) 214 return True 215 else: 216 return False 217 218 219def ext_update_version(cursor, ext, version): 220 """Update extension version. 221 222 Return True if success. 223 224 Args: 225 cursor (cursor) -- cursor object of psycopg2 library 226 ext (str) -- extension name 227 version (str) -- extension version 228 """ 229 query = "ALTER EXTENSION \"%s\" UPDATE" % ext 230 params = {} 231 232 if version != 'latest': 233 query += " TO %(ver)s" 234 params['ver'] = version 235 236 cursor.execute(query, params) 237 executed_queries.append(cursor.mogrify(query, params)) 238 239 return True 240 241 242def ext_create(cursor, ext, schema, cascade, version): 243 query = "CREATE EXTENSION \"%s\"" % ext 244 params = {} 245 246 if schema: 247 query += " WITH SCHEMA \"%s\"" % schema 248 if version: 249 query += " VERSION %(ver)s" 250 params['ver'] = version 251 if cascade: 252 query += " CASCADE" 253 254 cursor.execute(query, params) 255 executed_queries.append(cursor.mogrify(query, params)) 256 return True 257 258 259def ext_get_versions(cursor, ext): 260 """ 261 Get the current created extension version and available versions. 262 263 Return tuple (current_version, [list of available versions]). 264 265 Note: the list of available versions contains only versions 266 that higher than the current created version. 267 If the extension is not created, this list will contain all 268 available versions. 269 270 Args: 271 cursor (cursor) -- cursor object of psycopg2 library 272 ext (str) -- extension name 273 """ 274 275 # 1. Get the current extension version: 276 query = ("SELECT extversion FROM pg_catalog.pg_extension " 277 "WHERE extname = %(ext)s") 278 279 current_version = None 280 cursor.execute(query, {'ext': ext}) 281 res = cursor.fetchone() 282 if res: 283 current_version = res[0] 284 285 # 2. Get available versions: 286 query = ("SELECT version FROM pg_available_extension_versions " 287 "WHERE name = %(ext)s") 288 cursor.execute(query, {'ext': ext}) 289 res = cursor.fetchall() 290 291 available_versions = parse_ext_versions(current_version, res) 292 293 if current_version is None: 294 current_version = False 295 296 return (current_version, available_versions) 297 298 299def parse_ext_versions(current_version, ext_ver_list): 300 """Parse ext versions. 301 302 Args: 303 current_version (str) -- version to compare elements of ext_ver_list with 304 ext_ver_list (list) -- list containing dicts with versions 305 306 Return a sorted list with versions that are higher than current_version. 307 308 Note: Incomparable versions (e.g., postgis version "unpackaged") are skipped. 309 """ 310 available_versions = [] 311 312 for line in ext_ver_list: 313 if line['version'] == 'unpackaged': 314 continue 315 316 try: 317 if current_version is None: 318 if LooseVersion(line['version']) >= LooseVersion('0'): 319 available_versions.append(line['version']) 320 else: 321 if LooseVersion(line['version']) > LooseVersion(current_version): 322 available_versions.append(line['version']) 323 except Exception: 324 # When a version cannot be compared, skip it 325 # (there's a note in the documentation) 326 continue 327 328 return sorted(available_versions, key=LooseVersion) 329 330# =========================================== 331# Module execution. 332# 333 334 335def main(): 336 argument_spec = postgres_common_argument_spec() 337 argument_spec.update( 338 db=dict(type="str", required=True, aliases=["login_db"]), 339 ext=dict(type="str", required=True, aliases=["name"]), 340 schema=dict(type="str"), 341 state=dict(type="str", default="present", choices=["absent", "present"]), 342 cascade=dict(type="bool", default=False), 343 session_role=dict(type="str"), 344 version=dict(type="str"), 345 trust_input=dict(type="bool", default=True), 346 ) 347 348 module = AnsibleModule( 349 argument_spec=argument_spec, 350 supports_check_mode=True, 351 ) 352 353 ext = module.params["ext"] 354 schema = module.params["schema"] 355 state = module.params["state"] 356 cascade = module.params["cascade"] 357 version = module.params["version"] 358 session_role = module.params["session_role"] 359 trust_input = module.params["trust_input"] 360 changed = False 361 362 if not trust_input: 363 check_input(module, ext, schema, version, session_role) 364 365 if version and state == 'absent': 366 module.warn("Parameter version is ignored when state=absent") 367 368 conn_params = get_conn_params(module, module.params) 369 db_connection = connect_to_db(module, conn_params, autocommit=True) 370 cursor = db_connection.cursor(cursor_factory=DictCursor) 371 372 try: 373 # Get extension info and available versions: 374 curr_version, available_versions = ext_get_versions(cursor, ext) 375 376 if state == "present": 377 if version == 'latest': 378 if available_versions: 379 version = available_versions[-1] 380 else: 381 version = '' 382 383 if version: 384 # If the specific version is passed and it is not available for update: 385 if version not in available_versions: 386 if not curr_version: 387 module.fail_json(msg="Passed version '%s' is not available" % version) 388 389 elif LooseVersion(curr_version) == LooseVersion(version): 390 changed = False 391 392 else: 393 module.fail_json(msg="Passed version '%s' is lower than " 394 "the current created version '%s' or " 395 "the passed version is not available" % (version, curr_version)) 396 397 # If the specific version is passed and it is higher that the current version: 398 if curr_version: 399 if LooseVersion(curr_version) < LooseVersion(version): 400 if module.check_mode: 401 changed = True 402 else: 403 changed = ext_update_version(cursor, ext, version) 404 405 # If the specific version is passed and it is created now: 406 if curr_version == version: 407 changed = False 408 409 # If the ext doesn't exist and installed: 410 elif not curr_version and available_versions: 411 if module.check_mode: 412 changed = True 413 else: 414 changed = ext_create(cursor, ext, schema, cascade, version) 415 416 # If version is not passed: 417 else: 418 if not curr_version: 419 # If the ext doesn't exist and it's installed: 420 if available_versions: 421 if module.check_mode: 422 changed = True 423 else: 424 changed = ext_create(cursor, ext, schema, cascade, version) 425 426 # If the ext doesn't exist and not installed: 427 else: 428 module.fail_json(msg="Extension %s is not installed" % ext) 429 430 elif state == "absent": 431 if curr_version: 432 if module.check_mode: 433 changed = True 434 else: 435 changed = ext_delete(cursor, ext, cascade) 436 else: 437 changed = False 438 439 except Exception as e: 440 db_connection.close() 441 module.fail_json(msg="Database query failed: %s" % to_native(e), exception=traceback.format_exc()) 442 443 db_connection.close() 444 module.exit_json(changed=changed, db=module.params["db"], ext=ext, queries=executed_queries) 445 446 447if __name__ == '__main__': 448 main() 449