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