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