def get_promotion_blacklist(request: Request,
username: str = None,
min_id: int = None,
max_id: int = None,
limit: int = 10):
"""Get up to the given limit number of users which match the given criteria
and are barred from being promoted.
This requires the `view-others-trust` permission to include results that
aren't the authenticated user and the `view-self-trust` reason to include
the authenticated user in the response. The mod username will always be
replaced with `LoansBot`. Permissions are not consistently checked and this
information is not considered secure, however these permissions are used to
avoid this endpoint being used in browser extensions as it's not intended for
that purpose.
The reason is replaced with users trust status, so for example "unknown" or
"bad". Users with the trust status "good" are not returned in this endpoint.
This attempts to emulate the behavior of the promotion blacklist within the
new improved trust system.
Arguments:
- `username (str, None)`: If specified only users which match the given username
with an ILIKE query will be returned.
- `min_id (int, None)`: If specified only TRUSTS which have an id of the given value
or higher will be returned. This can be used to walk trusts.
- `max_id (int, None)`: If specified only TRUSTS which have an id of the given value
or lower will be returned. This can be used to walk trusts.
- `limit (int)`: The maximum number of results to return. For users for which the
global ratelimit is applied this is restricted to 3 or fewer. For other users this
has no explicit limit but does linearly increase the request cost.
Result:
```json
{
"result_type": "PROMOTION_BLACKLIST"
"success": true,
"list": [
{
id: 1,
username: "******",
mod_username: "******",
reason: "some text here",
added_at: <utc milliseconds>
},
...
]
}
```
"""
with LazyItgs() as itgs:
auth = find_bearer_token(request)
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, auth, (trusts.helper.VIEW_OTHERS_TRUST_PERMISSION,
trusts.helper.VIEW_SELF_TRUST_PERMISSION,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
resp = try_handle_deprecated_call(itgs, request, SLUG, user_id=user_id)
if resp is not None:
return resp
if limit <= 0:
limit = 10
settings = get_settings(itgs, user_id) if user_id is not None else None
if limit > 3 and (settings is None
or settings.global_ratelimit_applies):
# Avoid accidentally blowing through the global ratelimit
limit = 3
request_cost = 7 * limit
headers = {'x-request-cost': str(request_cost)}
if not ratelimit_helper.check_ratelimit(
itgs, user_id, perms, request_cost, settings=settings):
return JSONResponse(content=RATELIMIT_RESPONSE.dict(),
status_code=429,
headers=headers)
can_view_others_trust = trusts.helper.VIEW_OTHERS_TRUST_PERMISSION in perms
can_view_self_trust = trusts.helper.VIEW_SELF_TRUST_PERMISSION in perms
if not can_view_others_trust and not can_view_self_trust:
headers['Cache-Control'] = 'no-store'
headers['Pragma'] = 'no-cache'
return JSONResponse(content=ResponseFormat(list=[]).dict(),
status_code=200,
headers=headers)
if can_view_others_trust and can_view_self_trust:
headers['Cache-Control'] = 'public, max-age=600'
else:
headers['Cache-Control'] = 'no-store'
headers['Pragma'] = 'no-cache'
headers['x-can-view-others-trust'] = str(can_view_others_trust)
headers['x-can-view-self-trust'] = str(can_view_self_trust)
usrs = Table('users')
trsts = Table('trusts')
query = (Query.from_(trsts).select(
trsts.id, usrs.username, trsts.status,
trsts.created_at).join(usrs).on(usrs.id == trsts.user_id).where(
trsts.status != Parameter('$1')).limit('$2'))
args = ['good', limit]
if username is not None:
query = query.where(
usrs.username.ilike(Parameter(f'${len(args) + 1}')))
args.append(username)
if min_id is not None:
query = query.where(trsts.id >= Parameter(f'${len(args) + 1}'))
args.append(min_id)
if max_id is not None:
query = query.where(trsts.id <= Parameter(f'${len(args) + 1}'))
args.append(max_id)
if not can_view_self_trust:
query = query.where(usrs.id != Parameter(f'${len(args) + 1}'))
args.append(user_id)
if not can_view_others_trust:
query = query.where(usrs.id == Parameter(f'${len(args) + 1}'))
args.append(user_id)
itgs.read_cursor.execute(*convert_numbered_args(query.get_sql(), args))
denylist = []
row = itgs.read_cursor.fetchone()
while row is not None:
(trust_id, username, status, trust_created_at) = row
denylist.append(
ResponseEntry(id=trust_id,
username=username,
reason=status,
added_at=(trust_created_at.timestamp() * 1000)))
row = itgs.read_cursor.fetchone()
return JSONResponse(content=ResponseFormat(list=denylist).dict(),
status_code=200,
headers=headers)
def index_loans(request: Request,
id: int = 0,
after_time: int = 0,
before_time: int = 0,
borrower_id: int = 0,
lender_id: int = 0,
includes_user_id: int = 0,
borrower_name: str = '',
lender_name: str = '',
includes_user_name: str = '',
principal_cents: int = 0,
principal_repayment_cents: int = -1,
unpaid: int = -1,
repaid: int = -1,
format: int = 2,
limit: int = 10):
id = _zero_to_none(id)
after_time = _zero_to_none(after_time)
before_time = _zero_to_none(before_time)
borrower_id = _zero_to_none(borrower_id)
lender_id = _zero_to_none(lender_id)
includes_user_id = _zero_to_none(includes_user_id)
borrower_name = _blank_to_none(borrower_name)
lender_name = _blank_to_none(lender_name)
includes_user_name = _blank_to_none(includes_user_name)
principal_cents = _zero_to_none(principal_cents)
principal_repayment_cents = _neg1_to_none(principal_repayment_cents)
unpaid = _neg1_to_none(unpaid)
repaid = _neg1_to_none(repaid)
limit = _zero_to_none(limit)
attempt_request_cost = 5
headers = {'x-request-cost': str(attempt_request_cost)}
with LazyItgs() as itgs:
auth = find_bearer_token(request)
user_id, _, perms = users.helper.get_permissions_from_header(
itgs, auth, ratelimit_helper.RATELIMIT_PERMISSIONS)
resp = try_handle_deprecated_call(itgs, request, SLUG, user_id=user_id)
if resp is not None:
return resp
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
attempt_request_cost):
return JSONResponse(content=RATELIMIT_RESPONSE.dict(),
status_code=429,
headers=headers)
if limit is not None and (limit < 0 or limit >= 1000):
return JSONResponse(content=PHPErrorResponse(errors=[
PHPError(
error_type='INVALID_PARAMETER',
error_message=(
'Limit must be 0 or a positive integer less than 1000'
))
]).dict(),
status_code=400)
if limit is None and user_id is None:
headers[
'x-limit-warning'] = 'unauthed requests limit=0 replaced with limit=100'
limit = 100
if format not in (0, 1, 2, 3):
return JSONResponse(content=PHPErrorResponse(errors=[
PHPError(error_type='INVALID_PARAMETER',
error_message=('Format must be 0, 1, 2, or 3'))
]).dict(),
status_code=400)
loans = Table('loans')
if limit is None:
real_request_cost = 100
else:
real_request_cost = min(100, limit)
if format == 0:
real_request_cost = math.ceil(math.log(real_request_cost + 1))
elif format < 3:
# Cost needs to be greater than loans show
real_request_cost = 25 + real_request_cost * 2
else:
# We need to ensure the cost is greater than using the /users show
# endpoint for getting usernames
real_request_cost = 25 + math.ceil(real_request_cost * 4.1)
headers['x-request-cost'] = str(attempt_request_cost +
real_request_cost)
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
real_request_cost):
return JSONResponse(content=RATELIMIT_RESPONSE.dict(),
status_code=429,
headers=headers)
moneys = Table('moneys')
principals = moneys.as_('principals')
principal_repayments = moneys.as_('principal_repayments')
usrs = Table('users')
lenders = usrs.as_('lenders')
borrowers = usrs.as_('borrowers')
query = (Query.from_(loans).where(loans.deleted_at.isnull()).orderby(
loans.id, order=Order.desc))
params = []
joins = set()
def _add_param(val):
params.append(val)
return Parameter(f'${len(params)}')
def _ensure_principals():
nonlocal query
if 'principals' in joins:
return
joins.add('principals')
query = (query.join(principals).on(
principals.id == loans.principal_id))
def _ensure_principal_repayments():
nonlocal query
if 'principal_repayments' in joins:
return
joins.add('principal_repayments')
query = (query.join(principal_repayments).on(
principal_repayments.id == loans.principal_repayment_id))
def _ensure_lenders():
nonlocal query
if 'lenders' in joins:
return
joins.add('lenders')
query = (query.join(lenders).on(lenders.id == loans.lender_id))
def _ensure_borrowers():
nonlocal query
if 'borrowers' in joins:
return
joins.add('borrowers')
query = (query.join(borrowers).on(
borrowers.id == loans.borrower_id))
if id is not None:
query = query.where(loans.id == _add_param(id))
if after_time is not None:
query = (query.where(loans.created_at > _add_param(
datetime.fromtimestamp(after_time / 1000.0))))
if before_time is not None:
query = (query.where(loans.created_at < _add_param(
datetime.fromtimestamp(before_time / 1000.0))))
if principal_cents is not None:
_ensure_principals()
query = (query.where(
principals.amount_usd_cents == _add_param(principal_cents)))
if principal_repayment_cents is not None:
_ensure_principal_repayments()
query = (query.where(principal_repayments.amount_usd_cents ==
_add_param(principal_repayment_cents)))
if borrower_id is not None:
query = (query.where(loans.borrower_id == _add_param(borrower_id)))
if lender_id is not None:
query = (query.where(loans.lender_id == _add_param(lender_id)))
if includes_user_id is not None:
prm = _add_param(includes_user_id)
query = (query.where((loans.borrower_id == prm)
| (loans.lender_id == prm)))
if borrower_name is not None:
_ensure_borrowers()
query = (query.where(
borrowers.username == _add_param(borrower_name.lower())))
if lender_name is not None:
_ensure_lenders()
query = (query.where(
lenders.username == _add_param(lender_name.lower())))
if includes_user_name is not None:
_ensure_lenders()
_ensure_borrowers()
prm = _add_param(includes_user_name)
query = (query.where((lenders.username == prm)
| (borrowers.username == prm)))
if unpaid is not None:
if unpaid:
query = query.where(loans.unpaid_at.notnull())
else:
query = query.where(loans.unpaid_at.isnull())
if repaid is not None:
if repaid:
query = query.where(loans.repaid_at.notnull())
else:
query = query.where(loans.repaid_at.isnull())
if limit is not None:
query = query.limit(limit)
query = query.select(loans.id)
if format > 0:
_ensure_principals()
_ensure_principal_repayments()
event_tables = (Table('loan_repayment_events'),
Table('loan_unpaid_events'),
Table('loan_admin_events'))
latest_events = Table('latest_events')
query = (query.with_(
Query.from_(loans).select(
loans.id.as_('loan_id'),
Greatest(loans.created_at,
*(tbl.created_at for tbl in event_tables
)).as_('latest_event_at')).groupby(loans.id),
'latest_events').left_join(latest_events).on(
latest_events.loan_id == loans.id).select(
loans.lender_id, loans.borrower_id,
principals.amount_usd_cents,
principal_repayments.amount_usd_cents, (Case().when(
loans.unpaid_at.isnull(), 'false').else_('true')),
Cast(
Extract('epoch', loans.created_at) * 1000,
'bigint'),
Cast(
Extract('epoch', latest_events.latest_event_at) *
1000, 'bigint')))
if format == 3:
creation_infos = Table('loan_creation_infos')
_ensure_borrowers()
_ensure_lenders()
query = (query.join(creation_infos).on(
creation_infos.loan_id == loans.id).select(
Function('SUBSTRING', creation_infos.parent_fullname,
4),
Function('SUBSTRING', creation_infos.comment_fullname,
4), lenders.username, borrowers.username))
sql, args = convert_numbered_args(query.get_sql(), params)
headers['Cache-Control'] = 'public, max-age=600'
if format == 0:
return _UltraCompactResponse((sql, args), headers,
'LOANS_ULTRACOMPACT')
elif format == 1:
return _CompactResponse((sql, args), headers, 'LOANS_COMPACT')
elif format == 2:
return _StandardResponse((sql, args), headers, 'LOANS_STANDARD')
else:
return _ExtendedResponse((sql, args), headers, 'LOANS_EXTENDED')
def lookup(reason: str,
captcha: str,
limit: int = None,
next_id: int = None,
email: str = None,
name: str = None,
street_address: str = None,
city: str = None,
state: str = None,
zip: str = None,
country: str = None,
authorization=Header(None)):
"""Searches for users matching the given description. This operation is
aggressively logged and may result in alerting multiple people. At least
one filter must be specified; filters are compared using the ILIKE
operator.
"""
if authorization is None:
return Response(status_code=401)
if limit is None:
limit = 5
elif limit <= 0:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['limit'],
'msg': 'must be positive',
'type': 'range_error'
}
})
elif limit > 10:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['limit'],
'msg': 'must be <=10',
'type': 'range_error'
}
})
if len(reason) < 3:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['reason'],
'msg': 'must be >=3 chars',
'type': 'range_error'
}
})
if sum([
1 if (s is not None and len(s) > 1) else 0
for s in [email, name, street_address, city, state, zip, country]
]) == 0:
return Response(status_code=400)
attempt_request_cost = 5
success_request_cost = 95 + 25 * limit
with LazyItgs(no_read_only=True) as itgs:
auth = demographics_helper.get_failure_response_or_user_id_and_perms_for_authorization(
itgs, authorization, attempt_request_cost, None, None,
demographics_helper.LOOKUP_DEMOGRAPHICS_PERMISSION, [])
if isinstance(auth, Response):
return auth
(user_id, perms) = auth
headers = {'x-request-cost': str(attempt_request_cost)}
if not security.verify_captcha(itgs, captcha):
return Response(status_code=403, headers=headers)
headers['x-request-cost'] = str(attempt_request_cost +
success_request_cost)
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
success_request_cost):
return Response(status_code=429, headers=headers)
demos = Table('user_demographics')
query = (Query.from_(demos).select(
demos.id, demos.user_id, demos.email, demos.name,
demos.street_address, demos.city, demos.state, demos.zip,
demos.country).where(demos.deleted == Parameter('$1')).orderby(
demos.id).limit(Parameter('$2')))
args = [False, limit + 1]
if next_id is not None:
query = query.where(demos.id > Parameter(f'${len(args) + 1}'))
args.append(next_id)
search_fields = [(email, demos.email), (name, demos.name),
(street_address, demos.street_address),
(city, demos.city), (state, demos.state),
(zip, demos.zip), (country, demos.country)]
for (query_param, field) in search_fields:
if query_param is not None:
query = query.where(field.ilike(
Parameter(f'${len(args) + 1}')))
args.append(query_param)
itgs.read_cursor.execute(*convert_numbered_args(query.get_sql(), args))
result = []
have_more = False
last_id = None
row = itgs.read_cursor.fetchone()
while row is not None:
(row_id, row_user_id, row_email, row_name, row_street_address,
row_city, row_state, row_zip, row_country) = row
if len(result) >= limit:
have_more = True
row = itgs.read_cursor.fetchone()
continue
last_id = row_id
result.append(
demographics_models.UserDemographics(
user_id=row_user_id,
email=row_email,
name=row_name,
street_address=row_street_address,
city=row_city,
state=row_state,
zip=row_zip,
country=row_country))
row = itgs.read_cursor.fetchone()
lookups = Table('user_demographic_lookups')
itgs.write_cursor.execute(
Query.into(lookups).columns(
lookups.admin_user_id, lookups.email, lookups.name,
lookups.street_address, lookups.city, lookups.state,
lookups.zip, lookups.country,
lookups.reason).insert(*[Parameter('%s')
for _ in range(9)]).returning(
lookups.id).get_sql(),
(user_id, email, name, street_address, city, state, zip, country,
reason))
(lookup_id, ) = itgs.write_cursor.fetchone()
views = Table('user_demographic_views')
itgs.write_cursor.execute(
Query.into(views).columns(views.user_id, views.admin_user_id,
views.lookup_id).
insert(*[tuple(Parameter('%s') for _ in range(3))
for _ in result]).get_sql(),
tuple(
itertools.chain.from_iterable(
(demo.user_id, user_id, lookup_id) for demo in result)))
itgs.write_conn.commit()
headers['Cache-Control'] = 'no-store'
headers['Pragma'] = 'no-cache'
return JSONResponse(
status_code=200,
content=demographics_models.UserDemographicsLookup(
hits=result, next_id=(last_id if have_more else None)).dict())
def index_user_history(req_user_id: int,
limit: int = 25,
before_id: int = None,
authorization=Header(None)):
if authorization is None:
return Response(status_code=401)
if limit <= 0:
return JSONResponse(status_code=422,
content={
'detail': {
'loc': ['limit'],
'msg': 'Must be positive',
'type': 'range_error'
}
})
request_cost = max(1, math.log(limit))
headers = {'x-request-cost': str(request_cost)}
with LazyItgs() as itgs:
user_id, _, perms = helper.get_permissions_from_header(
itgs, authorization,
(settings_helper.VIEW_OTHERS_SETTINGS_PERMISSION,
*ratelimit_helper.RATELIMIT_PERMISSIONS))
if not ratelimit_helper.check_ratelimit(itgs, user_id, perms,
request_cost):
return Response(status_code=429, headers=headers)
if user_id is None:
return Response(status_code=404, headers=headers)
can_see_others_settings = settings_helper.VIEW_OTHERS_SETTINGS_PERMISSION in perms
users = Table('users')
if req_user_id != user_id:
if not can_see_others_settings:
return Response(status_code=404, headers=headers)
itgs.read_cursor.execute(
Query.from_(users).select(1).where(
users.id == Parameter('%s')).get_sql(), (req_user_id, ))
if itgs.read_cursor.fetchone() is None:
return Response(status_code=404, headers=headers)
events = Table('user_settings_events')
query = (Query.from_(events).select(
events.id).where(events.user_id == Parameter('$1')).limit(
Parameter('$2')).orderby(events.id, order=Order.desc))
args = [req_user_id, limit + 1]
if before_id is not None:
query = query.where(events.id < Parameter('$3'))
args.append(before_id)
itgs.read_cursor.execute(*convert_numbered_args(query.get_sql(), args))
result = []
have_more = False
row = itgs.read_cursor.fetchone()
while row is not None:
if len(result) < limit:
result.append(row[0])
else:
have_more = True
row = itgs.read_cursor.fetchone()
# Not cacheable; inserting an item at the front breaks all the pages
headers['Cache-Control'] = 'no-store'
return JSONResponse(status_code=200,
content=settings_models.UserSettingsHistory(
before_id=(min(result) if result else None)
if have_more else None,
history=result).dict(),
headers=headers)
def execute_get_missing_alerts(itgs, bonus_filters):
"""Executes the read to get all alerts which should have been sent out already
for endpoint usage. The endpoint users is filtered using `bonus_filters`.
If `bonus_filters` is a no-op then this function will return one row
for each use of an endpoint by any user for which there is no later alert.
The result is sorted by user id.
Arguments:
- `itgs (LazyIntegrations)`: The integrations to use for sending alerts.
- `bonus_filters (callable)`: A callable which accepts the query, a
callable which accepts an argument and returns the Parameter which will
refer to that argment, and keyword arguments for each Table reference we
have. This should return the new Query to use after filtering the results.
Returns (via `itgs.read_cursor.fetchall()`):
- `rows (list)`: A list of lists, where each inner list has the following
elements:
- `user_id (int)`: The id of the user which should be sent an alert.
- `username (str)`: The username of the user with id `user_id`.
- `endpoint_id (int)`: The id of the endpoint the user used
- `endpoint_slug (str)`: The slug of the endpoint with id `endpoint_id`
- `first_use_in_interval (datetime)`: The earliest time within the
interval that the user used the endpoint.
- `last_use_in_interval (datetime)`: The latest time within the interval
that the user used the endpoint.
- `count_in_interval (int)`: The number of times the user used the endpoint
within the interval.
"""
endpoint_users = Table('endpoint_users')
endpoint_alerts = Table('endpoint_alerts')
most_recent_alerts = Table('most_recent_alerts')
usage_after_filters = Table('usage_after_filters')
users = Table('users')
args = []
def add_param(arg):
args.append(arg)
return Parameter(f'${len(args)}')
query = (Query.with_(
Query.from_(endpoint_alerts).select(
endpoint_alerts.endpoint_id.as_('endpoint_id'),
endpoint_alerts.user_id.as_('user_id'),
Max(endpoint_alerts.sent_at).as_('max_sent_at')).groupby(
endpoint_alerts.endpoint_id,
endpoint_alerts.user_id), 'most_recent_alerts'
).with_(
bonus_filters(Query.from_(endpoint_users).join(most_recent_alerts).on(
(most_recent_alerts.endpoint_id == endpoint_users.endpoint_id)
& (most_recent_alerts.user_id == endpoint_users.user_id)).select(
endpoint_users.endpoint_id.as_('endpoint_id'),
endpoint_users.user_id.as_('user_id'),
Min(endpoint_users.created_at).as_('first_usage'),
Max(endpoint_users.created_at).as_('last_usage'),
Count(endpoint_users.id).as_('count_usage')).where(
endpoint_users.user_id.notnull()).where(
endpoint_users.created_at >
most_recent_alerts.max_sent_at).groupby(
endpoint_users.endpoint_id,
endpoint_users.user_id),
add_param,
endpoint_users=endpoint_users,
endpoint_alerts=endpoint_alerts,
most_recent_alerts=most_recent_alerts),
'usage_after_filters').from_(usage_after_filters).join(users).on(
users.id == usage_after_filters.user_id).select(
usage_after_filters.user_id, users.username,
usage_after_filters.endpoint_id,
usage_after_filters.first_usage,
usage_after_filters.last_usage,
usage_after_filters.count_usage).orderby(
usage_after_filters.user_id))
(sql, ordered_args) = convert_numbered_args(query.get_sql(), args)
itgs.read_cursor.execute(sql, ordered_args)