1#!/usr/bin/python 2# -*- coding: utf-8 -*- 3 4# Copyright: (c) 2016, 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_schema 17short_description: Add or remove PostgreSQL schema 18description: 19- Add or remove PostgreSQL schema. 20version_added: '2.3' 21options: 22 name: 23 description: 24 - Name of the schema to add or remove. 25 required: true 26 type: str 27 aliases: 28 - schema 29 database: 30 description: 31 - Name of the database to connect to and add or remove the schema. 32 type: str 33 default: postgres 34 aliases: 35 - db 36 - login_db 37 owner: 38 description: 39 - Name of the role to set as owner of the schema. 40 type: str 41 session_role: 42 version_added: '2.8' 43 description: 44 - Switch to session_role after connecting. 45 - The specified session_role must be a role that the current login_user is a member of. 46 - Permissions checking for SQL commands is carried out as though the session_role 47 were the one that had logged in originally. 48 type: str 49 state: 50 description: 51 - The schema state. 52 type: str 53 default: present 54 choices: [ absent, present ] 55 cascade_drop: 56 description: 57 - Drop schema with CASCADE to remove child objects. 58 type: bool 59 default: false 60 version_added: '2.8' 61 ssl_mode: 62 description: 63 - Determines whether or with what priority a secure SSL TCP/IP connection will be negotiated with the server. 64 - See https://www.postgresql.org/docs/current/static/libpq-ssl.html for more information on the modes. 65 - Default of C(prefer) matches libpq default. 66 type: str 67 default: prefer 68 choices: [ allow, disable, prefer, require, verify-ca, verify-full ] 69 version_added: '2.8' 70 ca_cert: 71 description: 72 - Specifies the name of a file containing SSL certificate authority (CA) certificate(s). 73 - If the file exists, the server's certificate will be verified to be signed by one of these authorities. 74 type: str 75 aliases: [ ssl_rootcert ] 76 version_added: '2.8' 77seealso: 78- name: PostgreSQL schemas 79 description: General information about PostgreSQL schemas. 80 link: https://www.postgresql.org/docs/current/ddl-schemas.html 81- name: CREATE SCHEMA reference 82 description: Complete reference of the CREATE SCHEMA command documentation. 83 link: https://www.postgresql.org/docs/current/sql-createschema.html 84- name: ALTER SCHEMA reference 85 description: Complete reference of the ALTER SCHEMA command documentation. 86 link: https://www.postgresql.org/docs/current/sql-alterschema.html 87- name: DROP SCHEMA reference 88 description: Complete reference of the DROP SCHEMA command documentation. 89 link: https://www.postgresql.org/docs/current/sql-dropschema.html 90author: 91- Flavien Chantelot (@Dorn-) <contact@flavien.io> 92- Thomas O'Donnell (@andytom) 93extends_documentation_fragment: postgres 94''' 95 96EXAMPLES = r''' 97- name: Create a new schema with name acme in test database 98 postgresql_schema: 99 db: test 100 name: acme 101 102- name: Create a new schema acme with a user bob who will own it 103 postgresql_schema: 104 name: acme 105 owner: bob 106 107- name: Drop schema "acme" with cascade 108 postgresql_schema: 109 name: acme 110 state: absent 111 cascade_drop: yes 112''' 113 114RETURN = r''' 115schema: 116 description: Name of the schema. 117 returned: success, changed 118 type: str 119 sample: "acme" 120queries: 121 description: List of executed queries. 122 returned: always 123 type: list 124 sample: ["CREATE SCHEMA \"acme\""] 125''' 126 127import traceback 128 129try: 130 from psycopg2.extras import DictCursor 131except ImportError: 132 # psycopg2 is checked by connect_to_db() 133 # from ansible.module_utils.postgres 134 pass 135 136from ansible.module_utils.basic import AnsibleModule 137from ansible.module_utils.postgres import ( 138 connect_to_db, 139 get_conn_params, 140 postgres_common_argument_spec, 141) 142from ansible.module_utils.database import SQLParseError, pg_quote_identifier 143from ansible.module_utils._text import to_native 144 145executed_queries = [] 146 147 148class NotSupportedError(Exception): 149 pass 150 151 152# =========================================== 153# PostgreSQL module specific support methods. 154# 155 156def set_owner(cursor, schema, owner): 157 query = "ALTER SCHEMA %s OWNER TO %s" % ( 158 pg_quote_identifier(schema, 'schema'), 159 pg_quote_identifier(owner, 'role')) 160 cursor.execute(query) 161 executed_queries.append(query) 162 return True 163 164 165def get_schema_info(cursor, schema): 166 query = ("SELECT schema_owner AS owner " 167 "FROM information_schema.schemata " 168 "WHERE schema_name = %(schema)s") 169 cursor.execute(query, {'schema': schema}) 170 return cursor.fetchone() 171 172 173def schema_exists(cursor, schema): 174 query = ("SELECT schema_name FROM information_schema.schemata " 175 "WHERE schema_name = %(schema)s") 176 cursor.execute(query, {'schema': schema}) 177 return cursor.rowcount == 1 178 179 180def schema_delete(cursor, schema, cascade): 181 if schema_exists(cursor, schema): 182 query = "DROP SCHEMA %s" % pg_quote_identifier(schema, 'schema') 183 if cascade: 184 query += " CASCADE" 185 cursor.execute(query) 186 executed_queries.append(query) 187 return True 188 else: 189 return False 190 191 192def schema_create(cursor, schema, owner): 193 if not schema_exists(cursor, schema): 194 query_fragments = ['CREATE SCHEMA %s' % pg_quote_identifier(schema, 'schema')] 195 if owner: 196 query_fragments.append('AUTHORIZATION %s' % pg_quote_identifier(owner, 'role')) 197 query = ' '.join(query_fragments) 198 cursor.execute(query) 199 executed_queries.append(query) 200 return True 201 else: 202 schema_info = get_schema_info(cursor, schema) 203 if owner and owner != schema_info['owner']: 204 return set_owner(cursor, schema, owner) 205 else: 206 return False 207 208 209def schema_matches(cursor, schema, owner): 210 if not schema_exists(cursor, schema): 211 return False 212 else: 213 schema_info = get_schema_info(cursor, schema) 214 if owner and owner != schema_info['owner']: 215 return False 216 else: 217 return True 218 219# =========================================== 220# Module execution. 221# 222 223 224def main(): 225 argument_spec = postgres_common_argument_spec() 226 argument_spec.update( 227 schema=dict(type="str", required=True, aliases=['name']), 228 owner=dict(type="str", default=""), 229 database=dict(type="str", default="postgres", aliases=["db", "login_db"]), 230 cascade_drop=dict(type="bool", default=False), 231 state=dict(type="str", default="present", choices=["absent", "present"]), 232 session_role=dict(type="str"), 233 ) 234 235 module = AnsibleModule( 236 argument_spec=argument_spec, 237 supports_check_mode=True, 238 ) 239 240 schema = module.params["schema"] 241 owner = module.params["owner"] 242 state = module.params["state"] 243 cascade_drop = module.params["cascade_drop"] 244 changed = False 245 246 conn_params = get_conn_params(module, module.params) 247 db_connection = connect_to_db(module, conn_params, autocommit=True) 248 cursor = db_connection.cursor(cursor_factory=DictCursor) 249 250 try: 251 if module.check_mode: 252 if state == "absent": 253 changed = not schema_exists(cursor, schema) 254 elif state == "present": 255 changed = not schema_matches(cursor, schema, owner) 256 module.exit_json(changed=changed, schema=schema) 257 258 if state == "absent": 259 try: 260 changed = schema_delete(cursor, schema, cascade_drop) 261 except SQLParseError as e: 262 module.fail_json(msg=to_native(e), exception=traceback.format_exc()) 263 264 elif state == "present": 265 try: 266 changed = schema_create(cursor, schema, owner) 267 except SQLParseError as e: 268 module.fail_json(msg=to_native(e), exception=traceback.format_exc()) 269 except NotSupportedError as e: 270 module.fail_json(msg=to_native(e), exception=traceback.format_exc()) 271 except SystemExit: 272 # Avoid catching this on Python 2.4 273 raise 274 except Exception as e: 275 module.fail_json(msg="Database query failed: %s" % to_native(e), exception=traceback.format_exc()) 276 277 db_connection.close() 278 module.exit_json(changed=changed, schema=schema, queries=executed_queries) 279 280 281if __name__ == '__main__': 282 main() 283