1#!/usr/bin/python 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 10ANSIBLE_METADATA = {'metadata_version': '1.1', 11 'status': ['preview'], 12 'supported_by': 'community'} 13 14DOCUMENTATION = r''' 15--- 16module: postgresql_ext 17short_description: Add or remove PostgreSQL extensions from a database 18description: 19- Add or remove PostgreSQL extensions from a database. 20version_added: '1.9' 21options: 22 name: 23 description: 24 - Name of the extension to add or remove. 25 required: true 26 type: str 27 aliases: 28 - ext 29 db: 30 description: 31 - Name of the database to add or remove the extension to/from. 32 required: true 33 type: str 34 aliases: 35 - login_db 36 schema: 37 description: 38 - Name of the schema to add the extension to. 39 version_added: '2.8' 40 type: str 41 session_role: 42 description: 43 - Switch to session_role after connecting. 44 - The specified session_role must be a role that the current login_user is a member of. 45 - Permissions checking for SQL commands is carried out as though the session_role were the one that had logged in originally. 46 type: str 47 version_added: '2.8' 48 state: 49 description: 50 - The database extension state. 51 default: present 52 choices: [ absent, present ] 53 type: str 54 cascade: 55 description: 56 - Automatically install/remove any extensions that this extension depends on 57 that are not already installed/removed (supported since PostgreSQL 9.6). 58 type: bool 59 default: no 60 version_added: '2.8' 61 login_unix_socket: 62 description: 63 - Path to a Unix domain socket for local connections. 64 type: str 65 version_added: '2.8' 66 ssl_mode: 67 description: 68 - Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server. 69 - See https://www.postgresql.org/docs/current/static/libpq-ssl.html for more information on the modes. 70 - Default of C(prefer) matches libpq default. 71 type: str 72 default: prefer 73 choices: [ allow, disable, prefer, require, verify-ca, verify-full ] 74 version_added: '2.8' 75 ca_cert: 76 description: 77 - Specifies the name of a file containing SSL certificate authority (CA) certificate(s). 78 - If the file exists, the server's certificate will be verified to be signed by one of these authorities. 79 type: str 80 aliases: [ ssl_rootcert ] 81 version_added: '2.8' 82 version: 83 description: 84 - Extension version to add or update to. Has effect with I(state=present) only. 85 - If not specified, the latest extension version will be created. 86 - It can't downgrade an extension version. 87 When version downgrade is needed, remove the extension and create new one with appropriate version. 88 - Set I(version=latest) to update the extension to the latest available version. 89 type: str 90 version_added: '2.9' 91seealso: 92- name: PostgreSQL extensions 93 description: General information about PostgreSQL extensions. 94 link: https://www.postgresql.org/docs/current/external-extensions.html 95- name: CREATE EXTENSION reference 96 description: Complete reference of the CREATE EXTENSION command documentation. 97 link: https://www.postgresql.org/docs/current/sql-createextension.html 98- name: ALTER EXTENSION reference 99 description: Complete reference of the ALTER EXTENSION command documentation. 100 link: https://www.postgresql.org/docs/current/sql-alterextension.html 101- name: DROP EXTENSION reference 102 description: Complete reference of the DROP EXTENSION command documentation. 103 link: https://www.postgresql.org/docs/current/sql-droppublication.html 104notes: 105- The default authentication assumes that you are either logging in as 106 or sudo'ing to the C(postgres) account on the host. 107- This module uses I(psycopg2), a Python PostgreSQL database adapter. 108- You must ensure that psycopg2 is installed on the host before using this module. 109- If the remote host is the PostgreSQL server (which is the default case), 110 then PostgreSQL must also be installed on the remote host. 111- For Ubuntu-based systems, install the C(postgresql), C(libpq-dev), 112 and C(python-psycopg2) packages on the remote host before using this module. 113requirements: [ psycopg2 ] 114author: 115- Daniel Schep (@dschep) 116- Thomas O'Donnell (@andytom) 117- Sandro Santilli (@strk) 118- Andrew Klychkov (@Andersson007) 119extends_documentation_fragment: postgres 120''' 121 122EXAMPLES = r''' 123- name: Adds postgis extension to the database acme in the schema foo 124 postgresql_ext: 125 name: postgis 126 db: acme 127 schema: foo 128 129- name: Removes postgis extension to the database acme 130 postgresql_ext: 131 name: postgis 132 db: acme 133 state: absent 134 135- name: Adds earthdistance extension to the database template1 cascade 136 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 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 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 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.module_utils.postgres import ( 185 connect_to_db, 186 get_conn_params, 187 postgres_common_argument_spec, 188) 189from ansible.module_utils._text import to_native 190 191executed_queries = [] 192 193 194class NotSupportedError(Exception): 195 pass 196 197 198# =========================================== 199# PostgreSQL module specific support methods. 200# 201 202def ext_exists(cursor, ext): 203 query = "SELECT * FROM pg_extension WHERE extname=%(ext)s" 204 cursor.execute(query, {'ext': ext}) 205 return cursor.rowcount == 1 206 207 208def ext_delete(cursor, ext, cascade): 209 if ext_exists(cursor, ext): 210 query = "DROP EXTENSION \"%s\"" % ext 211 if cascade: 212 query += " CASCADE" 213 cursor.execute(query) 214 executed_queries.append(query) 215 return True 216 else: 217 return False 218 219 220def ext_update_version(cursor, ext, version): 221 """Update extension version. 222 223 Return True if success. 224 225 Args: 226 cursor (cursor) -- cursor object of psycopg2 library 227 ext (str) -- extension name 228 version (str) -- extension version 229 """ 230 if version != 'latest': 231 query = ("ALTER EXTENSION \"%s\" UPDATE TO '%s'" % (ext, version)) 232 else: 233 query = ("ALTER EXTENSION \"%s\" UPDATE" % ext) 234 cursor.execute(query) 235 executed_queries.append(query) 236 return True 237 238 239def ext_create(cursor, ext, schema, cascade, version): 240 query = "CREATE EXTENSION \"%s\"" % ext 241 if schema: 242 query += " WITH SCHEMA \"%s\"" % schema 243 if version: 244 query += " VERSION '%s'" % version 245 if cascade: 246 query += " CASCADE" 247 cursor.execute(query) 248 executed_queries.append(query) 249 return True 250 251 252def ext_get_versions(cursor, ext): 253 """ 254 Get the current created extension version and available versions. 255 256 Return tuple (current_version, [list of available versions]). 257 258 Note: the list of available versions contains only versions 259 that higher than the current created version. 260 If the extension is not created, this list will contain all 261 available versions. 262 263 Args: 264 cursor (cursor) -- cursor object of psycopg2 library 265 ext (str) -- extension name 266 """ 267 268 # 1. Get the current extension version: 269 query = ("SELECT extversion FROM pg_catalog.pg_extension " 270 "WHERE extname = '%s'" % ext) 271 272 current_version = '0' 273 cursor.execute(query) 274 res = cursor.fetchone() 275 if res: 276 current_version = res[0] 277 278 # 2. Get available versions: 279 query = ("SELECT version FROM pg_available_extension_versions " 280 "WHERE name = '%s'" % ext) 281 cursor.execute(query) 282 res = cursor.fetchall() 283 284 available_versions = [] 285 if res: 286 # Make the list of available versions: 287 for line in res: 288 if LooseVersion(line[0]) > LooseVersion(current_version): 289 available_versions.append(line['version']) 290 291 if current_version == '0': 292 current_version = False 293 294 return (current_version, available_versions) 295 296# =========================================== 297# Module execution. 298# 299 300 301def main(): 302 argument_spec = postgres_common_argument_spec() 303 argument_spec.update( 304 db=dict(type="str", required=True, aliases=["login_db"]), 305 ext=dict(type="str", required=True, aliases=["name"]), 306 schema=dict(type="str"), 307 state=dict(type="str", default="present", choices=["absent", "present"]), 308 cascade=dict(type="bool", default=False), 309 session_role=dict(type="str"), 310 version=dict(type="str"), 311 ) 312 313 module = AnsibleModule( 314 argument_spec=argument_spec, 315 supports_check_mode=True, 316 ) 317 318 ext = module.params["ext"] 319 schema = module.params["schema"] 320 state = module.params["state"] 321 cascade = module.params["cascade"] 322 version = module.params["version"] 323 changed = False 324 325 if version and state == 'absent': 326 module.warn("Parameter version is ignored when state=absent") 327 328 conn_params = get_conn_params(module, module.params) 329 db_connection = connect_to_db(module, conn_params, autocommit=True) 330 cursor = db_connection.cursor(cursor_factory=DictCursor) 331 332 try: 333 # Get extension info and available versions: 334 curr_version, available_versions = ext_get_versions(cursor, ext) 335 336 if state == "present": 337 if version == 'latest': 338 if available_versions: 339 version = available_versions[-1] 340 else: 341 version = '' 342 343 if version: 344 # If the specific version is passed and it is not available for update: 345 if version not in available_versions: 346 if not curr_version: 347 module.fail_json(msg="Passed version '%s' is not available" % version) 348 349 elif LooseVersion(curr_version) == LooseVersion(version): 350 changed = False 351 352 else: 353 module.fail_json(msg="Passed version '%s' is lower than " 354 "the current created version '%s' or " 355 "the passed version is not available" % (version, curr_version)) 356 357 # If the specific version is passed and it is higher that the current version: 358 if curr_version and version: 359 if LooseVersion(curr_version) < LooseVersion(version): 360 if module.check_mode: 361 changed = True 362 else: 363 changed = ext_update_version(cursor, ext, version) 364 365 # If the specific version is passed and it is created now: 366 if curr_version == version: 367 changed = False 368 369 # If the ext doesn't exist and installed: 370 elif not curr_version and available_versions: 371 if module.check_mode: 372 changed = True 373 else: 374 changed = ext_create(cursor, ext, schema, cascade, version) 375 376 # If version is not passed: 377 else: 378 if not curr_version: 379 # If the ext doesn't exist and it's installed: 380 if available_versions: 381 if module.check_mode: 382 changed = True 383 else: 384 changed = ext_create(cursor, ext, schema, cascade, version) 385 386 # If the ext doesn't exist and not installed: 387 else: 388 module.fail_json(msg="Extension %s is not installed" % ext) 389 390 elif state == "absent": 391 if curr_version: 392 if module.check_mode: 393 changed = True 394 else: 395 changed = ext_delete(cursor, ext, cascade) 396 else: 397 changed = False 398 399 except Exception as e: 400 db_connection.close() 401 module.fail_json(msg="Database query failed: %s" % to_native(e), exception=traceback.format_exc()) 402 403 db_connection.close() 404 module.exit_json(changed=changed, db=module.params["db"], ext=ext, queries=executed_queries) 405 406 407if __name__ == '__main__': 408 main() 409