<filename>vakt/storage/sql/__init__.py
"""
SQL Storage for Policies.
"""

import logging

from sqlalchemy import and_, or_, literal, func
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm.exc import FlushError

from .model import PolicyModel, PolicyActionModel, PolicyResourceModel, PolicySubjectModel
from ..abc import Storage
from ...checker import StringExactChecker, StringFuzzyChecker, RegexChecker, RulesChecker
from ...exceptions import PolicyExistsError, UnknownCheckerType
from ...policy import TYPE_STRING_BASED, TYPE_RULE_BASED


log = logging.getLogger(__name__)


class SQLStorage(Storage):
    """Stores all policies in SQL Database"""

    def __init__(self, scoped_session):
        """
            Initialize SQL Storage

            :param scoped_session: SQL Alchemy scoped session
        """
        self.session = scoped_session
        self.dialect = self.session.bind.engine.dialect.name

    def add(self, policy):
        try:
            policy_model = PolicyModel.from_policy(policy)
            self.session.add(policy_model)
            self.session.commit()
        except IntegrityError:
            self.session.rollback()
            log.error('Error trying to create already existing policy with UID=%s.', policy.uid)
            raise PolicyExistsError(policy.uid)
        # todo - figure out why FlushError is raised instead of IntegrityError on PyPy tests
        except FlushError as e:
            if 'conflicts with persistent instance' in str(e):
                self.session.rollback()
                log.error('Error trying to create already existing policy with UID=%s.', policy.uid)
                raise PolicyExistsError(policy.uid)
        log.info('Added Policy: %s', policy)

    def get(self, uid):
        policy_model = self.session.query(PolicyModel).get(uid)
        if not policy_model:
            return None
        return policy_model.to_policy()

    def get_all(self, limit, offset):
        self._check_limit_and_offset(limit, offset)
        cur = self.session.query(PolicyModel).order_by(PolicyModel.uid.asc()).slice(offset, offset + limit)
        for policy_model in cur:
            yield policy_model.to_policy()

    def find_for_inquiry(self, inquiry, checker=None):
        cur = self._get_filtered_cursor(inquiry, checker)
        for policy_model in cur:
            yield policy_model.to_policy()

    def update(self, policy):
        try:
            policy_model = self.session.query(PolicyModel).get(policy.uid)
            if not policy_model:
                return
            policy_model.update(policy)
            self.session.commit()
        except IntegrityError:
            self.session.rollback()
            raise
        log.info('Updated Policy with UID=%s. New value is: %s', policy.uid, policy)

    def delete(self, uid):
        self.session.query(PolicyModel).filter(PolicyModel.uid == uid).delete()
        log.info('Deleted Policy with UID=%s.', uid)

    def _get_filtered_cursor(self, inquiry, checker):
        """
            Returns cursor with proper query-filter based on the checker type.
        """
        cur = self.session.query(PolicyModel)
        if isinstance(checker, StringFuzzyChecker):
            return cur.filter(
                PolicyModel.type == TYPE_STRING_BASED,
                PolicyModel.actions.any(PolicyActionModel.action_string.like('%{}%'.format(inquiry.action))),
                PolicyModel.resources.any(PolicyResourceModel.resource_string.like('%{}%'.format(inquiry.resource))),
                PolicyModel.subjects.any(PolicySubjectModel.subject_string.like('%{}%'.format(inquiry.subject))))
        elif isinstance(checker, StringExactChecker):
            return cur.filter(
                PolicyModel.type == TYPE_STRING_BASED,
                PolicyModel.actions.any(PolicyActionModel.action_string == inquiry.action),
                PolicyModel.resources.any(PolicyResourceModel.resource_string == inquiry.resource),
                PolicyModel.subjects.any(PolicySubjectModel.subject_string == inquiry.subject))
        elif isinstance(checker, RegexChecker):
            if not self._supports_regex_operator():
                return cur.filter(PolicyModel.type == TYPE_STRING_BASED)
            return cur.filter(
                PolicyModel.type == TYPE_STRING_BASED,
                PolicyModel.actions.any(
                    or_(
                        and_(PolicyActionModel.action_regex.is_(None),
                             PolicyActionModel.action_string == inquiry.action),
                        and_(PolicyActionModel.action_regex.isnot(None),
                             self._regex_operation(inquiry.action, PolicyActionModel.action_regex))
                    ),
                ),
                PolicyModel.resources.any(
                    or_(
                        and_(PolicyResourceModel.resource_regex.is_(None),
                             PolicyResourceModel.resource_string == inquiry.resource),
                        and_(PolicyResourceModel.resource_regex.isnot(None),
                             self._regex_operation(inquiry.resource, PolicyResourceModel.resource_regex))
                    ),
                ),
                PolicyModel.subjects.any(
                    or_(
                        and_(PolicySubjectModel.subject_regex.is_(None),
                             PolicySubjectModel.subject_string == inquiry.subject),
                        and_(PolicySubjectModel.subject_regex.isnot(None),
                             self._regex_operation(inquiry.subject, PolicySubjectModel.subject_regex))
                    ),
                )
            )
        elif isinstance(checker, RulesChecker):
            return cur.filter(PolicyModel.type == TYPE_RULE_BASED)
        elif not checker:
            return cur
        else:
            log.error('Provided Checker type is not supported.')
            raise UnknownCheckerType(checker)

    def _supports_regex_operator(self):
        """
        Does database support regex operator?
        """
        return self.dialect in ['mysql', 'postgresql', 'oracle']

    def _regex_operation(self, left, right):
        """
        Get database-specific regex operation.
        Don't forget to check if there is a support for regex operator before using it.
        """
        if self.dialect == 'mysql':
            return literal(left).op('REGEXP BINARY', is_comparison=True)(right)
        elif self.dialect == 'postgresql':
            return literal(left).op('~', is_comparison=True)(right)
        elif self.dialect == 'oracle':
            return func.REGEXP_LIKE(left, right)
        return None

<filename>vakt/storage/sql/__init__.py
"""
SQL Storage for Policies.
"""

import logging

from sqlalchemy import and_, or_, literal, func
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm.exc import FlushError

from .model import PolicyModel, PolicyActionModel, PolicyResourceModel, PolicySubjectModel
from ..abc import Storage
from ...checker import StringExactChecker, StringFuzzyChecker, RegexChecker, RulesChecker
from ...exceptions import PolicyExistsError, UnknownCheckerType
from ...policy import TYPE_STRING_BASED, TYPE_RULE_BASED


log = logging.getLogger(__name__)


class SQLStorage(Storage):
    """Stores all policies in SQL Database"""

    def __init__(self, scoped_session):
        """
            Initialize SQL Storage

            :param scoped_session: SQL Alchemy scoped session
        """
        self.session = scoped_session
        self.dialect = self.session.bind.engine.dialect.name

    def add(self, policy):
        try:
            policy_model = PolicyModel.from_policy(policy)
            self.session.add(policy_model)
            self.session.commit()
        except IntegrityError:
            self.session.rollback()
            log.error('Error trying to create already existing policy with UID=%s.', policy.uid)
            raise PolicyExistsError(policy.uid)
        # todo - figure out why FlushError is raised instead of IntegrityError on PyPy tests
        except FlushError as e:
            if 'conflicts with persistent instance' in str(e):
                self.session.rollback()
                log.error('Error trying to create already existing policy with UID=%s.', policy.uid)
                raise PolicyExistsError(policy.uid)
        log.info('Added Policy: %s', policy)

    def get(self, uid):
        policy_model = self.session.query(PolicyModel).get(uid)
        if not policy_model:
            return None
        return policy_model.to_policy()

    def get_all(self, limit, offset):
        self._check_limit_and_offset(limit, offset)
        cur = self.session.query(PolicyModel).order_by(PolicyModel.uid.asc()).slice(offset, offset + limit)
        for policy_model in cur:
            yield policy_model.to_policy()

    def find_for_inquiry(self, inquiry, checker=None):
        cur = self._get_filtered_cursor(inquiry, checker)
        for policy_model in cur:
            yield policy_model.to_policy()

    def update(self, policy):
        try:
            policy_model = self.session.query(PolicyModel).get(policy.uid)
            if not policy_model:
                return
            policy_model.update(policy)
            self.session.commit()
        except IntegrityError:
            self.session.rollback()
            raise
        log.info('Updated Policy with UID=%s. New value is: %s', policy.uid, policy)

    def delete(self, uid):
        self.session.query(PolicyModel).filter(PolicyModel.uid == uid).delete()
        log.info('Deleted Policy with UID=%s.', uid)

    def _get_filtered_cursor(self, inquiry, checker):
        """
            Returns cursor with proper query-filter based on the checker type.
        """
        cur = self.session.query(PolicyModel)
        if isinstance(checker, StringFuzzyChecker):
            return cur.filter(
                PolicyModel.type == TYPE_STRING_BASED,
                PolicyModel.actions.any(PolicyActionModel.action_string.like('%{}%'.format(inquiry.action))),
                PolicyModel.resources.any(PolicyResourceModel.resource_string.like('%{}%'.format(inquiry.resource))),
                PolicyModel.subjects.any(PolicySubjectModel.subject_string.like('%{}%'.format(inquiry.subject))))
        elif isinstance(checker, StringExactChecker):
            return cur.filter(
                PolicyModel.type == TYPE_STRING_BASED,
                PolicyModel.actions.any(PolicyActionModel.action_string == inquiry.action),
                PolicyModel.resources.any(PolicyResourceModel.resource_string == inquiry.resource),
                PolicyModel.subjects.any(PolicySubjectModel.subject_string == inquiry.subject))
        elif isinstance(checker, RegexChecker):
            if not self._supports_regex_operator():
                return cur.filter(PolicyModel.type == TYPE_STRING_BASED)
            return cur.filter(
                PolicyModel.type == TYPE_STRING_BASED,
                PolicyModel.actions.any(
                    or_(
                        and_(PolicyActionModel.action_regex.is_(None),
                             PolicyActionModel.action_string == inquiry.action),
                        and_(PolicyActionModel.action_regex.isnot(None),
                             self._regex_operation(inquiry.action, PolicyActionModel.action_regex))
                    ),
                ),
                PolicyModel.resources.any(
                    or_(
                        and_(PolicyResourceModel.resource_regex.is_(None),
                             PolicyResourceModel.resource_string == inquiry.resource),
                        and_(PolicyResourceModel.resource_regex.isnot(None),
                             self._regex_operation(inquiry.resource, PolicyResourceModel.resource_regex))
                    ),
                ),
                PolicyModel.subjects.any(
                    or_(
                        and_(PolicySubjectModel.subject_regex.is_(None),
                             PolicySubjectModel.subject_string == inquiry.subject),
                        and_(PolicySubjectModel.subject_regex.isnot(None),
                             self._regex_operation(inquiry.subject, PolicySubjectModel.subject_regex))
                    ),
                )
            )
        elif isinstance(checker, RulesChecker):
            return cur.filter(PolicyModel.type == TYPE_RULE_BASED)
        elif not checker:
            return cur
        else:
            log.error('Provided Checker type is not supported.')
            raise UnknownCheckerType(checker)

    def _supports_regex_operator(self):
        """
        Does database support regex operator?
        """
        return self.dialect in ['mysql', 'postgresql', 'oracle']

    def _regex_operation(self, left, right):
        """
        Get database-specific regex operation.
        Don't forget to check if there is a support for regex operator before using it.
        """
        if self.dialect == 'mysql':
            return literal(left).op('REGEXP BINARY', is_comparison=True)(right)
        elif self.dialect == 'postgresql':
            return literal(left).op('~', is_comparison=True)(right)
        elif self.dialect == 'oracle':
            return func.REGEXP_LIKE(left, right)
        return None

SQL Storage for Policies. Stores all policies in SQL Database Initialize SQL Storage              :param scoped_session: SQL Alchemy scoped session # todo - figure out why FlushError is raised instead of IntegrityError on PyPy tests Returns cursor with proper query-filter based on the checker type. Does database support regex operator? Get database-specific regex operation.         Don't forget to check if there is a support for regex operator before using it.