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