1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
#!/usr/bin/python
# This file is part of Ansible
#
# Ansible is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# Ansible is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with Ansible. If not, see <http://www.gnu.org/licenses/>.
try:
import psycopg2
except ImportError:
postgresqldb_found = False
else:
postgresqldb_found = True
# ===========================================
# PostgreSQL module specific support methods.
#
def user_exists(cursor, user):
query = "SELECT rolname FROM pg_roles WHERE rolname=%(user)s"
cursor.execute(query, {'user': user})
return cursor.rowcount > 0
def user_add(cursor, user, password, db):
"""Create a new user with write access to the database"""
query = "CREATE USER %(user)s with PASSWORD '%(password)s'"
cursor.execute(query % {"user": user, "password": password})
grant_privileges(cursor, user, db)
return True
def has_privileges(cursor, user, db):
"""Check if the user has create privileges on the database"""
query = "SELECT has_database_privilege(%(user)s, %(db)s, 'CREATE')"
cursor.execute(query, {'user': user, 'db': db})
return cursor.fetchone()[0]
def grant_privileges(cursor, user, db):
"""Grant all privileges on the database"""
query = "GRANT ALL PRIVILEGES ON DATABASE %(db)s TO %(user)s"
cursor.execute(query % {'user': user, 'db': db})
def revoke_privileges(cursor, user, db):
"""Revoke all privileges on the database"""
query = "REVOKE ALL PRIVILEGES ON DATABASE %(db)s FROM %(user)s"
cursor.execute(query % {'user': user, 'db': db})
def user_mod(cursor, user, password, db):
"""Update password and permissions"""
changed = False
# Handle passwords.
if password is not None:
select = "SELECT rolpassword FROM pg_authid where rolname=%(user)s"
cursor.execute(select, {"user": user})
current_pass_hash = cursor.fetchone()[0]
# Not sure how to hash the new password, so we just initiate the
# change and check if the hash changed
alter = "ALTER USER %(user)s WITH PASSWORD '%(password)s'"
cursor.execute(alter % {"user": user, "password": password})
cursor.execute(select, {"user": user})
new_pass_hash = cursor.fetchone()[0]
if current_pass_hash != new_pass_hash:
changed = True
# Handle privileges.
# For now, we just check if the user has access to the database
if not has_privileges(cursor, user, db):
grant_privileges(cursor, user, db)
changed = True
return changed
def user_delete(cursor, user, db):
"""Delete a user, first revoking privileges"""
revoke_privileges(cursor, user, db)
cursor.execute("DROP USER %(user)s" % {'user': user})
return True
# ===========================================
# Module execution.
#
def main():
module = AnsibleModule(
argument_spec=dict(
login_user=dict(default="postgres"),
login_password=dict(default=""),
login_host=dict(default=""),
user=dict(required=True, aliases=['name']),
password=dict(default=None),
state=dict(default="present", choices=["absent", "present"]),
db=dict(required=True),
)
)
user = module.params["user"]
password = module.params["password"]
state = module.params["state"]
db = module.params["db"]
if not postgresqldb_found:
module.fail_json(msg="the python psycopg2 module is required")
try:
db_connection = psycopg2.connect(host=module.params["login_host"],
user=module.params["login_user"],
password=module.params["login_password"],
database=db)
cursor = db_connection.cursor()
except Exception as e:
module.fail_json(msg="unable to connect to database: %s" % e)
if state == "present":
if user_exists(cursor, user):
changed = user_mod(cursor, user, password, db)
else:
if password is None:
msg = "password parameter required when adding a user"
module.fail_json(msg=msg)
changed = user_add(cursor, user, password, db)
elif state == "absent":
if user_exists(cursor, user):
changed = user_delete(cursor, user, db)
else:
changed = False
# Commit the database changes
db_connection.commit()
module.exit_json(changed=changed, user=user)
# this is magic, see lib/ansible/module_common.py
#<<INCLUDE_ANSIBLE_MODULE_COMMON>>
main()
|
