Schema changes on sharded database

I have performance issues with one particulary large table, 500+ Million rows, 300Gb data, Postgres 10.5. It is already partitioned. I am working on optimising it here and there, but that is not trivial and only provides small improvements. Table is constanlty growing and we expect our userbase increace significantly so I need a way to scale up.

I want to use multi-tenant sharding approach. X tenants per shard. Shard resolving on app layer. Most of tenants have relateively small datasets, but few are huge and I want to be able to place them to separate shards . To do that I need lookup tables. Cross-shard queries are not concern at all, naturaly we have almost all of our queris per tenant, so all the data for the tenant will sit in same shard.

I will be using logical sharding, 4 phisical shards x32 logical (that is twice more shards than partitions currenly). Each logical shard is separate database. In most tutorials/talks people seem to use schemas instead of databases. Why? Databases are more isolated, and when moving single tenant or virtual shard to other location it does not seem to have any difference. So db looks like a better candidate to me.

Drawbacks look acceptable: update existing code (significantly), app should be shard aware

The question is: How do I handle migrations(schema changes)?

As first step I will have to create 128 databases, ensuring all of them have all tables, indexes, etc. I also want each of dbs have its own sequences to have ids unique accross all shards. Not trivial to me.

But further changes are problem aswell. Do I just iterate all connections and aplly changes? Is there a better (maybe async) way? What do I do if at some point shema in one shard is different from another.

Is this schema between a Desktop App and an API secure?

From my previous question: How secure is this schema between a Desktop App (c++) and an API (php).

To make it short: Client logs in using the Desktop App (and receives a JWT) and every X seconds/minutes the Desktop App sends this JWT to see if it’s still valid. No webpage is involved, everything is done between the Desktop App sending POST requests and the API answering.

I have 2 .php files: check.php (it’s the one that receives the Requests every X seconds/minutes) and login.php (used once to log in everytime the user opens the Desktop App).

Login.php

Receive username, password and a random value. Check if username and password are okay, then generate and store in DB a JWT using the random value and SharedSecret_1. Send the JWT back to the Desktop App so it can check if it’s valid and proceed to let the user use the software.

Check.php

Receive a JWT and a random value. Check if the JWT is the same than the one in the DB then generate and store in DB a new JWT using the random value and SharedSecret_2. Send the JWT back to the Desktop App so it can check if it’s valid.


I had 4 main problems:

  1. How to make sure the Desktop App knows if the JWT it receives is valid and not faked by the user.
  2. How to make sure the API knows if the data sent by the Desktop App is valid and not faked.
  3. The API needs to send a unique JWT everytime because if it’s always “Y” then user would be able to forward the data send by the Desktop App and fake an answer “Y”.
  4. I don’t want multiple users using the same account at the same time. Only 1 connection per account. (Like in an online game were if someone logs in while you are logged in, you get kicked).

From the answer I came with this idea:

  • Desktop App signs data before sending it solving 2nd problem. (Is it secure if the Desktop App sends the data with a JWT using a SharedSecret?)
  • Both Desktop App and API share a Secret Key (would be different in case I can sign data client-side using JWT) that the API will use to generate the JWT and the Desktop App will use to verify this JWT. Solving 1st problem.
  • A random value is sent by the Desktop App (along the JWT) everytime it performs the checks so the API uses it to generate a different JWT.
  • I solve problem 4 Using different SharedSecrets for “login” and “check”. If a user forwards it to login, it will generate a token with SharedSecret_1 and when the Desktop App verifies this token it will use SharedSecret_2 making it invalid.

My questions are:

  1. Is this approach secure?
  2. Can this random value be known by anyone without risking security? (Because the user would still need the shared secret to be able to generate a valid JWT)
  3. Should the random value be sent inside the JWT?
  4. How “random” should this value be? Is it okay if it’s a simple number from 1 to 100000?

When I’m asking about security I mean against piracy, preventing users from accessing my paid app for free. (I know it can’t be 100% secure, but I want it to be as secure as possible). I’m not taking into account what happens if a user reverses my Desktop App because if this happens then the user will simply remove the checks or will know the shared secret.

How secure is this schema between a Desktop App (c++) and an API (php)

as title says I’m writing a paid Desktop App that automates some process, it indirectly requires internet connection because MY app automates another Desktop app that requires internet connection. There is no webpage or such, only my Desktop App and my API.

Normal Workflow:

  1. User opens Desktop App: Login prompt shows on screen (it’s a simple interface written in c++ that then posts to my API).
  2. User presses the “Login” button: Data is sent via POST to my API.
  3. API validates and verifies the data, then generates a JWT and sends it to the user.
  4. User then uses the App all the time he/she wants: Every X seconds the JWT is sent to the API to see if it’s still valid.
  5. The user stops using the App: It might be because the App crashed, because he lost connection or because he closed it.

What I want to achieve:

  1. I need it to be secure for the users and for me (by me I mean I don’t want it to be easily pirated):
    • I’m using HTTPS.
    • I’m hashing the Password before sending it to my API.
    • I’m validating and verifying all the data.
    • I don’t know how to make sure that this data comes from my Desktop App and was not modified in the middle by the user (like replacing an unique identifier with a simple string).
  2. I want to prevent multiple clients logging in using the same account at the same time:
    • I don’t want multiple Apps connected at the same time using the same account. I don’t mind if an user uses the same account on different computers as long as he is not having more than 1 App connected at the same time.
    • When a user logs in with an account it will kill the rest of the “sessions” that this account had. (Like an online game where if you log in with an account that’s already logged in, it will kick the user that was logged in preventing from 2 clients using the same account at the same time).

My schema:

Using JWT, when user “A” logs in it will generate a new JWT using his username and some kind of unique information like IP or computer information.

My Desktop App will verify and store this JWT in memory. Then every X seconds it will do a POST request sending the JWT to see if it’s still valid.

  • If everything is okay then the App will continue to run normally.
  • If the token is not valid anymore or something is wrong it will log out.

Problems I found:

  1. Let’s say User “A” logs in and a JWT is then generated and sent to the Desktop App, how can the Desktop App tell that this JWT is valid and that is not a fake response the user is using so the App thinks it’s logged in?.

  2. What happens if user “A” logs in and then fakes responses every X seconds? I mean, how can I make this response unique? Is it a good fix to generate a new JWT everytime it performs this check?

  3. Let’s say I make it unique using some kind of unique identifier for each client (like IP, computer information, etc.), what if the user intercepts the information and replaces it with something simple like “hello world” so then all clients can use the same account and have a valid token at the same time. How do I prevent user from being able to intercept the data sent?

So my questions are:

  • How do I fix those problems.
  • Is it a good schema? I mean is it secure (to prevent piracy)? (I know nothing is 100% secure, but I don’t want to make an authentication system that can be easily broken or with no security at all).
  • Is JWT a good choice here? Are there alternatives that are “better” or “different”?
  • Do you see any other problems, flaws, bugs, etc.?
  • Do you have another schema or idea to achieve this?

Thanks in advance.

P.S.: I’m not taking into account what happens if the user reverses my Desktop App because that’s another topic and in that case nothing here is important.

l’ll do Shopify Store SEO optimization, Product Tags, Meta Description, Alt Tags and Schema Markup for $20

Shopify Store SEO optimization is the process of optimizing each and every product and page of your store in order to rank higher in the Search Engine Results Pages (SERPS). If you want to enhance Shopify Store ranking, organic web traffic and sales, you simply can’t ignore search engine optimization(SEO). This White Hat on page SEO services can boost google ranking of your Shopify Store or Store traffic by 90% or more. Our on page SEO optimization services include: Free: Shopify Store SEO analysis and Store SEO audit report.LSI, Focused & Long tail keywords Optimization.Install & configure necessary store apps.Compelling Meta descriptions, SEO Meta Titles and Product tags.Heading tags (H₁, H₂, H₃), Image alt tags and Shopify store Speed Optimization.Fix duplicate content & Redirect Broken Links to the Homepage or Parent Page.Social media Meta tags, Hyperlink and anchor text optimization.Search Engines friendly URLs and SEO Titles.Schema Markup, XML Sitemaps, Internal / External Linking and Robots.txt.Webmaster Tools(Google, Bing, Yahoo) & Google analytics. Why us? 100% Client Satisfaction and Money back Guarantee.Increased store authority.Top-ranked website based SEO keywords research.100% White Hat manual work.WordPress SEO, Wix SEO and Shopify Store SEO Specialists.If you have any questions, please don’t hesitate to get in touch. Tags: Shopify SEO, Shopify store SEO, e-commerce store SEO, store optimization, SEO services, Store SEO analysis, Shopify store SEO optimization, Shopify store SEO ranking, Shopify store on page SEO optimization, Shopify store SEO services, Shopify store SEO specialist, Shopify store SEO agency, Shopify store SEO consultant, Product Tags, SEO keyword research, LSI keywords research, Keywords analysis, Shopify store SEO Audit Report, Shopify store SEO audit, 301 redirect, Competitor analysis, Meta descriptions, image alt tags, Shopify store speed optimization, XML sitemap, robots file, hyper link optimization, anchor text optimization, SEO friendly titles, long tail keywords research and Targeted Organic Traffic.

by: ONELIFESEO
Created: —
Category: Local SEO
Viewed: 197


Sync database table schema and stored procedures

We have 2 databases. One is at our server, the other is local. We need to sync them both in a manner that local database has all data and server database has no data only empty tables and stored procedures and functions.

In server database we are changing stored procedures, functions, as well as tables. These changes are to be synchronized into Local DB i.e.

Common at both databases:

table 1 (id, name, price) 

Changes happened in Server DB:

table 1 (id, name, price, IsActive) 

We want IsActive field to be synchronized into local db, same way we need to sync stored procedures and functions.

So in simple words we need a solution to sync stored procedures, functions, and table schema without messing up existing data.

Can any one suggest any tool or code with which we can satisfy the same? With some google and search we found a codeproject article but that can be used for fresh database. Still sharing the link.

Thanks

Schema and Query design for table with multiple languages

I’ve been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and another column(s) with the translated text in that language. So I decided in the following schema:

CREATE TABLE IF NOT EXISTS Meowficer (     meowficer_id SMALLINT PRIMARY KEY,     skill_id SMALLINT UNIQUE NOT NULL,     -- Cut out irrelevant columns. );  CREATE TABLE IF NOT EXISTS MeowficerName (     meowficer_id SMALLINT NOT NULL,     language TEXT NOT NULL,     name TEXT NOT NULL,      FOREIGN KEY(meowficer_id) REFERENCES Meowficer(meowficer_id)  );  CREATE TABLE IF NOT EXISTS MeowficerSkill (     skill_id SMALLINT PRIMARY KEY,     next_id SMALLINT NOT NULL,     level SMALLINT NOT NULL,     experience SMALLINT NOT NULL,     icon TEXT NOT NULL,      -- Only one Skill for every Mewoficer.     FOREIGN KEY(skill_id) REFERENCES Meowficer(skill_id) );  CREATE TABLE IF NOT EXISTS MeowficerSkillNameDesc (     skill_id SMALLINT NOT NULL,     language TEXT NOT NULL,     skill_name TEXT NOT NULL,     skill_1 TEXT NOT NULL,     skill_2 TEXT NOT NULL,     skill_3 TEXT NOT NULL,      FOREIGN KEY (skill_id) REFERENCES MeowficerSkill(skill_id) ); 

And I can query the data I need this way:

-- Cut out part of the column selecting from the Meowficer table. SELECT     Meowficer.id, Meowficer.skill_id, MeowficerName.name, ..., MeowficerSkill.icon, MeowficerSkillNameDesc.skill_name, MeowficerSkillNameDesc.skill_1, MeowficerSkillNameDesc.skill_2, MeowficerSkillNameDesc.skill_3 FROM      Meowficer  JOIN      MeowficerName ON Meowficer.id = MeowficerName.id AND MeowficerName.language = 'ja' AND MeowficerName.name = 'ジャスティス' JOIN      MeowficerSkill ON Meowficer.skill_id = MeowficerSkill.skill_id AND MeowficerSkill.level = 2 JOIN     MeowficerSkillNameDesc ON MeowficerSkill.skill_id = MeowficerSkillNameDesc.skill_id AND MeowficerSkillNameDesc.language = 'ja' 

While the data sample I’m currently working with isn’t large at all and when I tried the code above it worked without any issues, is this the best approach for a multi language database schema and querying? Can it be improved on?

What is the gsettings schema for Settings > Universal Access > Sounds Keys

look I’m using Ubuntu 19.04 and currently trying to enable the sound keys from a command line but not sure where to look for the schema. At first, I thought it was gsettings set org.gnome.desktop.sound event-sounds "true" but I don’t think that was it. I’ve tried to browse for it throughout dconf-editor but still has no idea where it’s at; not to mention googled for the information many times. Thanks in advance!