Esempio n. 1
0
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
Esempio n. 2
0
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);
    """)
Esempio n. 3
0
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
Esempio n. 4
0
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)
Esempio n. 5
0
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
    )
Esempio n. 6
0
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')
""")
Esempio n. 7
0
def upgrade():
    verison = get_version()

    db.engine.execute('ALTER TABLE server ADD COLUMN hostaddr TEXT(1024)')
Esempio n. 8
0
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);
    """)