source: setup_db.py @ 148

Last change on this file since 148 was 148, checked in by pvkouteren, 11 years ago

Summary: Logging capabilities, better database setup, config file reading

setup_db.py: Always adds 'superuser' ibidas, when setup is done, the config file is adjusted so the database can't be cleared by accident. Script now also checks if database already exists and if so, prompts what to do.
postgres.py: Checks if views need to be used based on the user currently logged in. Does SQL logging if desired.
config.py: added shortcut method 'getValue'
IbidasLogger?.py: Logging (always present) that can be used by the code from now on.

ini files are set to some better defaults to prevent unwanted behaviour and some new settings added.

  • Property svn:mime-type set to text/plain
File size: 11.7 KB
Line 
1#!/usr/bin/env python
2"""
3This script will set up the database
4@author: Patrick van Kouteren <H.J.W.vanKouteren@student.tudelft.nl>
5@version: 1.1
6"""
7
8import config
9import container
10from container import postgres
11from psycopg2 import ProgrammingError, OperationalError
12import psycopg2
13import sys
14import time
15
16'''
17Setup database connection (we assume this is working).
18Note that we use the db 'template1' as this is a db ALWAYS present.
19We need a db to open a psycopg2 connection.
20'''
21_ConfigDefault = {
22    "setup.db_name":                "ibidas",
23    "setup.db_user":                "postgres",
24    "setup.db_user_pw":             "Put the postgres password here",
25    "setup.db_host":                "127.0.0.1",
26    "setup.db_port":                "5432",
27    "setup.authenticated_mode":     "yes",
28    "setup.setup_type":             "new"
29}
30
31setupconfig = config.loadConfig('setup.ini', _ConfigDefault)
32
33''' Extra security: Check if the database exists. If so, prompt if the user's sure to delete it '''
34
35try:
36    conn = psycopg2.connect("dbname='"+setupconfig['setup.db_name']+"' " \
37                               "user='"+setupconfig['setup.db_user']+"' "\
38                               "host='"+setupconfig['setup.db_host']+"' "\
39                               "port='"+setupconfig['setup.db_port']+"' "\
40                               "password='"+setupconfig['setup.db_user_pw']+"'");
41    conn.close()
42    db_already_exists = True
43except:
44    db_already_exists = False
45
46if (db_already_exists is True):
47    yn = ""
48    while(not (yn == "y" or yn == "n")):
49        yn = raw_input("The database you've specified in setup.ini already exist. By proceeding, you'll "\
50                       "completely clear and delete this existing database. Are you sure you want to do this? (y/n)\n")
51    if (yn == "n"):
52        print "=== Database setup script overview ==="
53        print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] User aborted script. Database was not overwritten."
54        print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Please supply your preferred data in setup.ini and run this setup again"
55        print "=== End of database setup script ==="
56        sys.exit()
57
58       
59''' If the database already exists, the user has decided to clear it. Let's do some work! '''
60
61try:
62    cd = postgres.openConnection(setupconfig['setup.db_host'],\
63                                 setupconfig['setup.db_port'],"template1",\
64                                 setupconfig['setup.db_user'],setupconfig['setup.db_user_pw'])
65except OperationalError:
66    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Could not open database connection." \
67          "Please check the setup.ini config file and run setup.py again"
68    sys.exit()
69
70
71''' Now we have an open database connection. Let's start! '''
72
73print "=== Database setup script ==="
74''' Obtain current isolation level '''           
75il = cd.isolation_level
76''' For dropping db, we need isolation level 0 '''
77cd.set_isolation_level(0)
78cursor = cd.getSingletonCursor()
79try:
80    cursor.execute("DROP DATABASE " + setupconfig['setup.db_name'] + ";")
81    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Dropping old database.."
82except ProgrammingError:
83    ''' The database did not exist '''
84    pass
85except OperationalError:
86    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: The database is accessed by a user. Please close all connections, including PgAdmin, and run this script again!"
87    sys.exit()
88
89
90''' Drop the user ibidas if it exists '''
91
92try:
93    cursor.execute("DROP USER ibidas;")
94    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Dropping user 'ibidas'.."
95except ProgrammingError:
96    ''' The database did not exist '''
97    pass
98#except OperationalError:
99#    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: The database is accessed by a user. Please close all connections, including PgAdmin, and run this script again!"
100#    sys.exit()
101
102   
103''' Create the database '''
104
105try:
106    cursor.execute("CREATE DATABASE " + setupconfig['setup.db_name'] + " WITH ENCODING 'UTF8';")
107    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Database " + setupconfig['setup.db_name'] + " created."
108except OperationalError:
109    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: The database is accessed by a user. Please close all connections, including PgAdmin, and run this script again!"
110    sys.exit()
111   
112cd.set_isolation_level(il)
113cd.close()
114
115
116''' Now that the database itself is created, connect and create the tables '''
117
118cd = postgres.openConnection(setupconfig['setup.db_host'],\
119            setupconfig['setup.db_port'],setupconfig['setup.db_name'],\
120            setupconfig['setup.db_user'],setupconfig['setup.db_user_pw'])
121cursor = cd.getSingletonCursor()
122
123print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Creating tables.."
124filename = "sql/preimport.sql" # The file which contains the sql to create the db schema
125file = open(filename, 'r')
126sql = file.read()
127
128''' Obtain current isolation level and save for later. We need level 0 for now. '''           
129il = cd.isolation_level
130cd.set_isolation_level(0)
131
132''' Execute the contents of the sql file '''
133try:
134    cursor.execute(sql)
135except ProgrammingError:
136    print "ProgrammingError!"
137    pass
138file.close()
139
140print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Tables created."
141
142'''
143We need a 'superuser', so let's create it.
144'''
145
146#if (setupconfig['setup.authenticated_mode'] == "no"):
147# create one 'superuser'
148ibidas_user_sql = "CREATE USER ibidas WITH PASSWORD 'ibidas' NOCREATEDB CREATEUSER CREATEROLE;"
149try:
150    cursor.execute(ibidas_user_sql)
151except ProgrammingError:
152    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Unable to create superuser ibidas. Please run this script again!"
153    sys.exit()
154
155''' Apply user restrictions on the tables '''
156print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Applying user restrictions.."
157try:
158    cursor.execute("REVOKE ALL ON DATABASE " + setupconfig['setup.db_name'] + " FROM PUBLIC")
159    cursor.execute("GRANT CONNECT ON DATABASE " + setupconfig['setup.db_name'] + " TO PUBLIC")
160except ProgrammingError:
161    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Error applying restrictions. Please run this script again!"
162    sys.exit()
163
164
165''' Create views and apply restrictions on them '''
166print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Creating views.."
167
168tables = cd._getTables()
169existing = []
170for table in tables:
171    try:
172        cursor.execute("SELECT source_id FROM " + table['name'] +";")
173        existing.append(table['name'])
174    except ProgrammingError:
175        pass
176
177
178'''
179The variable existing now contains all tables for which a view should be defined.
180These tables contain a link to set (by means of source_id) and so can be related to mydatasets_view
181'''
182
183# Define mydatasets_view
184mydatasets_view_sql = "CREATE VIEW mydatasets_view AS SELECT source_id FROM set_pg_group WHERE grosysid IN (SELECT grosysid FROM pg_group WHERE groname IN (SELECT applicable_roles.role_name FROM information_schema.applicable_roles WHERE applicable_roles.grantee::name = (SELECT \"current_user\"() as \"current_user\")));"
185mydatasets_rights_sql = "GRANT SELECT ON TABLE mydatasets_view TO PUBLIC; GRANT SELECT ON TABLE pg_group TO PUBLIC; GRANT SELECT ON TABLE information_schema.applicable_roles TO PUBLIC;"
186try:
187    cursor.execute(mydatasets_view_sql)
188except ProgrammingError:
189    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Unable to create the mydatsets_view. Please check the query and run this script again!"
190    sys.exit()
191 
192for viewtable in existing:
193    viewtable_view = viewtable + "_view"
194    if viewtable == 'set':
195        view_sql = "CREATE VIEW " + viewtable_view + " AS SELECT * FROM " + viewtable + " WHERE set_id IN (SELECT source_id FROM mydatasets_view);"
196    else:
197        view_sql = "CREATE VIEW " + viewtable_view + " AS SELECT * FROM " + viewtable + " WHERE source_id IN (SELECT * FROM mydatasets_view);"
198    rights_sql = "GRANT SELECT ON TABLE " + viewtable_view + " TO PUBLIC"
199    try:
200        cursor.execute(view_sql)
201        cursor.execute(rights_sql)
202    except ProgrammingError:
203        print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Oops: Unable to create the " + viewtable_view +". Please check the query and run this script again!"
204        sys.exit()
205print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Created " + str(len(existing) + 1) + " views.."
206
207
208''' Return to the normal isolation level '''
209cd.set_isolation_level(il)
210
211
212''' Create the config file based on the authenticated_mode variable '''
213
214print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Checking Ibidas operating mode.."
215
216if (setupconfig['setup.authenticated_mode'] == "yes"):
217    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Authenticated mode is ON. The " \
218          "configuration file 'ibidas.ini' will contain the login credentials of the setup " \
219          "configuration file. Please create one or more users and change these credentials " \
220          "for your own system's security purposes!"
221    _dbConfigDefault = {
222        "database.name":                setupconfig['setup.db_name'],
223        "database.user":                setupconfig['setup.db_user'],
224        "database.password":            setupconfig['setup.db_user_pw'],
225        "database.host":                setupconfig['setup.db_host'],
226        "database.port":                setupconfig['setup.db_port'],
227        "ibidas.authenticated_mode":    setupconfig['setup.authenticated_mode'],
228        "ibidas.query_logging":         'on',
229        "ibidas.log_level":             'DEBUG'
230    }
231    dbconfig = config.loadConfig('ibidas.ini', _dbConfigDefault)
232    config.writeConfig('ibidas.ini', _dbConfigDefault)
233elif (setupconfig['setup.authenticated_mode'] == "no"):
234    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Authenticated mode is OFF. The " \
235          "configuration file 'ibidas.ini' will contain a default superuser 'ibidas'. " \
236          "Please note that although ibidas operates quicker in this mode, it is intended only for " \
237          "instances containing openly available datasets!"
238    _dbConfigDefault = {
239        "database.name":                setupconfig['setup.db_name'],
240        "database.user":                'ibidas',
241        "database.password":            'ibidas',
242        "database.host":                setupconfig['setup.db_host'],
243        "database.port":                setupconfig['setup.db_port'],
244        "ibidas.authenticated_mode":    setupconfig['setup.authenticated_mode'],
245        "ibidas.query_logging":         'on',
246        "ibidas.log_level":             'DEBUG'
247    }
248    dbconfig = config.loadConfig('ibidas.ini', _dbConfigDefault)
249    config.writeConfig('ibidas.ini', _dbConfigDefault)
250else:
251    print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] No valid value for 'authenticated_mode' was " \
252          "found in the setup.ini file. Please supply a valid argument (yes / no) and run setup again."
253    sys.exit()
254
255''' Close connections '''
256   
257cursor.close()
258cd.close()
259
260''' Change the setup.ini setup_type variable '''
261print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Resetting setup.ini variable 'setup_type' to prevent clearing this database next time."
262setupconfig['setup.setup_type'] = 'none'
263config.writeConfig('setup.ini', setupconfig)
264
265print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] Setup_db script finished."
266print "["+ time.strftime("%H:%M:%S", time.localtime()) + "] We hope you'll enjoy using Ibidas. For support, please visit https://wiki.nbic.nl/index.php/Ibidas"
267print "=== End of database setup script ==="
268   
269'''
270End of script. We have:
271Dropped and created the database and filled it with tables and views
272Defined access rights and created the configuration file.
273'''
Note: See TracBrowser for help on using the repository browser.