1#!/usr/bin/python
2# -*- coding: utf-8 -*-
3#
4# (c) 2014, Jens Depuydt <http://www.jensd.be>
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
10
11ANSIBLE_METADATA = {'metadata_version': '1.1',
12                    'status': ['preview'],
13                    'supported_by': 'community'}
14
15DOCUMENTATION = r'''
16---
17module: postgresql_lang
18short_description: Adds, removes or changes procedural languages with a PostgreSQL database
19description:
20- Adds, removes or changes procedural languages with a PostgreSQL database.
21- This module allows you to add a language, remote a language or change the trust
22  relationship with a PostgreSQL database.
23- The module can be used on the machine where executed or on a remote host.
24- When removing a language from a database, it is possible that dependencies prevent
25  the database from being removed. In that case, you can specify I(cascade=yes) to
26  automatically drop objects that depend on the language (such as functions in the
27  language).
28- In case the language can't be deleted because it is required by the
29  database system, you can specify I(fail_on_drop=no) to ignore the error.
30- Be careful when marking a language as trusted since this could be a potential
31  security breach. Untrusted languages allow only users with the PostgreSQL superuser
32  privilege to use this language to create new functions.
33version_added: '1.7'
34options:
35  lang:
36    description:
37    - Name of the procedural language to add, remove or change.
38    required: true
39    type: str
40    aliases:
41    - name
42  trust:
43    description:
44    - Make this language trusted for the selected db.
45    type: bool
46    default: 'no'
47  db:
48    description:
49    - Name of database to connect to and where the language will be added, removed or changed.
50    type: str
51    aliases:
52    - login_db
53  force_trust:
54    description:
55    - Marks the language as trusted, even if it's marked as untrusted in pg_pltemplate.
56    - Use with care!
57    type: bool
58    default: 'no'
59  fail_on_drop:
60    description:
61    - If C(yes), fail when removing a language. Otherwise just log and continue.
62    - In some cases, it is not possible to remove a language (used by the db-system).
63    - When dependencies block the removal, consider using I(cascade).
64    type: bool
65    default: 'yes'
66  cascade:
67    description:
68    - When dropping a language, also delete object that depend on this language.
69    - Only used when I(state=absent).
70    type: bool
71    default: 'no'
72  session_role:
73    version_added: '2.8'
74    description:
75    - Switch to session_role after connecting.
76    - The specified I(session_role) must be a role that the current I(login_user) is a member of.
77    - Permissions checking for SQL commands is carried out as though the I(session_role) were the one that had logged in originally.
78    type: str
79  state:
80    description:
81    - The state of the language for the selected database.
82    type: str
83    default: present
84    choices: [ absent, present ]
85  login_unix_socket:
86    description:
87      - Path to a Unix domain socket for local connections.
88    type: str
89    version_added: '2.8'
90  ssl_mode:
91    description:
92      - Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server.
93      - See U(https://www.postgresql.org/docs/current/static/libpq-ssl.html) for more information on the modes.
94      - Default of C(prefer) matches libpq default.
95    type: str
96    default: prefer
97    choices: [ allow, disable, prefer, require, verify-ca, verify-full ]
98    version_added: '2.8'
99  ca_cert:
100    description:
101      - Specifies the name of a file containing SSL certificate authority (CA) certificate(s).
102      - If the file exists, the server's certificate will be verified to be signed by one of these authorities.
103    type: str
104    aliases: [ ssl_rootcert ]
105    version_added: '2.8'
106seealso:
107- name: PostgreSQL languages
108  description: General information about PostgreSQL languages.
109  link: https://www.postgresql.org/docs/current/xplang.html
110- name: CREATE LANGUAGE reference
111  description: Complete reference of the CREATE LANGUAGE command documentation.
112  link: https://www.postgresql.org/docs/current/sql-createlanguage.html
113- name: ALTER LANGUAGE reference
114  description: Complete reference of the ALTER LANGUAGE command documentation.
115  link: https://www.postgresql.org/docs/current/sql-alterlanguage.html
116- name: DROP LANGUAGE reference
117  description: Complete reference of the DROP LANGUAGE command documentation.
118  link: https://www.postgresql.org/docs/current/sql-droplanguage.html
119author:
120- Jens Depuydt (@jensdepuydt)
121- Thomas O'Donnell (@andytom)
122extends_documentation_fragment: postgres
123'''
124
125EXAMPLES = r'''
126- name: Add language pltclu to database testdb if it doesn't exist
127  postgresql_lang: db=testdb lang=pltclu state=present
128
129# Add language pltclu to database testdb if it doesn't exist and mark it as trusted.
130# Marks the language as trusted if it exists but isn't trusted yet.
131# force_trust makes sure that the language will be marked as trusted
132- name: Add language pltclu to database testdb if it doesn't exist and mark it as trusted
133  postgresql_lang:
134    db: testdb
135    lang: pltclu
136    state: present
137    trust: yes
138    force_trust: yes
139
140- name: Remove language pltclu from database testdb
141  postgresql_lang:
142    db: testdb
143    lang: pltclu
144    state: absent
145
146- name: Remove language pltclu from database testdb and remove all dependencies
147  postgresql_lang:
148    db: testdb
149    lang: pltclu
150    state: absent
151    cascade: yes
152
153- name: Remove language c from database testdb but ignore errors if something prevents the removal
154  postgresql_lang:
155    db: testdb
156    lang: pltclu
157    state: absent
158    fail_on_drop: no
159'''
160
161RETURN = r'''
162queries:
163  description: List of executed queries.
164  returned: always
165  type: list
166  sample: ['CREATE LANGUAGE "acme"']
167  version_added: '2.8'
168'''
169
170from ansible.module_utils.basic import AnsibleModule
171from ansible.module_utils.postgres import (
172    connect_to_db,
173    get_conn_params,
174    postgres_common_argument_spec,
175)
176
177executed_queries = []
178
179
180def lang_exists(cursor, lang):
181    """Checks if language exists for db"""
182    query = "SELECT lanname FROM pg_language WHERE lanname = %(lang)s"
183    cursor.execute(query, {'lang': lang})
184    return cursor.rowcount > 0
185
186
187def lang_istrusted(cursor, lang):
188    """Checks if language is trusted for db"""
189    query = "SELECT lanpltrusted FROM pg_language WHERE lanname = %(lang)s"
190    cursor.execute(query, {'lang': lang})
191    return cursor.fetchone()[0]
192
193
194def lang_altertrust(cursor, lang, trust):
195    """Changes if language is trusted for db"""
196    query = "UPDATE pg_language SET lanpltrusted = %(trust)s WHERE lanname = %(lang)s"
197    cursor.execute(query, {'trust': trust, 'lang': lang})
198    executed_queries.append(cursor.mogrify(query, {'trust': trust, 'lang': lang}))
199    return True
200
201
202def lang_add(cursor, lang, trust):
203    """Adds language for db"""
204    if trust:
205        query = 'CREATE TRUSTED LANGUAGE "%s"' % lang
206    else:
207        query = 'CREATE LANGUAGE "%s"' % lang
208    executed_queries.append(query)
209    cursor.execute(query)
210    return True
211
212
213def lang_drop(cursor, lang, cascade):
214    """Drops language for db"""
215    cursor.execute("SAVEPOINT ansible_pgsql_lang_drop")
216    try:
217        if cascade:
218            query = "DROP LANGUAGE \"%s\" CASCADE" % lang
219        else:
220            query = "DROP LANGUAGE \"%s\"" % lang
221        executed_queries.append(query)
222        cursor.execute(query)
223    except Exception:
224        cursor.execute("ROLLBACK TO SAVEPOINT ansible_pgsql_lang_drop")
225        cursor.execute("RELEASE SAVEPOINT ansible_pgsql_lang_drop")
226        return False
227    cursor.execute("RELEASE SAVEPOINT ansible_pgsql_lang_drop")
228    return True
229
230
231def main():
232    argument_spec = postgres_common_argument_spec()
233    argument_spec.update(
234        db=dict(type="str", required=True, aliases=["login_db"]),
235        lang=dict(type="str", required=True, aliases=["name"]),
236        state=dict(type="str", default="present", choices=["absent", "present"]),
237        trust=dict(type="bool", default="no"),
238        force_trust=dict(type="bool", default="no"),
239        cascade=dict(type="bool", default="no"),
240        fail_on_drop=dict(type="bool", default="yes"),
241        session_role=dict(type="str"),
242    )
243
244    module = AnsibleModule(
245        argument_spec=argument_spec,
246        supports_check_mode=True,
247    )
248
249    db = module.params["db"]
250    lang = module.params["lang"]
251    state = module.params["state"]
252    trust = module.params["trust"]
253    force_trust = module.params["force_trust"]
254    cascade = module.params["cascade"]
255    fail_on_drop = module.params["fail_on_drop"]
256
257    conn_params = get_conn_params(module, module.params)
258    db_connection = connect_to_db(module, conn_params, autocommit=False)
259    cursor = db_connection.cursor()
260
261    changed = False
262    kw = {'db': db, 'lang': lang, 'trust': trust}
263
264    if state == "present":
265        if lang_exists(cursor, lang):
266            lang_trusted = lang_istrusted(cursor, lang)
267            if (lang_trusted and not trust) or (not lang_trusted and trust):
268                if module.check_mode:
269                    changed = True
270                else:
271                    changed = lang_altertrust(cursor, lang, trust)
272        else:
273            if module.check_mode:
274                changed = True
275            else:
276                changed = lang_add(cursor, lang, trust)
277                if force_trust:
278                    changed = lang_altertrust(cursor, lang, trust)
279
280    else:
281        if lang_exists(cursor, lang):
282            if module.check_mode:
283                changed = True
284                kw['lang_dropped'] = True
285            else:
286                changed = lang_drop(cursor, lang, cascade)
287                if fail_on_drop and not changed:
288                    msg = "unable to drop language, use cascade to delete dependencies or fail_on_drop=no to ignore"
289                    module.fail_json(msg=msg)
290                kw['lang_dropped'] = changed
291
292    if changed:
293        if module.check_mode:
294            db_connection.rollback()
295        else:
296            db_connection.commit()
297
298    kw['changed'] = changed
299    kw['queries'] = executed_queries
300    db_connection.close()
301    module.exit_json(**kw)
302
303
304if __name__ == '__main__':
305    main()
306