class APIkey(AsDictMixin, Base): __tablename__ = 'apikeys' __table_args__ = (UniqueConstraint(u'uid', u'name', name='uniq_apikey_owner'), ) __rest_exclude__ = ('hashvalue', ) __rest_related__ = ('scopes', ) id = Column(String(16), primary_key=True, unique=True) name = Column(String(32), nullable=False) uid = Column(ForeignKey(u'people.id', ondelete='CASCADE'), nullable=False) prefix = Column(String(8), nullable=False, unique=True) hashvalue = Column(StringEncryptedType(Unicode, aes_secret, AesEngine, 'pkcs5', length=96), nullable=False) expires = Column(TIMESTAMP) last_used = Column(TIMESTAMP) created = Column(TIMESTAMP, nullable=False, server_default=func.now()) modified = Column(TIMESTAMP) status = Column(Enum(u'active', u'disabled'), nullable=False, server_default=u'active') scopes = relationship('Scope', secondary='apikeyscopes', backref=backref('scope'), order_by='Scope.name') owner = relationship('Person', foreign_keys=[uid], backref=backref('apikey_uid', cascade='all, delete-orphan'))
class DomainCertificate(DomainModel, CertificateModel): __tablename__ = "certificates" id = sa.Column(sa.Integer, sa.Sequence("certificates_id_seq"), primary_key=True) created_at = sa.Column(postgresql.TIMESTAMP) updated_at = sa.Column(postgresql.TIMESTAMP) deleted_at = sa.Column(postgresql.TIMESTAMP, index=True) route_guid = sa.Column(sa.Text) domain = sa.Column(sa.Text) # cert_url is the Let's Encrypt URL for the certificate cert_url = sa.Column(sa.Text) # certificate is the actual body of the certificate chain # this was used by the old broker, but the renewer uses fullchain_pem and leaf_pem instead certificate = sa.Column(postgresql.BYTEA) expires = sa.Column(postgresql.TIMESTAMP, index=True) private_key_pem: str = sa.Column( StringEncryptedType(sa.Text, db_encryption_key, AesGcmEngine, "pkcs5")) csr_pem = sa.Column(sa.Text) challenges: List["DomainChallenge"] = orm.relationship( "DomainChallenge", backref="certificate", lazy="dynamic") order_json = sa.Column(sa.Text) fullchain_pem = sa.Column(sa.Text) leaf_pem = sa.Column(sa.Text) iam_server_certificate_id = sa.Column(sa.Text) iam_server_certificate_name = sa.Column(sa.Text) iam_server_certificate_arn = sa.Column(sa.Text)
def create(self, app_config: Optional[Dict[str, Any]], *args: List[Any], **kwargs: Optional[Dict[str, Any]]) -> TypeDecorator: if app_config: return StringEncryptedType(*args, app_config["SECRET_KEY"], **kwargs) else: raise Exception("Missing app_config kwarg")
def bank_account_number(cls): # pylint:disable=no-self-argument, # noqa: N805 """Declare attribute for bank account number.""" return db.Column('bank_account_number', StringEncryptedType(String, cls._get_enc_secret, AesEngine, 'pkcs5'), nullable=True, index=True)
class ServiceInstance(Base): id = db.Column(db.String(36), primary_key=True) operations = db.relation("Operation", backref="service_instance", lazy="dynamic") challenges = db.relation("Challenge", backref="service_instance", lazy="dynamic") acme_user_id = db.Column(db.Integer, db.ForeignKey("acme_user.id")) domain_names = db.Column(postgresql.JSONB, default=[]) order_json = db.Column(db.Text) csr_pem = db.Column(db.Text) cert_pem = db.Column(db.Text) private_key_pem = db.Column( StringEncryptedType(db.Text, db_encryption_key, AesGcmEngine, "pkcs5")) fullchain_pem = db.Column(db.Text) iam_server_certificate_id = db.Column(db.String) iam_server_certificate_name = db.Column(db.String) iam_server_certificate_arn = db.Column(db.String) cloudfront_distribution_arn = db.Column(db.String) cloudfront_distribution_id = db.Column(db.String) cloudfront_distribution_url = db.Column(db.String) cloudfront_origin_hostname = db.Column(db.String) cloudfront_origin_path = db.Column(db.String) route53_change_ids = db.Column(postgresql.JSONB, default=[]) deactivated_at = db.Column(db.TIMESTAMP(timezone=True)) def __repr__(self): return f"<ServiceInstance {self.id} {self.domain_names}>"
class Account(AsDictMixin, Base): __tablename__ = 'accounts' __table_args__ = ( UniqueConstraint(u'id', u'uid', name='uniq_account_user'), dict(schema=core_schema), ) __rest_exclude__ = ('backup_codes', 'password', 'totp_secret') __rest_hybrid__ = ('totp', ) id = Column(String(16), primary_key=True, unique=True) name = Column(String(32), nullable=False, unique=True) uid = Column(ForeignKey(u'%speople.id' % schema_pre, ondelete='CASCADE'), nullable=False, unique=True) password = Column(StringEncryptedType(Unicode, get_aes_secret, AesEngine, 'pkcs5', length=128), nullable=False) password_must_change = Column(BOOLEAN, nullable=False, server_default="False") totp_secret = Column( StringEncryptedType(Unicode, get_aes_secret, AesEngine, 'pkcs5', length=64)) backup_codes = Column(LargeBinary(256)) is_admin = Column(BOOLEAN, nullable=False, server_default="False") settings_id = Column(ForeignKey(u'%ssettings.id' % schema_pre), nullable=False) last_login = Column(TIMESTAMP) created = Column(TIMESTAMP, nullable=False, server_default=func.now()) modified = Column(TIMESTAMP) status = Column(Enum(u'active', u'disabled'), nullable=False) owner = relationship('Person', foreign_keys=[uid], backref=backref('account_uid', cascade='all, delete-orphan')) settings = relationship('Settings') @hybrid_property def totp(self): return True if self.totp_secret else False
class User(UserMixin, db.Model): __tablename__ = "users" id = db.Column(db.Integer, primary_key=True) username = db.Column(StringEncryptedType(db.String, settings.DB_ENCRYPT_KEY, AesEngine), unique=True) email = db.Column( StringEncryptedType(db.String, settings.DB_ENCRYPT_KEY, AesEngine)) avatar_url = db.Column(db.String(255)) name = db.Column(db.String(255)) oauth_token = db.Column( StringEncryptedType(db.Text, settings.DB_ENCRYPT_KEY, AesEngine)) courses = db.relationship('Course', secondary=registered, backref=db.backref('users'))
class Credential(AsDictMixin, Base): __tablename__ = 'credentials' __table_args__ = ( UniqueConstraint(u'name', u'uid', name='uniq_name_uid'), dict(schema=core_schema), ) __rest_exclude__ = ('secret', 'otherdata') id = Column(String(16), primary_key=True, unique=True) name = Column(String(64), nullable=False) vendor = Column(String(32), nullable=False) # placeholder field TODO make clear how this will be used # probably to delegate to Secrets Manager / KMS type = Column(String(16)) url = Column(String(length=64)) key = Column(String(length=128)) secret = Column( StringEncryptedType(Unicode, get_aes_secret, AesEngine, 'pkcs5', length=128)) otherdata = Column( StringEncryptedType(Unicode, get_aes_secret, AesEngine, 'pkcs5', length=1024)) expires = Column(TIMESTAMP) settings_id = Column(ForeignKey(u'%ssettings.id' % schema_pre), nullable=False) uid = Column(ForeignKey(u'%speople.id' % schema_pre, ondelete='CASCADE'), nullable=False) created = Column(TIMESTAMP, nullable=False, server_default=func.now()) modified = Column(TIMESTAMP) status = Column(Enum(u'active', u'disabled'), nullable=False, server_default=u'active') settings = relationship('Settings', foreign_keys=[settings_id]) owner = relationship('Person', foreign_keys=[uid], backref=backref('credential_uid', cascade='all, delete-orphan'))
class ACMEUser(Base): __tablename__ = "acme_user" id = db.Column(db.Integer, primary_key=True) email = db.Column(db.String, nullable=False) uri = db.Column(db.String, nullable=False) private_key_pem = db.Column( StringEncryptedType(db.Text, db_encryption_key, AesGcmEngine, "pkcs5"), nullable=False, ) registration_json = db.Column(db.Text) service_instances = db.relation("ServiceInstance", backref="acme_user", lazy="dynamic")
class ServiceInstance(Base): __tablename__ = "service_instance" id = sa.Column(sa.String(36), primary_key=True) domain_names = sa.Column(postgresql.JSONB, default=[]) cert_pem = sa.Column(sa.Text) cert_expires_at = sa.Column(sa.TIMESTAMP(timezone=True)) private_key_pem = sa.Column( StringEncryptedType(sa.Text, db_encryption_key, AesGcmEngine, "pkcs5") ) fullchain_pem = sa.Column(sa.Text) iam_server_certificate_id = sa.Column(sa.String) iam_server_certificate_name = sa.Column(sa.String) iam_server_certificate_arn = sa.Column(sa.String) current_certificate_id = sa.Column(sa.Integer, sa.ForeignKey("certificate.id")) new_certificate_id = sa.Column(sa.Integer, sa.ForeignKey("certificate.id")) csr_pem = sa.Column(sa.Text)
class DomainAcmeUserV2(DomainModel, AcmeUserV2Model): __tablename__ = "acme_user_v2" id = sa.Column(sa.Integer, autoincrement=True, primary_key=True) email = sa.Column(sa.String, nullable=False) uri = sa.Column(sa.String, nullable=False) private_key_pem: str = sa.Column( StringEncryptedType(sa.Text, db_encryption_key, AesGcmEngine, "pkcs5")) registration_json = sa.Column(sa.Text) routes: List[DomainRoute] = orm.relation( "DomainRoute", backref="acme_user", primaryjoin= "(foreign(DomainRoute.acme_user_id)) == DomainAcmeUserV2.id", )
class Certificate(Base): __tablename__ = "certificate" id = sa.Column(sa.Integer, primary_key=True) service_instance_id = sa.Column( sa.String, sa.ForeignKey("service_instance.id"), nullable=False ) subject_alternative_names = sa.Column(postgresql.JSONB, default=[]) leaf_pem = sa.Column(sa.Text) expires_at = sa.Column(sa.TIMESTAMP(timezone=True)) private_key_pem = sa.Column( StringEncryptedType(sa.Text, db_encryption_key, AesGcmEngine, "pkcs5") ) fullchain_pem = sa.Column(sa.Text) iam_server_certificate_id = sa.Column(sa.String) iam_server_certificate_name = sa.Column(sa.String) iam_server_certificate_arn = sa.Column(sa.String) csr_pem = sa.Column(sa.Text)
class GitData(db.Model): __tablename__ = "gitdata" id = db.Column(db.Integer, primary_key=True) sha = db.Column(db.Text, nullable=False, unique=True) ref = db.Column(db.Text) api_url = db.Column(db.Text) additions = db.Column(db.Integer) deletions = db.Column(db.Integer) date = db.Column(db.DateTime) contributor_user = db.Column( StringEncryptedType(db.Text, settings.DB_ENCRYPT_KEY, AesEngine)) repo_id = db.Column(db.Integer, db.ForeignKey("repos.id")) repo = db.relationship(Repo, backref=db.backref("gitdata", lazy='dynamic', cascade="all, delete"))
class Certificate(Base): id = db.Column(db.Integer, primary_key=True) service_instance_id = db.Column(db.String, db.ForeignKey("service_instance.id"), nullable=False) subject_alternative_names = db.Column(postgresql.JSONB, default=[]) leaf_pem = db.Column(db.Text) expires_at = db.Column(db.TIMESTAMP(timezone=True)) private_key_pem = db.Column( StringEncryptedType(db.Text, db_encryption_key, AesGcmEngine, "pkcs5")) csr_pem = db.Column(db.Text) fullchain_pem = db.Column(db.Text) iam_server_certificate_id = db.Column(db.String) iam_server_certificate_name = db.Column(db.String) iam_server_certificate_arn = db.Column(db.String) challenges = db.relation("Challenge", backref="certificate", lazy="dynamic", cascade="all, delete-orphan") order_json = db.Column(db.Text)
class Album(AsDictMixin, Base): __tablename__ = 'albums' __table_args__ = (UniqueConstraint(u'name', u'uid', name='uniq_album_user'), ) __rest_related__ = ('pictures', ) id = Column(String(16), primary_key=True, unique=True) name = Column(String(64), nullable=False) sizes = Column(String(32), nullable=False, server_default=constants.DEFAULT_THUMBNAIL_SIZES) encryption = Column(Enum(u'aes')) password = Column( StringEncryptedType(Unicode, aes_secret, AesEngine, 'pkcs5', length=64)) uid = Column(ForeignKey(u'people.id', ondelete='CASCADE'), nullable=False) list_id = Column(ForeignKey(u'lists.id')) event_id = Column(ForeignKey(u'events.id')) cover_id = Column(ForeignKey(u'pictures.id')) category_id = Column(ForeignKey(u'categories.id'), nullable=False) privacy = Column(String(8), nullable=False, server_default=u'invitee') created = Column(TIMESTAMP, nullable=False, server_default=func.now()) modified = Column(TIMESTAMP) status = Column(Enum('active', u'disabled'), nullable=False, server_default=u'active') pictures = relationship('Picture', secondary='albumcontents', backref=backref('albums'), order_by='AlbumContent.rank') cover = relationship('Picture', foreign_keys=[cover_id]) list = relationship('List') category = relationship('Category') owner = relationship('Person', foreign_keys=[uid], backref=backref('album_uid', cascade='all, delete-orphan'))
class Tokens(dec_Base.Base, sso_base): __tablename__ = 'tokens' token_id = Column(Integer, primary_key=True, nullable=False, autoincrement=True) discord_user = Column(BIGINT, ForeignKey("discord_users.user_id"), nullable=False) refresh_token = Column(StringEncryptedType( String, InsightUtilities.ColumnEncryption.helper_get_key(), AesEngine, 'pkcs5'), nullable=False) token = Column(StringEncryptedType( String, InsightUtilities.ColumnEncryption.helper_get_key(), AesEngine, 'pkcs5'), nullable=True) # JWT token character_id = Column(Integer, ForeignKey("characters.character_id"), nullable=True) corporation_id = Column(Integer, ForeignKey("corporations.corporation_id"), nullable=True) alliance_id = Column(Integer, ForeignKey("alliances.alliance_id"), nullable=True) last_updated = Column(DateTime, default=datetime.datetime.utcnow(), nullable=False) last_modified = Column(DateTime, default=datetime.datetime.utcnow(), nullable=False) etag_character = Column(String, nullable=True) etag_corp = Column(String, nullable=True) etag_alliance = Column(String, nullable=True) error_count = Column(Integer, default=0, nullable=False) object_user = relationship("Users", uselist=False, back_populates="object_tokens") object_channels = relationship("Discord_Tokens", uselist=True, cascade="delete", back_populates="object_token") object_pilot = relationship("Characters", uselist=False, lazy="joined") object_corp = relationship("Corporations", uselist=False, lazy="joined") object_alliance = relationship("Alliances", uselist=False, lazy="joined") object_contacts_pilots = relationship( "Contacts_Characters", cascade="save-update,merge,delete,delete-orphan", uselist=True, back_populates="object_token") object_contacts_corps = relationship( "Contacts_Corporations", cascade="save-update,merge,delete,delete-orphan", uselist=True, back_populates="object_token") object_contacts_alliances = relationship( "Contacts_Alliances", cascade="save-update,merge,delete,delete-orphan", uselist=True, back_populates="object_token") def __init__(self, discord_user, refresh_token): self.discord_user = discord_user self.refresh_token = refresh_token def __str__(self): syncs_with = "Pilot: {}\n".format( self.object_pilot.character_name) if self.object_pilot else "" syncs_with += "Corp: {}\n".format( self.object_corp.corporation_name) if self.object_corp else "" syncs_with += "Alliance: {}\n".format( self.object_alliance.alliance_name) if self.object_alliance else "" syncs_with += "Contains {} pilot, {} corp, and {} alliance contacts." \ .format(str(len(self.object_contacts_pilots)), str(len(self.object_contacts_corps)), str(len(self.object_contacts_alliances))) return syncs_with def str_mod(self): rst = "TokenID: {} Modified: {}\n".format( str(self.token_id), self.last_modified.replace(microsecond=0)) rst += self.__str__() return rst def str_upd(self): rst = "TokenID: {} Updated: {}\n".format( str(self.token_id), self.last_updated.replace(microsecond=0)) rst += self.__str__() return rst def str_wChcount(self): ch_count = "Discord channels using this token: {}".format( str(len(self.object_channels))) return "{}\n{}".format(self.str_upd(), ch_count) def get_affiliation(self, token, service_module): char_id = service_module.sso.get_char(token) if char_id: self.object_pilot = tb_characters(char_id) api = swagger_client.CharacterApi() api.api_client.set_default_header('User-Agent', "InsightDiscordKillfeeds") response = api.get_characters_character_id(character_id=char_id) if response.corporation_id: self.object_corp = tb_corporations(response.corporation_id) if response.alliance_id: self.object_alliance = tb_alliances(response.alliance_id) else: raise InsightExc.SSO.SSOerror def __alliance_api_call(self, api, **kwargs): return api.get_alliances_alliance_id_contacts_with_http_info( alliance_id=self.alliance_id, **kwargs) def __set_alliance_etag(self, new_etag): self.etag_alliance = new_etag def __corp_api_call(self, api, **kwargs): return api.get_corporations_corporation_id_contacts_with_http_info( corporation_id=self.corporation_id, **kwargs) def __set_corp_etag(self, new_etag): self.etag_corp = new_etag def __pilot_api_call(self, api, **kwargs): return api.get_characters_character_id_contacts_with_http_info( character_id=self.character_id, **kwargs) def __set_pilot_etag(self, new_etag): self.etag_character = new_etag def get_results(self, function_ptr, etag, new_etag_function_ptr): swagger_config = swagger_client.Configuration() swagger_config.access_token = self.token contacts_api = swagger_client.ContactsApi( swagger_client.ApiClient(swagger_config)) r = function_ptr(contacts_api, datasource='tranquility', token=self.token, if_none_match=str(etag)) __new_etag = r[2].get("Etag") number_pages = r[2].get("X-Pages") if number_pages is None: return r[0] else: r_items = [] + r[0] __index = 2 while int(number_pages) >= __index: r_items += function_ptr(contacts_api, page=__index, datasource='tranquility', token=self.token)[0] __index += 1 new_etag_function_ptr(__new_etag) return r_items def process_contact(self, contact, owner, service_module): if contact.contact_id == self.character_id or contact.contact_id == self.corporation_id or contact.contact_id == self.alliance_id: return # prevent adding self elif contact.contact_type == "character": self.object_contacts_pilots.append( tb_contacts_pilots(contact.contact_id, owner, contact.standing, service_module)) elif contact.contact_type == "corporation": self.object_contacts_corps.append( tb_contacts_corps(contact.contact_id, owner, contact.standing, service_module)) elif contact.contact_type == "alliance": self.object_contacts_alliances.append( tb_contacts_alliances(contact.contact_id, owner, contact.standing, service_module)) else: return def __remove(self, enum_owner, service_module): db: Session = service_module.get_session() def helper(table): rows = db.query(table).filter(table.token == self.token_id, table.owner == enum_owner) rows.delete() helper(tb_contacts_alliances) helper(tb_contacts_corps) helper(tb_contacts_pilots) def write_changes(self, api_call, etag, etag_function, enum_owner, service_module): lg = InsightLogger.InsightLogger.get_logger('Tokens.{}'.format( self.token_id), 'Tokens.log', child=True) try: contacts = self.get_results(api_call, etag, etag_function) self.__remove(enum_owner, service_module) for c in contacts: self.process_contact(c, enum_owner, service_module) if enum_owner == contact_owner.pilot and self.character_id is not None: self.object_contacts_pilots.append( tb_contacts_pilots(self.character_id, enum_owner, 10.0, service_module)) elif enum_owner == contact_owner.corp and self.corporation_id is not None: self.object_contacts_corps.append( tb_contacts_corps(self.corporation_id, enum_owner, 10.0, service_module)) elif enum_owner == contact_owner.alliance and self.alliance_id is not None: self.object_contacts_alliances.append( tb_contacts_alliances(self.alliance_id, enum_owner, 10.0, service_module)) else: pass self.last_updated = datetime.datetime.utcnow() self.last_modified = datetime.datetime.utcnow() lg.info('{} contact update ok.'.format(enum_owner.value)) except ApiException as ex: if ex.status == 304: # nochanges self.last_updated = datetime.datetime.utcnow() lg.info('{} contact update ok. No changes.'.format( enum_owner.value)) elif ex.status == 403: # changed alliance/corp er_resp = "" try: error_body = json.loads(ex.body) er_resp = str(error_body.get('error')) except Exception as ex_403: lg.exception(ex_403) if enum_owner == contact_owner.pilot and er_resp == "Character ID mismatch between request path and auth token": self.character_id = None self.__remove(enum_owner, service_module) lg.info('Removing contact type: {}'.format( enum_owner.value)) if enum_owner == contact_owner.corp and er_resp == "Character is not in the corporation": self.corporation_id = None self.__remove(enum_owner, service_module) lg.info('Removing contact type: {}'.format( enum_owner.value)) if enum_owner == contact_owner.alliance and er_resp == "Character is not in the alliance": self.alliance_id = None self.__remove(enum_owner, service_module) lg.info('Removing contact type: {}'.format( enum_owner.value)) else: pass if ex.status != 304: lg.warning('{} updating error: {} Headers: {} Body: {}'.format( enum_owner.value, ex.status, ex.headers, ex.body)) except Exception as ex: print("Error: '{}' on token ID: {} when updating contacts.".format( str(ex), str(self.token_id))) lg.exception(ex) def update_contacts(self, service_): if self.token is not None: self.write_changes(self.__alliance_api_call, self.etag_alliance, self.__set_alliance_etag, contact_owner.alliance, service_) if self.alliance_id else None self.write_changes(self.__corp_api_call, self.etag_corp, self.__set_corp_etag, contact_owner.corp, service_) if self.corporation_id else None self.write_changes(self.__pilot_api_call, self.etag_character, self.__set_pilot_etag, contact_owner.pilot, service_) if self.character_id else None @classmethod def generate_from_auth(cls, discord_user_id, auth_code, service_module): db: Session = service_module.get_session() try: response = service_module.sso.get_token_from_auth(auth_code) except: raise InsightExc.SSO.SSOerror( "An error occurred when attempting to get a token from the auth code. Please try again later." ) try: __row = cls(discord_user_id, response.get("refresh_token")) service_module.sso.verify_jwt( response.get("access_token") ) # verify the token. Raise exception if invalid and cancel transaction __row.get_affiliation(response.get("access_token"), service_module) db.merge(__row) db.commit() name_resolver.api_mass_name_resolve(service_module, exclude_nonentity=True) return db.query(cls).filter( cls.discord_user == discord_user_id, cls.refresh_token == response.get("refresh_token")).one() except Exception as ex: print(ex) raise InsightExc.Db.DatabaseError finally: db.close() @classmethod def sync_all_tokens(cls, discord_user_id, service_module): db: Session = service_module.get_session() try: tokens = db.query(cls).filter( cls.discord_user == discord_user_id).all() for t in tokens: service_module.sso.get_token(t) db.commit() tokens = db.query(cls).filter(cls.discord_user == discord_user_id, cls.token.isnot(None)).all() for t in tokens: t.update_contacts(service_module) db.commit() except Exception as ex: print(ex) finally: db.close() @classmethod def mass_sync_all(cls, service_module): lg = InsightLogger.InsightLogger.get_logger('Tokens', 'Tokens.log') st = InsightLogger.InsightLogger.time_start() db: Session = service_module.get_session() try: ids = [ id.discord_user for id in db.query(cls.discord_user).distinct() ] db.close() for id in ids: cls.sync_all_tokens(id, service_module) except Exception as ex: print(ex) finally: db.close() InsightLogger.InsightLogger.time_log( lg, st, 'Update all tokens in the database.', warn_higher=1200, seconds=True) @classmethod def delete_noTracking(cls, service_module): """Find tokens that have no tracking targets and delete them.""" db: Session = service_module.get_session() try: all_tokens = db.query(cls).all() delete_tokens = [] for tok in all_tokens: if tok.character_id is None and tok.corporation_id is None and tok.alliance_id is None: delete_tokens.append(tok) db.close() for tok in delete_tokens: service_module.sso.delete_token(tok) except Exception as ex: print(ex) finally: db.close()
class AnalysisService(Base): __tablename__ = 'analysis_services' read = create = update = delete = accessible_by_groups_members name = sa.Column( sa.String, unique=True, index=True, nullable=False, doc='Unique name/identifier of the analysis service.', ) display_name = sa.Column( sa.String, nullable=False, doc='Display name of the analysis service.' ) description = sa.Column( sa.String, nullable=True, doc=( 'Long-form description of what the analysis service does,' ' what it returns, and what it requires. Could include' ' links to documentation and code here.' ), ) version = sa.Column( sa.String, nullable=True, doc='Semantic version (or githash) of the analysis service.', ) contact_name = sa.Column( sa.String, nullable=True, doc=( 'Name of person responsible for the service (ie. the maintainer). ' ' This person does not need to be part of this SkyPortal instance.' ), ) contact_email = sa.Column( EmailType(), nullable=True, doc='Email address of the person responsible for the service.', ) url = sa.Column( URLType, nullable=False, doc=( "URL to running service accessible to this SkyPortal instance. " " For example, http://localhost:5000/analysis/<service_name>." ), ) optional_analysis_parameters = sa.Column( JSONType, nullable=True, default=dict, doc=( 'Optional parameters to be passed to the analysis service, along with ' 'possible values to be shown in the UI. ' ), ) authentication_type = sa.Column( allowed_external_authentication_types, nullable=False, doc=( f'''Service authentiction method. One of: {', '.join(f"'{t}'" for t in AUTHENTICATION_TYPES)}.''' ' See https://docs.python-requests.org/en/master/user/authentication/' ), ) _authinfo = sa.Column( StringEncryptedType(JSONType, cfg['app.secret_key'], AesEngine, 'pkcs5'), nullable=True, doc=('Contains authentication credentials for the service.'), ) enabled = sa.Column(sa.Boolean, nullable=False, default=True) analysis_type = sa.Column( allowed_analysis_types, nullable=False, doc=f'''Type of analysis. One of: {', '.join(f"'{t}'" for t in ANALYSIS_TYPES)}''', ) input_data_types = sa.Column( ARRAY(allowed_analysis_input_types), default=[], doc=( 'List of allowed_analysis_input_types required by the service.' ' This data will be assembled and sent over to the analysis service.' ), ) groups = relationship( "Group", secondary="group_analysisservices", cascade="save-update, merge, refresh-expire, expunge", passive_deletes=True, doc="Groups that can access to this analysis service.", ) timeout = sa.Column( sa.Float, default=3600.0, doc="Max time in seconds to wait for the analysis service to complete.", ) upload_only = sa.Column( sa.Boolean, default=False, doc=( "If true, the analysis service is an upload type, where the user is responsible" " for providing the input data to the service. If false, the service is " " called using the data provided in input_data_types" ), ) obj_analyses = relationship( 'ObjAnalysis', back_populates='analysis_service', cascade='save-update, merge, refresh-expire, expunge, delete-orphan, delete', passive_deletes=True, doc="Instances of analysis applied to specific objects", ) @property def authinfo(self): if self._authinfo is None: return {} else: return json.loads(self._authinfo) @authinfo.setter def authinfo(self, value): self._authinfo = value
def Encrypt(Col=Unicode, array=False, **args): enc = StringEncryptedType(Col, vars.FLASK_KEY, FernetEngine) if array: enc = ARRAY(enc) return Column(enc, **args)
class CatSource(BaseModel): __tablename__ = "sources" id = Column(Integer, primary_key=True) source_type = Column(String) name = Column(String, unique=True) uri = Column(String) port = Column(String) username = Column(String) password = Column(StringEncryptedType(String, get_secret_key, FernetEngine)) database = Column(String) cluster = Column(String) project_id = Column(String) project_credentials = Column( StringEncryptedType(String, get_secret_key, FernetEngine)) page_size = Column(String) filter_key = Column(String) included_tables_regex = Column(String) key_path = Column(String) account = Column(String) role = Column(String) warehouse = Column(String) aws_access_key_id = Column( StringEncryptedType(String, get_secret_key, FernetEngine)) aws_secret_access_key = Column( StringEncryptedType(String, get_secret_key, FernetEngine)) region_name = Column(String) s3_staging_dir = Column(String) schemata = relationship("CatSchema", back_populates="source") jobs = relationship("Job", back_populates="source") default_schema = relationship( "DefaultSchema", back_populates="source", cascade="all, delete-orphan", uselist=False, ) def __init__( self, source_type: str, name: str, uri: Optional[str] = None, port: Optional[int] = None, username: Optional[str] = None, password: Optional[str] = None, database: Optional[str] = None, cluster: Optional[str] = None, project_id: Optional[str] = None, project_credentials: Optional[str] = None, page_size: Optional[str] = None, filter_key: Optional[str] = None, included_tables_regex: Optional[str] = None, key_path: Optional[str] = None, account: Optional[str] = None, role: Optional[str] = None, warehouse: Optional[str] = None, aws_access_key_id: Optional[str] = None, aws_secret_access_key: Optional[str] = None, region_name: Optional[str] = None, s3_staging_dir: Optional[str] = None, **kwargs, ): self.uri = uri self.port = port if source_type is not None: source_type = source_type.lower() self.source_type = source_type self.username = username self.password = password self.name = name self.database = database self.cluster = cluster self.project_id = project_id self.project_credentials = project_credentials self.page_size = page_size self.filter_key = filter_key self.included_tables_regex = included_tables_regex self.key_path = key_path self.role = role self.account = account self.warehouse = warehouse self.aws_access_key_id = aws_access_key_id self.aws_secret_access_key = aws_secret_access_key self.region_name = region_name self.s3_staging_dir = s3_staging_dir @property def conn_string(self): if self.source_type == "bigquery": project_id = self.project_id conn_string = f"bigquery://{project_id}" elif self.source_type == "snowflake": conn_string = URL( account=self.account, user=self.username, password=self.password, database=self.database, warehouse=self.warehouse, role=self.role, ) elif self.source_type == "sqlite": conn_string = "sqlite:///{}".format(self.uri) elif self.source_type == "athena": conn_string = ( "awsathena+rest://{aws_access_key_id}:{aws_secret_access_key}@athena.{region_name}.amazonaws.com:443/" "?s3_staging_dir={s3_staging_dir}".format( aws_access_key_id=quote_plus(self.aws_access_key_id) if self.aws_access_key_id is not None else "", aws_secret_access_key=quote_plus( self.aws_secret_access_key) if self.aws_secret_access_key is not None else "", region_name=self.region_name, s3_staging_dir=quote_plus(self.s3_staging_dir), )) else: username_password_placeholder = (f"{self.username}:{self.password}" if self.password is not None else "") if self.source_type in ["redshift"]: self.dialect = "postgres" elif self.source_type == "mysql": self.dialect = "mysql+pymysql" else: self.dialect = self.source_type uri_port_placeholder = (f"{self.uri}:{self.port}" if self.port is not None else f"{self.uri}") database = self.database or "" conn_string = f"{self.dialect}://{username_password_placeholder}@{uri_port_placeholder}/{database}" return conn_string @property def fqdn(self): return self.name def __repr__(self): return "<Source: {}>".format(self.name) def __eq__(self, other): return self.fqdn == other.fqdn def __hash__(self): return hash(self.fqdn)
def upgrade(): # ### commands auto generated by Alembic - please adjust! ### op.create_table('people', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('name', sa.String(length=64), nullable=False), sa.Column('identity', sa.String(length=64), nullable=False), sa.Column('referrer_id', sa.String(length=16), nullable=True), sa.Column('privacy', sa.String(length=8), server_default='public', nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'disabled', 'suspended'), nullable=False), sa.ForeignKeyConstraint(['referrer_id'], ['people.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('identity') ) op.create_table('time_zone_name', sa.Column('id', sa.INTEGER(), nullable=False), sa.Column('name', sa.String(length=32), nullable=False), sa.Column('status', sa.Enum('active', 'disabled'), server_default='active', nullable=False), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('name') ) op.create_table('apikeys', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('name', sa.String(length=64), nullable=False), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('prefix', sa.String(length=8), nullable=False), sa.Column('hashvalue', StringEncryptedType(sa.String(), length=96), nullable=False), sa.Column('expires', sa.TIMESTAMP(), nullable=True), sa.Column('last_used', sa.TIMESTAMP(), nullable=True), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'disabled'), server_default='active', nullable=False), sa.ForeignKeyConstraint(['uid'], ['people.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('prefix'), sa.UniqueConstraint('name', 'uid', name='uniq_apikey_owner'), ) op.create_table('categories', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('name', sa.String(length=64), nullable=False), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'disabled'), server_default='active', nullable=False), sa.ForeignKeyConstraint(['uid'], ['people.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('name', 'uid', name='uniq_category_owner'), ) op.create_table('contacts', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('label', sa.String(length=16), server_default='home', nullable=False), sa.Column('type', sa.String(length=12), server_default='email', nullable=False), sa.Column('carrier', sa.String(length=16), nullable=True), sa.Column('info', sa.String(length=255), nullable=True), sa.Column('muted', sa.BOOLEAN(), server_default=sa.text('0'), nullable=False), sa.Column('rank', sa.INTEGER(), server_default='1', nullable=False), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('privacy', sa.String(length=8), server_default='member', nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'unconfirmed', 'disabled'), server_default='unconfirmed', nullable=False), sa.ForeignKeyConstraint(['uid'], ['people.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('info', 'type', name='uniq_info_type'), ) with op.batch_alter_table('contacts', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_contacts_uid'), ['uid'], unique=False) op.create_table('grants', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('name', sa.String(length=24), nullable=False), sa.Column('value', sa.String(length=64), nullable=False), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('expires', sa.TIMESTAMP(), nullable=True), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'disabled'), server_default='active', nullable=False), sa.ForeignKeyConstraint(['uid'], ['people.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('name', 'uid', name='uniq_grant_user') ) op.create_table( 'locations', sa.Column('id', sa.String(length=16), primary_key=True, unique=True), sa.Column('name', sa.String(length=255)), sa.Column('address', sa.String(length=255)), sa.Column('city', sa.String(length=64), nullable=False), sa.Column('state', sa.String(length=16)), sa.Column('postalcode', sa.String(length=12)), sa.Column('country', sa.String(length=2), nullable=False, server_default=Constants.DEFAULT_COUNTRY), # Unsupported by MariaDB, alas # sa.Column('geo', Geometry(geometry_type='POINT', management=True)), sa.Column('geolat', sa.INTEGER()), sa.Column('geolong', sa.INTEGER()), sa.Column('neighborhood', sa.String(length=32)), sa.Column('privacy', sa.String(length=8), nullable=False, server_default=u"public"), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('category_id', sa.String(length=16), nullable=False), sa.Column('created', sa.TIMESTAMP(), nullable=False, server_default=sa.func.now()), sa.Column('modified', sa.TIMESTAMP()), sa.Column('status', sa.Enum('active', u'disabled'), nullable=False), sa.ForeignKeyConstraint(['uid'], [u'people.id']), sa.ForeignKeyConstraint(['category_id'], [u'categories.id']), ) op.create_table('profileitems', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('item', sa.String(length=16), nullable=False), sa.Column('value', sa.String(length=96), nullable=True), sa.Column('location_id', sa.String(length=16), nullable=True), sa.Column('tz_id', sa.INTEGER(), nullable=True), sa.Column('privacy', sa.String(length=8), server_default='public', nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'disabled'), nullable=False), sa.ForeignKeyConstraint(['location_id'], ['locations.id'], ), sa.ForeignKeyConstraint(['tz_id'], ['time_zone_name.id'], ), sa.ForeignKeyConstraint(['uid'], ['people.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('uid', 'item', name='uniq_itemuid') ) op.create_table('lists', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('name', sa.String(length=64), nullable=False), sa.Column('description', sa.TEXT(), nullable=True), sa.Column('privacy', sa.String(length=8), server_default='public', nullable=False), sa.Column('category_id', sa.String(length=16), nullable=False), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'disabled'), nullable=False), sa.ForeignKeyConstraint(['category_id'], ['categories.id'], ), sa.ForeignKeyConstraint(['uid'], ['people.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('name', 'uid', name='uniq_list_owner') ) op.create_table('listmembers', sa.Column('uid', sa.String(length=16), primary_key=True, nullable=False), sa.Column('list_id', sa.String(length=16), primary_key=True, nullable=False), sa.Column('authorization', sa.String(length=8), server_default='member', nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.ForeignKeyConstraint(['list_id'], ['lists.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['uid'], ['people.id'], ondelete='CASCADE'), ) with op.batch_alter_table('listmembers', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_listmembers_list_id'), ['list_id'], unique=False) batch_op.create_unique_constraint('uniq_listmember', ['list_id', 'uid']) op.create_table('settings', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('name', sa.String(length=32), nullable=False), sa.Column('privacy', sa.String(length=8), server_default='public', nullable=False), sa.Column('smtp_port', sa.INTEGER(), server_default='587', nullable=False), sa.Column('smtp_smarthost', sa.String(length=255), nullable=True), sa.Column('smtp_credential_id', sa.String(length=16), nullable=True), sa.Column('country', sa.String(length=2), server_default='US', nullable=False), sa.Column('lang', sa.String(length=6), server_default='en_US', nullable=False), sa.Column('tz_id', sa.INTEGER(), server_default='598', nullable=False), sa.Column('url', sa.String(length=255), nullable=True), sa.Column('window_title', sa.String(length=127), server_default='Example apicrud Application', nullable=True), sa.Column('default_cat_id', sa.String(length=16), nullable=False), sa.Column('administrator_id', sa.String(length=16), nullable=False), sa.Column('default_hostlist_id', sa.String(length=16), nullable=True), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.ForeignKeyConstraint(['administrator_id'], ['people.id'], ), sa.ForeignKeyConstraint(['default_cat_id'], ['categories.id'], ), sa.ForeignKeyConstraint(['default_hostlist_id'], ['lists.id'], ), sa.ForeignKeyConstraint(['tz_id'], ['time_zone_name.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id') ) op.create_table('accounts', sa.Column('id', sa.String(length=16), nullable=False, primary_key=True, unique=True), sa.Column('name', sa.String(length=32), nullable=False), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('password', StringEncryptedType(sa.String(), length=128), nullable=False), sa.Column('password_must_change', sa.BOOLEAN(), server_default=sa.text('0'), nullable=False), sa.Column('totp_secret', StringEncryptedType(sa.String(), length=64), nullable=True), sa.Column('backup_codes', sa.LargeBinary(length=256)), sa.Column('is_admin', sa.BOOLEAN(), server_default=sa.text('0'), nullable=False), sa.Column('settings_id', sa.String(length=16), nullable=False), sa.Column('last_login', sa.TIMESTAMP(), nullable=True), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'disabled'), nullable=False), sa.ForeignKeyConstraint(['settings_id'], ['settings.id'], ), sa.ForeignKeyConstraint(['uid'], ['people.id'], ondelete='CASCADE'), sa.UniqueConstraint('id', 'uid', name='uniq_account_user'), sa.UniqueConstraint('name'), sa.UniqueConstraint('uid') ) op.create_table('scopes', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('name', sa.String(length=32), nullable=False), sa.Column('settings_id', sa.String(length=16), nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'disabled'), server_default='active', nullable=False), sa.ForeignKeyConstraint(['settings_id'], ['settings.id'], ), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('name', 'settings_id', name='uniq_scope_name'), ) op.create_table('apikeyscopes', sa.Column('apikey_id', sa.String(length=16), primary_key=True, nullable=False), sa.Column('scope_id', sa.String(length=16), primary_key=True, nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.ForeignKeyConstraint(['apikey_id'], ['apikeys.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['scope_id'], ['scopes.id'], ondelete='CASCADE'), ) with op.batch_alter_table('apikeyscopes', schema=None) as batch_op: batch_op.create_index(batch_op.f('ix_apikey_scope_id'), ['apikey_id'], unique=False) batch_op.create_unique_constraint('uniq_apikeyscope', ['apikey_id', 'scope_id']) op.create_table( 'credentials', sa.Column('id', sa.String(length=16), primary_key=True, nullable=False), sa.Column('name', sa.String(length=64), nullable=False), sa.Column('vendor', sa.String(length=32), nullable=False), sa.Column('type', sa.String(length=16), nullable=True), sa.Column('url', sa.String(length=64), nullable=True), sa.Column('key', sa.String(length=128), nullable=True), sa.Column('secret', StringEncryptedType(sa.String(), length=128), nullable=True), sa.Column('otherdata', StringEncryptedType(sa.String(), length=1024), nullable=True), sa.Column('expires', sa.TIMESTAMP(), nullable=True), sa.Column('settings_id', sa.String(length=16), nullable=False), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum(u'active', u'disabled'), nullable=False, server_default='active'), sa.ForeignKeyConstraint(['uid'], ['people.id'], ondelete='CASCADE'), sa.ForeignKeyConstraint(['settings_id'], [u'settings.id']), sa.UniqueConstraint('id'), sa.UniqueConstraint('name', 'uid', name='uniq_name_uid') ) with op.batch_alter_table('settings', schema=None) as batch_op: batch_op.create_foreign_key('settings_fk1', 'credentials', ['smtp_credential_id'], ['id']) ############################################### # Tables and column additions for media service ############################################### op.create_table('storageitems', sa.Column('id', sa.String(length=16), nullable=False), sa.Column('name', sa.String(length=32), nullable=False), sa.Column('prefix', sa.String(length=128), nullable=True), sa.Column('bucket', sa.String(length=64), nullable=False), sa.Column('region', sa.String(length=16), server_default='us-east-2', nullable=True), sa.Column('cdn_uri', sa.String(length=64), nullable=True), sa.Column('identifier', sa.String(length=64), nullable=True), sa.Column('privacy', sa.String(length=8), server_default='public', nullable=False), sa.Column('credentials_id', sa.String(length=16), nullable=True), sa.Column('uid', sa.String(length=16), nullable=False), sa.Column('created', sa.TIMESTAMP(), server_default=sa.text('(CURRENT_TIMESTAMP)'), nullable=False), sa.Column('modified', sa.TIMESTAMP(), nullable=True), sa.Column('status', sa.Enum('active', 'disabled'), server_default='active', nullable=False), sa.ForeignKeyConstraint(['credentials_id'], ['credentials.id'], ), sa.ForeignKeyConstraint(['uid'], ['people.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id'), sa.UniqueConstraint('id'), sa.UniqueConstraint('name', 'uid', name='uniq_storage_user') ) with op.batch_alter_table('settings', schema=None) as batch_op: batch_op.add_column(sa.Column('default_storage_id', sa.String(length=16), nullable=True)) batch_op.create_foreign_key('settings_fk2', 'storageitems', ['default_storage_id'], ['id']) """
def get_encrypt_key_type(self, registry, sqlalchemy_type, encrypt_key): sqlalchemy_type = StringEncryptedType(sqlalchemy_type, encrypt_key) if sgdb_in(registry.engine, ['MySQL', 'MariaDB']): sqlalchemy_type.impl = types.Text() return sqlalchemy_type