def create_app(app_name=None):
# Configuration settings
import config
if not app_name:
app_name = config.APP_NAME
# Check if app is created for CLI operations or Web
cli_mode = False
if app_name.endswith('-cli'):
cli_mode = True
# Only enable password related functionality in server mode.
if config.SERVER_MODE is True:
# Some times we need to access these config params where application
# context is not available (we can't use current_app.config in those
# cases even with current_app.app_context())
# So update these params in config itself.
# And also these updated config values will picked up by application
# since we are updating config before the application instance is
# created.
config.SECURITY_RECOVERABLE = True
config.SECURITY_CHANGEABLE = True
# Now we'll open change password page in alertify dialog
# we don't want it to redirect to main page after password
# change operation so we will open the same password change page again.
config.SECURITY_POST_CHANGE_VIEW = 'browser.change_password'
"""Create the Flask application, startup logging and dynamically load
additional modules (blueprints) that are found in this directory."""
app = PgAdmin(__name__, static_url_path='/static')
# Removes unwanted whitespace from render_template function
app.jinja_env.trim_blocks = True
app.config.from_object(config)
app.config.update(dict(PROPAGATE_EXCEPTIONS=True))
##########################################################################
# Setup logging and log the application startup
##########################################################################
# We won't care about errors in the logging system, we are more
# interested in application errors.
logging.raiseExceptions = False
# Add SQL level logging, and set the base logging level
logging.addLevelName(25, 'SQL')
app.logger.setLevel(logging.DEBUG)
app.logger.handlers = []
# We also need to update the handler on the webserver in order to see
# request. Setting the level prevents werkzeug from setting up it's own
# stream handler thus ensuring all the logging goes through the pgAdmin
# logger.
logger = logging.getLogger('werkzeug')
logger.setLevel(config.CONSOLE_LOG_LEVEL)
# Set SQLITE_PATH to TEST_SQLITE_PATH while running test cases
if ('PGADMIN_TESTING_MODE' in os.environ
and os.environ['PGADMIN_TESTING_MODE'] == '1'):
config.SQLITE_PATH = config.TEST_SQLITE_PATH
config.MASTER_PASSWORD_REQUIRED = False
config.UPGRADE_CHECK_ENABLED = False
if not cli_mode:
# Ensure the various working directories exist
from pgadmin.setup import create_app_data_directory
create_app_data_directory(config)
# File logging
fh = logging.FileHandler(config.LOG_FILE, encoding='utf-8')
fh.setLevel(config.FILE_LOG_LEVEL)
fh.setFormatter(logging.Formatter(config.FILE_LOG_FORMAT))
app.logger.addHandler(fh)
logger.addHandler(fh)
# Console logging
ch = logging.StreamHandler()
ch.setLevel(config.CONSOLE_LOG_LEVEL)
ch.setFormatter(logging.Formatter(config.CONSOLE_LOG_FORMAT))
app.logger.addHandler(ch)
logger.addHandler(ch)
# Log the startup
app.logger.info('########################################################')
app.logger.info('Starting %s v%s...', config.APP_NAME, config.APP_VERSION)
app.logger.info('########################################################')
app.logger.debug("Python syspath: %s", sys.path)
##########################################################################
# Setup i18n
##########################################################################
# Initialise i18n
babel = Babel(app)
app.logger.debug('Available translations: %s' % babel.list_translations())
@babel.localeselector
def get_locale():
"""Get the language for the user."""
language = 'en'
if config.SERVER_MODE is False:
# Get the user language preference from the miscellaneous module
if current_user.is_authenticated:
user_id = current_user.id
else:
user = user_datastore.get_user(config.DESKTOP_USER)
if user is not None:
user_id = user.id
user_language = Preferences.raw_value('misc', 'user_language',
'user_language', user_id)
if user_language is not None:
language = user_language
else:
# If language is available in get request then return the same
# otherwise check the session or cookie
data = request.form
if 'language' in data:
language = data['language'] or language
setattr(session, 'PGADMIN_LANGUAGE', language)
elif hasattr(session, 'PGADMIN_LANGUAGE'):
language = getattr(session, 'PGADMIN_LANGUAGE', language)
elif hasattr(request.cookies, 'PGADMIN_LANGUAGE'):
language = getattr(request.cookies, 'PGADMIN_LANGUAGE',
language)
return language
##########################################################################
# Setup authentication
##########################################################################
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///{0}?timeout={1}' \
.format(config.SQLITE_PATH.replace('\\', '/'),
getattr(config, 'SQLITE_TIMEOUT', 500)
)
# Override USER_DOES_NOT_EXIST and INVALID_PASSWORD messages from flask.
app.config['SECURITY_MSG_USER_DOES_NOT_EXIST'] = \
app.config['SECURITY_MSG_INVALID_PASSWORD'] = \
(gettext("Incorrect username or password."), "error")
# Create database connection object and mailer
db.init_app(app)
##########################################################################
# Upgrade the schema (if required)
##########################################################################
with app.app_context():
# Run migration for the first time i.e. create database
from config import SQLITE_PATH
from pgadmin.setup import db_upgrade
# If version not available, user must have aborted. Tables are not
# created and so its an empty db
if not os.path.exists(SQLITE_PATH) or get_version() == -1:
# If running in cli mode then don't try to upgrade, just raise
# the exception
if not cli_mode:
db_upgrade(app)
else:
if not os.path.exists(SQLITE_PATH):
raise FileNotFoundError('SQLite database file "' +
SQLITE_PATH + '" does not exists.')
raise RuntimeError('Specified SQLite database file '
'is not valid.')
else:
schema_version = get_version()
# Run migration if current schema version is greater than the
# schema version stored in version table
if CURRENT_SCHEMA_VERSION >= schema_version:
db_upgrade(app)
# Update schema version to the latest
if CURRENT_SCHEMA_VERSION > schema_version:
set_version(CURRENT_SCHEMA_VERSION)
db.session.commit()
if os.name != 'nt':
os.chmod(config.SQLITE_PATH, 0o600)
Mail(app)
# Don't bother paths when running in cli mode
if not cli_mode:
import pgadmin.utils.paths as paths
paths.init_app(app)
# Setup Flask-Security
user_datastore = SQLAlchemyUserDatastore(db, User, Role)
security = Security(None, user_datastore)
##########################################################################
# Setup security
##########################################################################
with app.app_context():
config.CSRF_SESSION_KEY = Keys.query.filter_by(
name='CSRF_SESSION_KEY').first().value
config.SECRET_KEY = Keys.query.filter_by(
name='SECRET_KEY').first().value
config.SECURITY_PASSWORD_SALT = Keys.query.filter_by(
name='SECURITY_PASSWORD_SALT').first().value
# Update the app.config with proper security keyes for signing CSRF data,
# signing cookies, and the SALT for hashing the passwords.
app.config.update(
dict({
'CSRF_SESSION_KEY': config.CSRF_SESSION_KEY,
'SECRET_KEY': config.SECRET_KEY,
'SECURITY_PASSWORD_SALT': config.SECURITY_PASSWORD_SALT,
'SESSION_COOKIE_DOMAIN': config.SESSION_COOKIE_DOMAIN,
# CSRF Token expiration till session expires
'WTF_CSRF_TIME_LIMIT': getattr(config, 'CSRF_TIME_LIMIT', None),
'WTF_CSRF_METHODS': ['GET', 'POST', 'PUT', 'DELETE'],
}))
security.init_app(app, user_datastore)
# register custom unauthorised handler.
app.login_manager.unauthorized_handler(pga_unauthorised)
# Set the permanent session lifetime to the specified value in config file.
app.permanent_session_lifetime = timedelta(
days=config.SESSION_EXPIRATION_TIME)
if not cli_mode:
app.session_interface = create_session_interface(
app, config.SESSION_SKIP_PATHS)
# Make the Session more secure against XSS & CSRF when running in web mode
if config.SERVER_MODE and config.ENHANCED_COOKIE_PROTECTION:
paranoid = Paranoid(app)
paranoid.redirect_view = 'browser.index'
##########################################################################
# Load all available server drivers
##########################################################################
driver.init_app(app)
authenticate.init_app(app)
##########################################################################
# Register language to the preferences after login
##########################################################################
@user_logged_in.connect_via(app)
def register_language(sender, user):
# After logged in, set the language in the preferences if we get from
# the login page
data = request.form
if 'language' in data:
language = data['language']
# Set the user language preference
misc_preference = Preferences.module('misc')
user_languages = misc_preference.preference('user_language')
if user_languages and language:
language = user_languages.set(language)
##########################################################################
# Register any local servers we can discover
##########################################################################
@user_logged_in.connect_via(app)
def on_user_logged_in(sender, user):
# Keep hold of the user ID
user_id = user.id
# Get the first server group for the user
servergroup_id = 1
servergroups = ServerGroup.query.filter_by(
user_id=user_id).order_by("id")
if servergroups.count() > 0:
servergroup = servergroups.first()
servergroup_id = servergroup.id
'''Add a server to the config database'''
def add_server(user_id, servergroup_id, name, superuser, port,
discovery_id, comment):
# Create a server object if needed, and store it.
servers = Server.query.filter_by(
user_id=user_id, discovery_id=svr_discovery_id).order_by("id")
if servers.count() > 0:
return
svr = Server(user_id=user_id,
servergroup_id=servergroup_id,
name=name,
host='localhost',
port=port,
maintenance_db='postgres',
username=superuser,
ssl_mode='prefer',
comment=svr_comment,
discovery_id=discovery_id)
db.session.add(svr)
db.session.commit()
# Figure out what servers are present
if winreg is not None:
arch_keys = set()
proc_arch = os.environ['PROCESSOR_ARCHITECTURE'].lower()
try:
proc_arch64 = os.environ['PROCESSOR_ARCHITEW6432'].lower()
except Exception:
proc_arch64 = None
if proc_arch == 'x86' and not proc_arch64:
arch_keys.add(0)
elif proc_arch == 'x86' or proc_arch == 'amd64':
arch_keys.add(winreg.KEY_WOW64_32KEY)
arch_keys.add(winreg.KEY_WOW64_64KEY)
for arch_key in arch_keys:
for server_type in ('PostgreSQL', 'EnterpriseDB'):
try:
root_key = winreg.OpenKey(
winreg.HKEY_LOCAL_MACHINE,
"SOFTWARE\\" + server_type + "\\Services", 0,
winreg.KEY_READ | arch_key)
for i in range(0, winreg.QueryInfoKey(root_key)[0]):
inst_id = winreg.EnumKey(root_key, i)
inst_key = winreg.OpenKey(root_key, inst_id)
svr_name = winreg.QueryValueEx(
inst_key, 'Display Name')[0]
svr_superuser = winreg.QueryValueEx(
inst_key, 'Database Superuser')[0]
svr_port = winreg.QueryValueEx(inst_key, 'Port')[0]
svr_discovery_id = inst_id
svr_comment = gettext(
"Auto-detected {0} installation with the data "
"directory at {1}").format(
winreg.QueryValueEx(
inst_key, 'Display Name')[0],
winreg.QueryValueEx(
inst_key, 'Data Directory')[0])
add_server(user_id, servergroup_id, svr_name,
svr_superuser, svr_port,
svr_discovery_id, svr_comment)
inst_key.Close()
except Exception:
pass
else:
# We use the postgres-winreg.ini file on non-Windows
from configparser import ConfigParser
registry = ConfigParser()
try:
registry.read('/etc/postgres-reg.ini')
sections = registry.sections()
# Loop the sections, and get the data from any that are PG or PPAS
for section in sections:
if (section.startswith('PostgreSQL/')
or section.startswith('EnterpriseDB/')):
svr_name = registry.get(section, 'Description')
svr_superuser = registry.get(section, 'Superuser')
# getint function throws exception if value is blank.
# Ex: Port=
# In such case we should handle the exception and continue
# to read the next section of the config file.
try:
svr_port = registry.getint(section, 'Port')
except ValueError:
continue
svr_discovery_id = section
description = registry.get(section, 'Description')
data_directory = registry.get(section, 'DataDirectory')
svr_comment = gettext(
"Auto-detected {0} installation "
"with the data directory at {1}").format(
description, data_directory)
add_server(user_id, servergroup_id, svr_name,
svr_superuser, svr_port, svr_discovery_id,
svr_comment)
except Exception:
pass
@user_logged_in.connect_via(app)
@user_logged_out.connect_via(app)
def force_session_write(app, user):
session.force_write = True
@user_logged_in.connect_via(app)
def store_crypt_key(app, user):
# in desktop mode, master password is used to encrypt/decrypt
# and is stored in the keyManager memory
if config.SERVER_MODE and 'password' in request.form:
current_app.keyManager.set(request.form['password'])
@user_logged_out.connect_via(app)
def current_user_cleanup(app, user):
from config import PG_DEFAULT_DRIVER
from pgadmin.utils.driver import get_driver
from flask import current_app
# remove key
current_app.keyManager.reset()
for mdl in current_app.logout_hooks:
try:
mdl.on_logout(user)
except Exception as e:
current_app.logger.exception(e)
_driver = get_driver(PG_DEFAULT_DRIVER)
_driver.gc_own()
##########################################################################
# Load plugin modules
##########################################################################
for module in app.find_submodules('pgadmin'):
app.logger.info('Registering blueprint module: %s' % module)
app.register_blueprint(module)
app.register_logout_hook(module)
@app.before_request
def limit_host_addr():
"""
This function validate the hosts from ALLOWED_HOSTS before allowing
HTTP request to avoid Host Header Injection attack
:return: None/JSON response with 403 HTTP status code
"""
client_host = str(request.host).split(':')[0]
valid = True
allowed_hosts = config.ALLOWED_HOSTS
if len(allowed_hosts) != 0:
regex = re.compile(
r'\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}(?:/\d{1,2}|)')
# Create separate list for ip addresses and host names
ip_set = list(filter(lambda ip: regex.match(ip), allowed_hosts))
host_set = list(
filter(lambda ip: not regex.match(ip), allowed_hosts))
is_ip = regex.match(client_host)
if is_ip:
ip_address = []
for ip in ip_set:
ip_address.extend(list(ipaddress.ip_network(ip)))
valid = ip_address.__contains__(
ipaddress.ip_address(client_host))
else:
valid = host_set.__contains__(client_host)
if not valid:
return make_json_response(status=403,
success=0,
errormsg=_("403 FORBIDDEN"))
##########################################################################
# Handle the desktop login
##########################################################################
@app.before_request
def before_request():
"""Login the default user if running in desktop mode"""
# Check the auth key is valid, if it's set, and we're not in server
# mode, and it's not a help file request.
if not config.SERVER_MODE and app.PGADMIN_INT_KEY != '' and (
('key' not in request.args
or request.args['key'] != app.PGADMIN_INT_KEY) and
request.cookies.get('PGADMIN_INT_KEY') != app.PGADMIN_INT_KEY
and request.endpoint != 'help.static'):
abort(401)
if not config.SERVER_MODE and not current_user.is_authenticated:
user = user_datastore.get_user(config.DESKTOP_USER)
# Throw an error if we failed to find the desktop user, to give
# the sysadmin a hint. We'll continue to try to login anyway as
# that'll through a nice 500 error for us.
if user is None:
app.logger.error(
'The desktop user %s was not found in the configuration '
'database.' % config.DESKTOP_USER)
abort(401)
login_user(user)
# if the server is restarted the in memory key will be lost
# but the user session may still be active. Logout the user
# to get the key again when login
if config.SERVER_MODE and current_user.is_authenticated and \
current_app.keyManager.get() is None and \
request.endpoint not in ('security.login', 'security.logout'):
logout_user()
@app.after_request
def after_request(response):
if 'key' in request.args:
domain = dict()
if config.COOKIE_DEFAULT_DOMAIN and \
config.COOKIE_DEFAULT_DOMAIN != 'localhost':
domain['domain'] = config.COOKIE_DEFAULT_DOMAIN
response.set_cookie('PGADMIN_INT_KEY',
value=request.args['key'],
path=config.COOKIE_DEFAULT_PATH,
secure=config.SESSION_COOKIE_SECURE,
httponly=config.SESSION_COOKIE_HTTPONLY,
samesite=config.SESSION_COOKIE_SAMESITE,
**domain)
SecurityHeaders.set_response_headers(response)
return response
##########################################################################
# Cache busting
##########################################################################
# Version number to be added to all static file url requests
# This is used by url_for function when generating urls
# This will solve caching issues when application is upgrading
# This is called - Cache Busting
@app.url_defaults
def add_internal_version(endpoint, values):
extensions = config.APP_VERSION_EXTN
# Add the internal version only if it is set
if config.APP_VERSION_PARAM is not None and \
config.APP_VERSION_PARAM != '':
# If there is a filename, add the version
if 'filename' in values \
and values['filename'].endswith(extensions):
values[config.APP_VERSION_PARAM] = config.APP_VERSION_INT
else:
# Sometimes there may be direct endpoint for some files
# There will be only one rule for such endpoints
urls = [url for url in app.url_map.iter_rules(endpoint)]
if len(urls) == 1 and urls[0].rule.endswith(extensions):
values[config.APP_VERSION_PARAM] = \
config.APP_VERSION_INT
# Strip away internal version param before sending further to app as it was
# required for cache busting only
@app.url_value_preprocessor
def strip_version_number(endpoint, values):
if values and config.APP_VERSION_PARAM in values:
values.pop(config.APP_VERSION_PARAM)
##########################################################################
# Minify output. Not required in desktop mode
##########################################################################
if not config.DEBUG and config.SERVER_MODE:
from flask_compress import Compress
Compress(app)
from pgadmin.misc.themes import themes
themes(app)
@app.context_processor
def inject_blueprint():
"""
Inject a reference to the current blueprint, if any.
"""
return {
'current_app': current_app,
'current_blueprint': current_blueprint,
}
@app.errorhandler(Exception)
def all_exception_handler(e):
current_app.logger.error(e, exc_info=True)
return internal_server_error(errormsg=str(e))
# Exclude HTTPexception from above handler (all_exception_handler)
# HTTPException are user defined exceptions and those should be returned
# as is
@app.errorhandler(HTTPException)
def http_exception_handler(e):
current_app.logger.error(e, exc_info=True)
return e
# Intialize the key manager
app.keyManager = KeyManager()
##########################################################################
# Protection against CSRF attacks
##########################################################################
with app.app_context():
pgCSRFProtect.init_app(app)
##########################################################################
# All done!
##########################################################################
return app
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
if get_version() != -1:
return
op.create_table('version',
sa.Column('name', sa.String(length=32), nullable=False),
sa.Column('value', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('name'))
op.create_table(
'user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(length=256), nullable=False),
sa.Column('password', sa.String(length=256), nullable=True),
sa.Column('active', sa.Boolean(), nullable=False),
sa.Column('confirmed_at', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('email'))
op.create_table(
'role', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('description', sa.String(length=256), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
op.create_table(
'setting', sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('setting', sa.String(length=256), nullable=False),
sa.Column('value', sa.String(length=1024), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('user_id', 'setting'))
op.create_table('roles_users',
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('role_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['role_id'],
['role.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
))
op.create_table('servergroup', sa.Column('id',
sa.Integer(),
nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('user_id', 'name'))
op.create_table(
'server', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('servergroup_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('host', sa.String(length=128), nullable=False),
sa.Column('port', sa.Integer(), nullable=False),
sa.Column('maintenance_db', sa.String(length=64), nullable=False),
sa.Column('username', sa.String(length=64), nullable=False),
sa.Column('ssl_mode', sa.String(length=16), nullable=False),
sa.ForeignKeyConstraint(
['servergroup_id'],
['servergroup.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
db.engine.execute("""
INSERT INTO "role"
VALUES(1, 'Administrators', 'pgAdmin Administrators Role')
""")
db.engine.execute("""
INSERT INTO "roles_users"
VALUES(1, 1);
""")
db.engine.execute("""
INSERT INTO "servergroup"
VALUES(1, 1, 'Servers')
""")
email, password = user_info()
current_salt = getattr(config, 'SECURITY_PASSWORD_SALT',
base64.urlsafe_b64encode(os.urandom(32)).decode())
if current_app.extensions.get('security') is None:
current_app.config['SECURITY_PASSWORD_SALT'] = current_salt
user_datastore = SQLAlchemyUserDatastore(db, User, Role)
Security(current_app, user_datastore, register_blueprint=False)
else:
current_app.config['SECURITY_PASSWORD_SALT'] = current_salt
setattr(config, 'SECURITY_PASSWORD_SALT', current_salt)
password = encrypt_password(password)
db.engine.execute("""
INSERT INTO "user"
VALUES(1, '%s',
'%s',
1, NULL)
""" % (email, password))
db.engine.execute("""
INSERT INTO "version"
VALUES('ConfigDB', 2);
""")
def create_app(app_name=None):
# Configuration settings
import config
if not app_name:
app_name = config.APP_NAME
# Only enable password related functionality in server mode.
if config.SERVER_MODE is True:
# Some times we need to access these config params where application
# context is not available (we can't use current_app.config in those
# cases even with current_app.app_context())
# So update these params in config itself.
# And also these updated config values will picked up by application
# since we are updating config before the application instance is
# created.
config.SECURITY_RECOVERABLE = True
config.SECURITY_CHANGEABLE = True
# Now we'll open change password page in alertify dialog
# we don't want it to redirect to main page after password
# change operation so we will open the same password change page again.
config.SECURITY_POST_CHANGE_VIEW = 'browser.change_password'
"""Create the Flask application, startup logging and dynamically load
additional modules (blueprints) that are found in this directory."""
app = PgAdmin(__name__, static_url_path='/static')
# Removes unwanted whitespace from render_template function
app.jinja_env.trim_blocks = True
app.config.from_object(config)
app.config.update(dict(PROPAGATE_EXCEPTIONS=True))
##########################################################################
# Setup logging and log the application startup
##########################################################################
# Add SQL level logging, and set the base logging level
logging.addLevelName(25, 'SQL')
app.logger.setLevel(logging.DEBUG)
app.logger.handlers = []
# We also need to update the handler on the webserver in order to see
# request. Setting the level prevents werkzeug from setting up it's own
# stream handler thus ensuring all the logging goes through the pgAdmin
# logger.
logger = logging.getLogger('werkzeug')
logger.setLevel(logging.INFO)
# Set SQLITE_PATH to TEST_SQLITE_PATH while running test cases
if ('PGADMIN_TESTING_MODE' in os.environ
and os.environ['PGADMIN_TESTING_MODE'] == '1'):
config.SQLITE_PATH = config.TEST_SQLITE_PATH
# Ensure the various working directories exist
from pgadmin.setup import create_app_data_directory, db_upgrade
create_app_data_directory(config)
# File logging
fh = logging.FileHandler(config.LOG_FILE, encoding='utf-8')
fh.setLevel(config.FILE_LOG_LEVEL)
fh.setFormatter(logging.Formatter(config.FILE_LOG_FORMAT))
app.logger.addHandler(fh)
logger.addHandler(fh)
# Console logging
ch = logging.StreamHandler()
ch.setLevel(config.CONSOLE_LOG_LEVEL)
ch.setFormatter(logging.Formatter(config.CONSOLE_LOG_FORMAT))
app.logger.addHandler(ch)
logger.addHandler(ch)
# Log the startup
app.logger.info('########################################################')
app.logger.info('Starting %s v%s...', config.APP_NAME, config.APP_VERSION)
app.logger.info('########################################################')
app.logger.debug("Python syspath: %s", sys.path)
##########################################################################
# Setup i18n
##########################################################################
# Initialise i18n
babel = Babel(app)
app.logger.debug('Available translations: %s' % babel.list_translations())
@babel.localeselector
def get_locale():
"""Get the language for the user."""
language = 'en'
if config.SERVER_MODE is False:
# Get the user language preference from the miscellaneous module
if current_user.is_authenticated:
user_id = current_user.id
else:
user = user_datastore.get_user(config.DESKTOP_USER)
if user is not None:
user_id = user.id
user_language = Preferences.raw_value('miscellaneous',
'user_language', None,
user_id)
if user_language is not None:
language = user_language
else:
# If language is available in get request then return the same
# otherwise check the session or cookie
data = request.form
if 'language' in data:
language = data['language'] or language
setattr(session, 'PGADMIN_LANGUAGE', language)
elif hasattr(session, 'PGADMIN_LANGUAGE'):
language = getattr(session, 'PGADMIN_LANGUAGE', language)
elif hasattr(request.cookies, 'PGADMIN_LANGUAGE'):
language = getattr(request.cookies, 'PGADMIN_LANGUAGE',
language)
return language
##########################################################################
# Setup authentication
##########################################################################
app.config['SQLALCHEMY_DATABASE_URI'] = u'sqlite:///{0}?timeout={1}' \
.format(config.SQLITE_PATH.replace(u'\\', u'/'),
getattr(config, 'SQLITE_TIMEOUT', 500)
)
# Create database connection object and mailer
db.init_app(app)
##########################################################################
# Upgrade the schema (if required)
##########################################################################
with app.app_context():
# Run migration for the first time i.e. create database
from config import SQLITE_PATH
# If version not available, user must have aborted. Tables are not
# created and so its an empty db
if not os.path.exists(SQLITE_PATH) or get_version() == -1:
db_upgrade(app)
else:
schema_version = get_version()
# Run migration if current schema version is greater than the
# schema version stored in version table
if CURRENT_SCHEMA_VERSION >= schema_version:
db_upgrade(app)
# Update schema version to the latest
if CURRENT_SCHEMA_VERSION > schema_version:
set_version(CURRENT_SCHEMA_VERSION)
db.session.commit()
Mail(app)
import pgadmin.utils.paths as paths
paths.init_app(app)
# Setup Flask-Security
user_datastore = SQLAlchemyUserDatastore(db, User, Role)
security = Security(None, user_datastore)
##########################################################################
# Setup security
##########################################################################
with app.app_context():
config.CSRF_SESSION_KEY = Keys.query.filter_by(
name='CSRF_SESSION_KEY').first().value
config.SECRET_KEY = Keys.query.filter_by(
name='SECRET_KEY').first().value
config.SECURITY_PASSWORD_SALT = Keys.query.filter_by(
name='SECURITY_PASSWORD_SALT').first().value
# Update the app.config with proper security keyes for signing CSRF data,
# signing cookies, and the SALT for hashing the passwords.
app.config.update(
dict({
'CSRF_SESSION_KEY': config.CSRF_SESSION_KEY,
'SECRET_KEY': config.SECRET_KEY,
'SECURITY_PASSWORD_SALT': config.SECURITY_PASSWORD_SALT,
'SESSION_COOKIE_DOMAIN': config.SESSION_COOKIE_DOMAIN
}))
security.init_app(app, user_datastore)
# register custom unauthorised handler.
app.login_manager.unauthorized_handler(pga_unauthorised)
# Set the permanent session lifetime to the specified value in config file.
app.permanent_session_lifetime = timedelta(
days=config.SESSION_EXPIRATION_TIME)
app.session_interface = create_session_interface(app,
config.SESSION_SKIP_PATHS)
# Make the Session more secure against XSS & CSRF when running in web mode
if config.SERVER_MODE:
paranoid = Paranoid(app)
paranoid.redirect_view = 'browser.index'
##########################################################################
# Load all available server drivers
##########################################################################
driver.init_app(app)
##########################################################################
# Register language to the preferences after login
##########################################################################
@user_logged_in.connect_via(app)
def register_language(sender, user):
# After logged in, set the language in the preferences if we get from
# the login page
data = request.form
if 'language' in data:
language = data['language']
# Set the user language preference
misc_preference = Preferences.module('miscellaneous')
user_languages = misc_preference.preference('user_language')
if user_languages and language:
language = user_languages.set(language)
##########################################################################
# Register any local servers we can discover
##########################################################################
@user_logged_in.connect_via(app)
def on_user_logged_in(sender, user):
# Keep hold of the user ID
user_id = user.id
# Get the first server group for the user
servergroup_id = 1
servergroups = ServerGroup.query.filter_by(
user_id=user_id).order_by("id")
if servergroups.count() > 0:
servergroup = servergroups.first()
servergroup_id = servergroup.id
'''Add a server to the config database'''
def add_server(user_id, servergroup_id, name, superuser, port,
discovery_id, comment):
# Create a server object if needed, and store it.
servers = Server.query.filter_by(
user_id=user_id, discovery_id=svr_discovery_id).order_by("id")
if servers.count() > 0:
return
svr = Server(user_id=user_id,
servergroup_id=servergroup_id,
name=name,
host='localhost',
port=port,
maintenance_db='postgres',
username=superuser,
ssl_mode='prefer',
comment=svr_comment,
discovery_id=discovery_id)
db.session.add(svr)
db.session.commit()
# Figure out what servers are present
if winreg is not None:
arch_keys = set()
proc_arch = os.environ['PROCESSOR_ARCHITECTURE'].lower()
try:
proc_arch64 = os.environ['PROCESSOR_ARCHITEW6432'].lower()
except Exception as e:
proc_arch64 = None
if proc_arch == 'x86' and not proc_arch64:
arch_keys.add(0)
elif proc_arch == 'x86' or proc_arch == 'amd64':
arch_keys.add(winreg.KEY_WOW64_32KEY)
arch_keys.add(winreg.KEY_WOW64_64KEY)
for arch_key in arch_keys:
for server_type in ('PostgreSQL', 'EnterpriseDB'):
try:
root_key = winreg.OpenKey(
winreg.HKEY_LOCAL_MACHINE,
"SOFTWARE\\" + server_type + "\Services", 0,
winreg.KEY_READ | arch_key)
for i in xrange(0, winreg.QueryInfoKey(root_key)[0]):
inst_id = winreg.EnumKey(root_key, i)
inst_key = winreg.OpenKey(root_key, inst_id)
svr_name = winreg.QueryValueEx(
inst_key, 'Display Name')[0]
svr_superuser = winreg.QueryValueEx(
inst_key, 'Database Superuser')[0]
svr_port = winreg.QueryValueEx(inst_key, 'Port')[0]
svr_discovery_id = inst_id
svr_comment = gettext(
"Auto-detected %s installation with the data "
"directory at %s" %
(winreg.QueryValueEx(inst_key,
'Display Name')[0],
winreg.QueryValueEx(inst_key,
'Data Directory')[0]))
add_server(user_id, servergroup_id, svr_name,
svr_superuser, svr_port,
svr_discovery_id, svr_comment)
inst_key.Close()
except Exception as e:
pass
else:
# We use the postgres-winreg.ini file on non-Windows
try:
from configparser import ConfigParser
except ImportError:
from ConfigParser import ConfigParser # Python 2
registry = ConfigParser()
try:
registry.read('/etc/postgres-reg.ini')
sections = registry.sections()
# Loop the sections, and get the data from any that are PG or PPAS
for section in sections:
if (section.startswith('PostgreSQL/')
or section.startswith('EnterpriseDB/')):
svr_name = registry.get(section, 'Description')
svr_superuser = registry.get(section, 'Superuser')
# getint function throws exception if value is blank.
# Ex: Port=
# In such case we should handle the exception and continue
# to read the next section of the config file.
try:
svr_port = registry.getint(section, 'Port')
except ValueError:
continue
svr_discovery_id = section
description = registry.get(section, 'Description')
data_directory = registry.get(section, 'DataDirectory')
if hasattr(str, 'decode'):
description = description.decode('utf-8')
data_directory = data_directory.decode('utf-8')
svr_comment = gettext(u"Auto-detected %s installation "
u"with the data directory at %s" %
(description, data_directory))
add_server(user_id, servergroup_id, svr_name,
svr_superuser, svr_port, svr_discovery_id,
svr_comment)
except Exception as e:
pass
@user_logged_in.connect_via(app)
@user_logged_out.connect_via(app)
def force_session_write(app, user):
session.force_write = True
@user_logged_out.connect_via(app)
def clear_current_user_connections(app, user):
from config import PG_DEFAULT_DRIVER
from pgadmin.utils.driver import get_driver
_driver = get_driver(PG_DEFAULT_DRIVER)
_driver.gc_own()
##########################################################################
# Load plugin modules
##########################################################################
for module in app.find_submodules('pgadmin'):
app.logger.info('Registering blueprint module: %s' % module)
app.register_blueprint(module)
##########################################################################
# Handle the desktop login
##########################################################################
@app.before_request
def before_request():
"""Login the default user if running in desktop mode"""
# Check the auth key is valid, if it's set, and we're not in server
# mode, and it's not a help file request.
if not config.SERVER_MODE and app.PGADMIN_KEY != '':
if (('key' not in request.args
or request.args['key'] != app.PGADMIN_KEY)
and request.cookies.get('PGADMIN_KEY') != app.PGADMIN_KEY
and request.endpoint != 'help.static'):
abort(401)
if not config.SERVER_MODE and not current_user.is_authenticated:
user = user_datastore.get_user(config.DESKTOP_USER)
# Throw an error if we failed to find the desktop user, to give
# the sysadmin a hint. We'll continue to try to login anyway as
# that'll through a nice 500 error for us.
if user is None:
app.logger.error(
'The desktop user %s was not found in the configuration '
'database.' % config.DESKTOP_USER)
abort(401)
login_user(user)
@app.after_request
def after_request(response):
if 'key' in request.args:
domain = dict()
if config.COOKIE_DEFAULT_DOMAIN and \
config.COOKIE_DEFAULT_DOMAIN != 'localhost':
domain['domain'] = config.COOKIE_DEFAULT_DOMAIN
response.set_cookie('PGADMIN_KEY',
value=request.args['key'],
path=config.COOKIE_DEFAULT_PATH,
**domain)
return response
##########################################################################
# Cache busting
##########################################################################
# Version number to be added to all static file url requests
# This is used by url_for function when generating urls
# This will solve caching issues when application is upgrading
# This is called - Cache Busting
@app.url_defaults
def add_internal_version(endpoint, values):
extensions = config.APP_VERSION_EXTN
# Add the internal version only if it is set
if config.APP_VERSION_PARAM is not None and \
config.APP_VERSION_PARAM != '':
# If there is a filename, add the version
if 'filename' in values \
and values['filename'].endswith(extensions):
values[config.APP_VERSION_PARAM] = config.APP_VERSION_INT
else:
# Sometimes there may be direct endpoint for some files
# There will be only one rule for such endpoints
urls = [url for url in app.url_map.iter_rules(endpoint)]
if len(urls) == 1 and urls[0].rule.endswith(extensions):
values[config.APP_VERSION_PARAM] = \
config.APP_VERSION_INT
# Strip away internal version param before sending further to app as it was
# required for cache busting only
@app.url_value_preprocessor
def strip_version_number(endpoint, values):
if values and config.APP_VERSION_PARAM in values:
values.pop(config.APP_VERSION_PARAM)
##########################################################################
# Minify output
##########################################################################
# HTMLMIN doesn't work with Python 2.6.
if not config.DEBUG and sys.version_info >= (2, 7):
from flask_htmlmin import HTMLMIN
HTMLMIN(app)
@app.context_processor
def inject_blueprint():
"""Inject a reference to the current blueprint, if any."""
return {
'current_app': current_app,
'current_blueprint': current_blueprint
}
##########################################################################
# All done!
##########################################################################
return app
def upgrade():
version = get_version()
# Changes introduced in schema version 2
if version < 2:
# Create the 'server' table
db.metadata.create_all(db.engine, tables=[Server.__table__])
if version < 3:
db.engine.execute('ALTER TABLE server ADD COLUMN comment TEXT(1024)')
if version < 4:
db.engine.execute('ALTER TABLE server ADD COLUMN password TEXT(64)')
if version < 5:
db.engine.execute('ALTER TABLE server ADD COLUMN role text(64)')
if version < 6:
db.engine.execute("ALTER TABLE server RENAME TO server_old")
db.engine.execute("""
CREATE TABLE server (
id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
servergroup_id INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
host VARCHAR(128) NOT NULL,
port INTEGER NOT NULL CHECK (port >= 1024 AND port <= 65534),
maintenance_db VARCHAR(64) NOT NULL,
username VARCHAR(64) NOT NULL,
ssl_mode VARCHAR(16) NOT NULL CHECK (
ssl_mode IN (
'allow', 'prefer', 'require', 'disable', 'verify-ca', 'verify-full'
)),
comment VARCHAR(1024), password TEXT(64), role text(64),
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES user (id),
FOREIGN KEY(servergroup_id) REFERENCES servergroup (id)
)""")
db.engine.execute("""
INSERT INTO server (
id, user_id, servergroup_id, name, host, port, maintenance_db, username,
ssl_mode, comment, password, role
) SELECT
id, user_id, servergroup_id, name, host, port, maintenance_db, username,
ssl_mode, comment, password, role
FROM server_old""")
db.engine.execute("DROP TABLE server_old")
if version < 8:
db.engine.execute("""
CREATE TABLE module_preference(
id INTEGER PRIMARY KEY,
name VARCHAR(256) NOT NULL
)""")
db.engine.execute("""
CREATE TABLE preference_category(
id INTEGER PRIMARY KEY,
mid INTEGER,
name VARCHAR(256) NOT NULL,
FOREIGN KEY(mid) REFERENCES module_preference(id)
)""")
db.engine.execute("""
CREATE TABLE preferences (
id INTEGER PRIMARY KEY,
cid INTEGER NOT NULL,
name VARCHAR(256) NOT NULL,
FOREIGN KEY(cid) REFERENCES preference_category (id)
)""")
db.engine.execute("""
CREATE TABLE user_preferences (
pid INTEGER,
uid INTEGER,
value VARCHAR(1024) NOT NULL,
PRIMARY KEY (pid, uid),
FOREIGN KEY(pid) REFERENCES preferences (pid),
FOREIGN KEY(uid) REFERENCES user (id)
)""")
if version < 9:
db.engine.execute("""
CREATE TABLE IF NOT EXISTS debugger_function_arguments (
server_id INTEGER ,
database_id INTEGER ,
schema_id INTEGER ,
function_id INTEGER ,
arg_id INTEGER ,
is_null INTEGER NOT NULL CHECK (is_null >= 0 AND is_null <= 1) ,
is_expression INTEGER NOT NULL CHECK (is_expression >= 0 AND is_expression <= 1) ,
use_default INTEGER NOT NULL CHECK (use_default >= 0 AND use_default <= 1) ,
value TEXT,
PRIMARY KEY (server_id, database_id, schema_id, function_id, arg_id)
)""")
if version < 10:
db.engine.execute("""
CREATE TABLE process(
user_id INTEGER NOT NULL,
pid TEXT NOT NULL,
desc TEXT NOT NULL,
command TEXT NOT NULL,
arguments TEXT,
start_time TEXT,
end_time TEXT,
logdir TEXT,
exit_code INTEGER,
acknowledge TEXT,
PRIMARY KEY(pid),
FOREIGN KEY(user_id) REFERENCES user (id)
)""")
if version < 11:
db.engine.execute("""
UPDATE role
SET name = 'Administrator',
description = 'pgAdmin Administrator Role'
WHERE name = 'Administrators'
""")
db.engine.execute("""
INSERT INTO role ( name, description )
VALUES ('User', 'pgAdmin User Role')
""")
if version < 12:
db.engine.execute("ALTER TABLE server RENAME TO server_old")
db.engine.execute("""
CREATE TABLE server (
id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
servergroup_id INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
host VARCHAR(128) NOT NULL,
port INTEGER NOT NULL CHECK (port >= 1024 AND port <= 65535),
maintenance_db VARCHAR(64) NOT NULL,
username VARCHAR(64) NOT NULL,
ssl_mode VARCHAR(16) NOT NULL CHECK (
ssl_mode IN (
'allow', 'prefer', 'require', 'disable', 'verify-ca', 'verify-full'
)),
comment VARCHAR(1024), password TEXT(64), role text(64),
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES user (id),
FOREIGN KEY(servergroup_id) REFERENCES servergroup (id)
)""")
db.engine.execute("""
INSERT INTO server (
id, user_id, servergroup_id, name, host, port, maintenance_db, username,
ssl_mode, comment, password, role
) SELECT
id, user_id, servergroup_id, name, host, port, maintenance_db, username,
ssl_mode, comment, password, role
FROM server_old""")
db.engine.execute("DROP TABLE server_old")
if version < 13:
db.engine.execute("""
ALTER TABLE SERVER
ADD COLUMN discovery_id TEXT
""")
if version < 14:
db.engine.execute("""
CREATE TABLE keys (
name TEST NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (name))
""")
sql = "INSERT INTO keys (name, value) VALUES ('CSRF_SESSION_KEY', '%s')" % base64.urlsafe_b64encode(
os.urandom(32)).decode()
db.engine.execute(sql)
sql = "INSERT INTO keys (name, value) VALUES ('SECRET_KEY', '%s')" % base64.urlsafe_b64encode(
os.urandom(32)).decode()
db.engine.execute(sql)
# If SECURITY_PASSWORD_SALT is not in the config, but we're upgrading, then it must (unless the
# user edited the main config - which they shouldn't have done) have been at it's default
# value, so we'll use that. Otherwise, use whatever we can find in the config.
if hasattr(config, 'SECURITY_PASSWORD_SALT'):
sql = "INSERT INTO keys (name, value) VALUES ('SECURITY_PASSWORD_SALT', '%s')" % config.SECURITY_PASSWORD_SALT
else:
sql = "INSERT INTO keys (name, value) VALUES ('SECURITY_PASSWORD_SALT', 'SuperSecret3')"
db.engine.execute(sql)
# Finally, update the schema version
# version.value = config.SETTINGS_SCHEMA_VERSION
db.engine.execute('UPDATE version set value="%s" WHERE name = "ConfigDB"' %
config.SETTINGS_SCHEMA_VERSION)
def upgrade():
version = get_version()
# Changes introduced in schema version 2
if version < 2:
# Create the 'server' table
db.metadata.create_all(db.engine, tables=[Server.__table__])
if version < 3:
db.engine.execute(
'ALTER TABLE server ADD COLUMN comment TEXT(1024)'
)
if version < 4:
db.engine.execute(
'ALTER TABLE server ADD COLUMN password TEXT(64)'
)
if version < 5:
db.engine.execute('ALTER TABLE server ADD COLUMN role text(64)')
if version < 6:
db.engine.execute("ALTER TABLE server RENAME TO server_old")
db.engine.execute("""
CREATE TABLE server (
id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
servergroup_id INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
host VARCHAR(128) NOT NULL,
port INTEGER NOT NULL CHECK (port >= 1024 AND port <= 65534),
maintenance_db VARCHAR(64) NOT NULL,
username VARCHAR(64) NOT NULL,
ssl_mode VARCHAR(16) NOT NULL CHECK (
ssl_mode IN (
'allow', 'prefer', 'require', 'disable', 'verify-ca', 'verify-full'
)),
comment VARCHAR(1024), password TEXT(64), role text(64),
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES user (id),
FOREIGN KEY(servergroup_id) REFERENCES servergroup (id)
)""")
db.engine.execute("""
INSERT INTO server (
id, user_id, servergroup_id, name, host, port, maintenance_db, username,
ssl_mode, comment, password, role
) SELECT
id, user_id, servergroup_id, name, host, port, maintenance_db, username,
ssl_mode, comment, password, role
FROM server_old""")
db.engine.execute("DROP TABLE server_old")
if version < 8:
db.engine.execute("""
CREATE TABLE module_preference(
id INTEGER PRIMARY KEY,
name VARCHAR(256) NOT NULL
)""")
db.engine.execute("""
CREATE TABLE preference_category(
id INTEGER PRIMARY KEY,
mid INTEGER,
name VARCHAR(256) NOT NULL,
FOREIGN KEY(mid) REFERENCES module_preference(id)
)""")
db.engine.execute("""
CREATE TABLE preferences (
id INTEGER PRIMARY KEY,
cid INTEGER NOT NULL,
name VARCHAR(256) NOT NULL,
FOREIGN KEY(cid) REFERENCES preference_category (id)
)""")
db.engine.execute("""
CREATE TABLE user_preferences (
pid INTEGER,
uid INTEGER,
value VARCHAR(1024) NOT NULL,
PRIMARY KEY (pid, uid),
FOREIGN KEY(pid) REFERENCES preferences (pid),
FOREIGN KEY(uid) REFERENCES user (id)
)""")
if version < 9:
db.engine.execute("""
CREATE TABLE IF NOT EXISTS debugger_function_arguments (
server_id INTEGER ,
database_id INTEGER ,
schema_id INTEGER ,
function_id INTEGER ,
arg_id INTEGER ,
is_null INTEGER NOT NULL CHECK (is_null >= 0 AND is_null <= 1) ,
is_expression INTEGER NOT NULL CHECK (is_expression >= 0 AND is_expression <= 1) ,
use_default INTEGER NOT NULL CHECK (use_default >= 0 AND use_default <= 1) ,
value TEXT,
PRIMARY KEY (server_id, database_id, schema_id, function_id, arg_id)
)""")
if version < 10:
db.engine.execute("""
CREATE TABLE process(
user_id INTEGER NOT NULL,
pid TEXT NOT NULL,
desc TEXT NOT NULL,
command TEXT NOT NULL,
arguments TEXT,
start_time TEXT,
end_time TEXT,
logdir TEXT,
exit_code INTEGER,
acknowledge TEXT,
PRIMARY KEY(pid),
FOREIGN KEY(user_id) REFERENCES user (id)
)""")
if version < 11:
db.engine.execute("""
UPDATE role
SET name = 'Administrator',
description = 'pgAdmin Administrator Role'
WHERE name = 'Administrators'
""")
db.engine.execute("""
INSERT INTO role ( name, description )
VALUES ('User', 'pgAdmin User Role')
""")
if version < 12:
db.engine.execute("ALTER TABLE server RENAME TO server_old")
db.engine.execute("""
CREATE TABLE server (
id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
servergroup_id INTEGER NOT NULL,
name VARCHAR(128) NOT NULL,
host VARCHAR(128) NOT NULL,
port INTEGER NOT NULL CHECK (port >= 1024 AND port <= 65535),
maintenance_db VARCHAR(64) NOT NULL,
username VARCHAR(64) NOT NULL,
ssl_mode VARCHAR(16) NOT NULL CHECK (
ssl_mode IN (
'allow', 'prefer', 'require', 'disable', 'verify-ca', 'verify-full'
)),
comment VARCHAR(1024), password TEXT(64), role text(64),
PRIMARY KEY (id),
FOREIGN KEY(user_id) REFERENCES user (id),
FOREIGN KEY(servergroup_id) REFERENCES servergroup (id)
)""")
db.engine.execute("""
INSERT INTO server (
id, user_id, servergroup_id, name, host, port, maintenance_db, username,
ssl_mode, comment, password, role
) SELECT
id, user_id, servergroup_id, name, host, port, maintenance_db, username,
ssl_mode, comment, password, role
FROM server_old""")
db.engine.execute("DROP TABLE server_old")
if version < 13:
db.engine.execute("""
ALTER TABLE SERVER
ADD COLUMN discovery_id TEXT
""")
if version < 14:
db.engine.execute("""
CREATE TABLE keys (
name TEST NOT NULL,
value TEXT NOT NULL,
PRIMARY KEY (name))
""")
sql = "INSERT INTO keys (name, value) VALUES ('CSRF_SESSION_KEY', '%s')" % base64.urlsafe_b64encode(
os.urandom(32)).decode()
db.engine.execute(sql)
sql = "INSERT INTO keys (name, value) VALUES ('SECRET_KEY', '%s')" % base64.urlsafe_b64encode(
os.urandom(32)).decode()
db.engine.execute(sql)
# If SECURITY_PASSWORD_SALT is not in the config, but we're upgrading, then it must (unless the
# user edited the main config - which they shouldn't have done) have been at it's default
# value, so we'll use that. Otherwise, use whatever we can find in the config.
if hasattr(config, 'SECURITY_PASSWORD_SALT'):
sql = "INSERT INTO keys (name, value) VALUES ('SECURITY_PASSWORD_SALT', '%s')" % config.SECURITY_PASSWORD_SALT
else:
sql = "INSERT INTO keys (name, value) VALUES ('SECURITY_PASSWORD_SALT', 'SuperSecret3')"
db.engine.execute(sql)
# Finally, update the schema version
# version.value = config.SETTINGS_SCHEMA_VERSION
db.engine.execute(
'UPDATE version set value="%s" WHERE name = "ConfigDB"' % config.SETTINGS_SCHEMA_VERSION
)
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
if get_version() != -1:
return
op.create_table('version',
sa.Column('name', sa.String(length=32), nullable=False),
sa.Column('value', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('name'))
op.create_table(
'user', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(length=256), nullable=False),
sa.Column('password', sa.String(length=256), nullable=True),
sa.Column('active', sa.Boolean(), nullable=False),
sa.Column('confirmed_at', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('email'))
op.create_table(
'role', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('description', sa.String(length=256), nullable=False),
sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('name'))
op.create_table(
'setting', sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('setting', sa.String(length=256), nullable=False),
sa.Column('value', sa.String(length=1024), nullable=True),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('user_id', 'setting'))
op.create_table('roles_users',
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('role_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(
['role_id'],
['role.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
))
op.create_table('servergroup', sa.Column('id',
sa.Integer(),
nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('user_id', 'name'))
op.create_table(
'server', sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('servergroup_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('host', sa.String(length=128), nullable=False),
sa.Column('port', sa.Integer(), nullable=False),
sa.Column('maintenance_db', sa.String(length=64), nullable=False),
sa.Column('username', sa.String(length=64), nullable=False),
sa.Column('ssl_mode', sa.String(length=16), nullable=False),
sa.ForeignKeyConstraint(
['servergroup_id'],
['servergroup.id'],
), sa.ForeignKeyConstraint(
['user_id'],
['user.id'],
), sa.PrimaryKeyConstraint('id'))
email, password = user_info()
db.engine.execute("""
INSERT INTO "user"
VALUES(1, '%s',
'%s',
1, NULL)
""" % (email, password))
db.engine.execute("""
INSERT INTO "version"
VALUES('ConfigDB', 2);
""")
db.engine.execute("""
INSERT INTO "role"
VALUES(1, 'Administrators', 'pgAdmin Administrators Role')
""")
db.engine.execute("""
INSERT INTO "roles_users"
VALUES(1, 1);
""")
db.engine.execute("""
INSERT INTO "servergroup"
VALUES(1, 1, 'Servers')
""")
def upgrade():
verison = get_version()
db.engine.execute('ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)')
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
if get_version() != -1:
return
op.create_table('version',
sa.Column('name', sa.String(length=32), nullable=False),
sa.Column('value', sa.Integer(), nullable=False),
sa.PrimaryKeyConstraint('name')
)
op.create_table('user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('email', sa.String(length=256), nullable=False),
sa.Column('password', sa.String(length=256), nullable=True),
sa.Column('active', sa.Boolean(), nullable=False),
sa.Column('confirmed_at', sa.DateTime(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('email')
)
op.create_table('role',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('description', sa.String(length=256), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('name')
)
op.create_table('setting',
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('setting', sa.String(length=256), nullable=False),
sa.Column('value', sa.String(length=1024), nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('user_id', 'setting')
)
op.create_table('roles_users',
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('role_id', sa.Integer(), nullable=True),
sa.ForeignKeyConstraint(['role_id'], ['role.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], )
)
op.create_table('servergroup',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('user_id', 'name')
)
op.create_table('server',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=False),
sa.Column('servergroup_id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=128), nullable=False),
sa.Column('host', sa.String(length=128), nullable=False),
sa.Column('port', sa.Integer(), nullable=False),
sa.Column('maintenance_db', sa.String(length=64), nullable=False),
sa.Column('username', sa.String(length=64), nullable=False),
sa.Column('ssl_mode', sa.String(length=16), nullable=False),
sa.ForeignKeyConstraint(['servergroup_id'], ['servergroup.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id')
)
db.engine.execute("""
INSERT INTO "role"
VALUES(1, 'Administrators', 'pgAdmin Administrators Role')
""")
db.engine.execute("""
INSERT INTO "roles_users"
VALUES(1, 1);
""")
db.engine.execute("""
INSERT INTO "servergroup"
VALUES(1, 1, 'Servers')
""")
email, password = user_info()
current_salt = getattr(
config, 'SECURITY_PASSWORD_SALT', base64.urlsafe_b64encode(
os.urandom(32)
).decode()
)
if current_app.extensions.get('security') is None:
current_app.config['SECURITY_PASSWORD_SALT'] = current_salt
user_datastore = SQLAlchemyUserDatastore(db, User, Role)
Security(current_app, user_datastore, register_blueprint=False)
else:
current_app.config['SECURITY_PASSWORD_SALT'] = current_salt
setattr(config, 'SECURITY_PASSWORD_SALT', current_salt)
password = encrypt_password(password)
db.engine.execute("""
INSERT INTO "user"
VALUES(1, '%s',
'%s',
1, NULL)
""" % (email, password))
db.engine.execute("""
INSERT INTO "version"
VALUES('ConfigDB', 2);
""")
