-
Notifications
You must be signed in to change notification settings - Fork 2k
/
commands.py
144 lines (116 loc) · 5.26 KB
/
commands.py
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
import re
from ckan.lib.cli import CkanCommand
import logging
log = logging.getLogger(__name__)
read_only_user_sql = '''
-- revoke permissions for the new user
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT CREATE ON SCHEMA public TO "{ckanuser}";
GRANT USAGE ON SCHEMA public TO "{ckanuser}";
GRANT CREATE ON SCHEMA public TO "{writeuser}";
GRANT USAGE ON SCHEMA public TO "{writeuser}";
-- create new read only user
CREATE USER "{readonlyuser}" {with_password} NOSUPERUSER NOCREATEDB NOCREATEROLE LOGIN;
-- take connect permissions from main db
REVOKE CONNECT ON DATABASE "{maindb}" FROM "{readonlyuser}";
-- grant select permissions for read-only user
GRANT CONNECT ON DATABASE "{datastore}" TO "{readonlyuser}";
GRANT USAGE ON SCHEMA public TO "{readonlyuser}";
-- grant access to current tables and views
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "{readonlyuser}";
-- grant access to new tables and views by default
ALTER DEFAULT PRIVILEGES FOR USER "{ckanuser}" IN SCHEMA public
GRANT SELECT ON TABLES TO "{readonlyuser}";
'''
class SetupDatastoreCommand(CkanCommand):
'''Perform commands to set up the datastore.
Make sure that the datastore urls are set properly before you run these commands.
Usage::
paster datastore create-db <sql-user-user>
paster datastore create-read-only-user <sql-super-user>
Where:
<sql-super-user> is the name of a postgres user with sufficient
permissions to create new tables, users, and grant
and revoke new permissions. Typically, this would
be the "postgres" user.
'''
summary = __doc__.split('\n')[0]
usage = __doc__
def __init__(self, name):
super(SetupDatastoreCommand, self).__init__(name)
def command(self):
'''
Parse command line arguments and call appropriate method.
'''
if not self.args or self.args[0] in ['--help', '-h', 'help']:
print SetupDatastoreCommand.__doc__
return
cmd = self.args[0]
self._load_config()
self.db_write_url_parts = self._get_db_config('ckan.datastore.write_url')
self.db_read_url_parts = self._get_db_config('ckan.datastore.read_url')
self.db_ckan_url_parts = self._get_db_config('sqlalchemy.url')
assert self.db_write_url_parts['db_name'] == self.db_read_url_parts['db_name'], "write and read db should be the same"
if cmd == 'create-db':
if len(self.args) != 2:
print self.usage
return
self.sql_superuser = self.args[1]
self.create_db()
if self.verbose:
print 'Creating DB: SUCCESS'
elif cmd == 'create-read-only-user':
if len(self.args) != 2:
print self.usage
return
self.sql_superuser = self.args[1]
self.create_read_only_user()
if self.verbose:
print 'Creating read-only user: SUCCESS'
else:
print self.usage
log.error('Command "%s" not recognized' % (cmd,))
return
def _get_db_config(self, name):
from pylons import config
url = config[name]
# e.g. 'postgres://tester:pass@localhost/ckantest3'
db_details_match = re.match('^\s*(?P<db_type>\w*)://(?P<db_user>[^:]*):?(?P<db_pass>[^@]*)@(?P<db_host>[^/:]*):?(?P<db_port>[^/]*)/(?P<db_name>[\w.-]*)', url)
if not db_details_match:
raise Exception('Could not extract db details from url: %r' % url)
db_details = db_details_match.groupdict()
return db_details
def _run_cmd(self, command_line):
import subprocess
retcode = subprocess.call(command_line, shell=True)
if retcode != 0:
raise SystemError('Command exited with errorcode: %i' % retcode)
def _run_sql(self, sql, as_sql_user, database='postgres'):
if self.verbose:
print "Executing: \n#####\n", sql, "\n####\nOn database:", database
if not self.simulate:
self._run_cmd("psql --username='{username}' --dbname='{database}' -c \"{sql}\" -W".format(
username=as_sql_user,
database=database,
sql=sql.replace('"', '\\"')
))
def create_db(self):
sql = "create database {0}".format(self.db_write_url_parts['db_name'])
self._run_sql(sql, as_sql_user=self.sql_superuser)
def create_read_only_user(self):
password = self.db_read_url_parts['db_pass']
self.validate_password(password)
sql = read_only_user_sql.format(
maindb=self.db_ckan_url_parts['db_name'],
datastore=self.db_write_url_parts['db_name'],
ckanuser=self.db_ckan_url_parts['db_user'],
readonlyuser=self.db_read_url_parts['db_user'],
with_password="WITH PASSWORD '{0}'".format(password) if password else "",
writeuser=self.db_write_url_parts['db_user'])
self._run_sql(sql,
as_sql_user=self.sql_superuser,
database=self.db_write_url_parts['db_name'])
def validate_password(self, password):
if "'" in password:
raise ValueError("Passwords cannot contain '")