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