1 Востаннє редагувалося FakiNyan (12.06.2016 19:26:27)

Тема: Питання по структурі таблиць і самої бд в postgresql

Прів.

1)  При реєстрації користувач вводить всіляку інфу про себе, типу емейла і пароля, і от кожний акаунт може мати один з декількох типів. При реєстрація він має один тип, ну а далі може отримати якийсь інший.
І от я думаю, як ці типи краще зберігати?

Якби я це писав в якійсь мові програмування, то створив би клас enum який тримав би в собі всі можливі типи, а в класі з даними користувача я б тримав поле типу enum. Ну ви зрозуміли.

Чи можна шось таке зробити в БД, і чи потрібно взагалі?

2) Якщо користувач зареєструвався в межах останніх 15 хвилин, і намагається зарегатись з іншими данами але з цього ж айпі, то таку можливість треба заблокувати, аби спамери усілякі не наробили купу акаунтів.

Я так розумію, що для цього потрібно тримати окрему таблицю, котра містила б айпішники і дату, коли з цього айпішника реєструвались в останній раз. Так?

3) Окрім паролю та емейлу, в бд повинна зберігатись остання дата спроби логіну від цього користувача, тому що якщо користувач ввів 5 разів підряд невірний логін, або пароль, то спроба логіну блокується на 15 хвилин.

Я от тільки що зрозумів, що в принципі це обмеження теж не прив'язане до акаунту, а прив'язане до айпі, ага?

То це треба в таблицю з айпішником і часом останньої реєстрації додати поле з часом останньої спроби логіну?

4) ОКрім паролю та емейлу, користувач, пізніше, може заповнити інфу про себе, типу стать, справжнє ім'я, вік, країна і т.д. Під це робити окрему таблицю, де ключове поле буде мати емейл акаунту?

Говоріть українською! Живіть українською! Відчувайте українською!

2 Востаннє редагувалося koala (12.06.2016 18:52:35)

Re: Питання по структурі таблиць і самої бд в postgresql

Відповідаю по мірі прочитання:
1) Або ENUM
https://www.postgresql.org/docs/9.1/sta … -enum.html
або FOREIGN KEY і таблиця видів користувачів
2) Зберігати час і IP реєстрації і робити запит за 15 хвилин. Можливо, в окремій таблиці (типу лога), але не обов'язково. Ще можна таке в щось на кшталт MemCached запхати, це ефективніше.
3) Краще не пароль зберігати, а його хеш. Ну і тут вже чітко вимальовується таблиця лог (час, тип події, IP) з індексами за всіма полями.
4) Додаткова інформація може спокійно лежати в таблиці користувачів, не треба тільки NOT NULL ставити.
І краще не робити користувацьку інформацію ключем - а якщо хтось захоче змінити email?

І ще почитайте щось про реляційні БД і нормальні форми.

Подякували: FakiNyan, 221VOLT, leofun013

3

Re: Питання по структурі таблиць і самої бд в postgresql

читаю оце
https://habrahabr.ru/company/mailru/blog/266811/

Говоріть українською! Живіть українською! Відчувайте українською!
Подякували: leofun011

4

Re: Питання по структурі таблиць і самої бд в postgresql

якого...?
http://puu.sh/pq8fj/732a4ca825.png
http://puu.sh/pq8hK/b6db75cf5f.png

Говоріть українською! Живіть українською! Відчувайте українською!

5

Re: Питання по структурі таблиць і самої бд в postgresql

Так по Ейлеру ж...

Подякували: leofun011

6

Re: Питання по структурі таблиць і самої бд в postgresql

якому ейлеру? я просто провтикав, там основа лагарифму = 2

Говоріть українською! Живіть українською! Відчувайте українською!
Подякували: leofun011

7

Re: Питання по структурі таблиць і самої бд в postgresql

Основа натурального логарифму (а оскільки база вами не вказана, то система вважає що вам потрібен саме натуральний, про що на скриншоті й написано) - число Ейлера.

8

Re: Питання по структурі таблиць і самої бд в postgresql

пойняв-пойняв, вже навіть подивився відюшку про число е, зайвий раз відчув себе бовдуром

Говоріть українською! Живіть українською! Відчувайте українською!
Подякували: leofun011

9 Востаннє редагувалося Djalin (12.06.2016 21:22:40)

Re: Питання по структурі таблиць і самої бд в postgresql

Порада - не зберігайте чистий хеш - соліть, бо було доволі дико коли в комерційному продукті зустрів пароль просто в хеші...

Подякували: FakiNyan, koala, 221VOLT, leofun014

10

Re: Питання по структурі таблиць і самої бд в postgresql

Djalin написав:

Порада - не зберігайте чистий хеш - соліть, бо було доволі дико коли в комерційному продукті зустрів пароль просто в хеші...

а де зберігати саму сіль? в тій самі табличці, що і пароль?

Говоріть українською! Живіть українською! Відчувайте українською!

11

Re: Питання по структурі таблиць і самої бд в postgresql

тільки що пойняв, що якщо ми формуємо хеш таким чином

$hash = $salt;<br>
$hash .= md5($salt.$pass);

то в результаті у нас виходе сіль+хеш, тобто сіль зберігається разом з самим хешем, ліл.
А я хтів був окреме поле під це створювати

Говоріть українською! Живіть українською! Відчувайте українською!

12

Re: Питання по структурі таблиць і самої бд в postgresql

як вам моя чудернацька процедура?

CREATE OR REPLACE FUNCTION add_new_user(
ip_ text, 
email_ text, 
password_ text, 
salt_ text, 
ceo_name_ text,
corp_name_ text,
corp_type_ corp_types,
bank_account_ bigint,
glad_coins_ bigint,
max_units_ int,
cur_units_ int,
account_type_ account_types)
RETURNS void AS $$
 DECLARE
  ref refcursor;
 BEGIN

  INSERT INTO users VALUES(
  DEFAULT,
  email_, 
  salt_||md5(password_||salt_),
  ceo_name_,
  corp_name_,
  corp_type_,
  bank_account_,
  glad_coins_,
  max_units_,
  cur_units_,
  NOW(),
  false,
  account_type_);

  INSERT INTO log VALUES(
  ip_,
  NOW(),
  'REGISTRATION',
  0);
 
 END;
 $$ LANGUAGE plpgsql;
Говоріть українською! Живіть українською! Відчувайте українською!
Подякували: leofun011

13

Re: Питання по структурі таблиць і самої бд в postgresql

в мене таке питання - де зберігати 'налаштування' бд?
Наприклад: заборонено реєструвати більше одного акаунта з одного IP. IP зберігаються в базі даних. І от в тій процедурі, що додає нового користувача в бд, було б непогано відразу й перевіряти - якщо заборонено реєструватись з одного IP двічі, то перевіряємо, чи в таблиці log є такий IP, і якщо є, то нічо не зберігаємо і повертаємо помилку, а якщо нема, то все ок.
І ще, в залежності від різних факторів при реєстрації можуть виникати різні користувацькі помилки, і було б непогано, аби БД відправляла серверу, в таких випадках, якесь кастомне повідомлення.
Наприклад, є якийсь ENUM

enum {
OneAccountPerIP, //те, про що я казав, один акаунт на один IP
WaitForNewAccount, //треба почекати якийсь час, перш ніж можна буде зарегати новий ак з цього IP
OK // реєстрація виконалась успішно
}

І от коли сервер викликає процедуру в БД, то вона йому має повернути одне зі значень цього ENUM'a. Як то зробити мона?

Говоріть українською! Живіть українською! Відчувайте українською!

14 Востаннє редагувалося koala (14.06.2016 21:59:37)

Re: Питання по структурі таблиць і самої бд в postgresql

Процедури, тригери, обмеження (CONSTRAINT/CHECK). Залежно від ситуації.
І взагалі, читайте документацію: https://www.postgresql.org/docs/9.3/sta … tures.html

Подякували: FakiNyan, leofun012

15 Востаннє редагувалося FakiNyan (15.06.2016 08:34:47)

Re: Питання по структурі таблиць і самої бд в postgresql

Щось я туплю з логіном.

1. Юзер відправляє логін та пароль
2. Сервер викликає процедурку в БД, котра приймає той логін і пароль
3. В процедурці виконується запит

 SELECT * FROM users WHERE login=login_ AND ...

А от що далі?
В мене ж зберігається сіль+хеш_пароля_з_сіллю. Як я можу написати далі

AND salt||md5(password||salt)=password_

якщо я не знаю солі?
Поки що єдина ідея - спочатку робити запит лише з перевіркою логіну, а потім вже діставати з отриманого запису хеш паролю, відокремлювати від нього сіль, і вже будувати хеш з паролем від юзеру і цією сіллю, ну і перевіряти

Говоріть українською! Живіть українською! Відчувайте українською!

16

Re: Питання по структурі таблиць і самої бд в postgresql

Або зберігайте одну сіль на систему десь збоку (в конфігураційному файлі чи в окремій табличці) і передавайте в запит, або різні - прямо поруч із паролями. Чесно, навмання не скажу, як безпечніше.
І використовуйте якийсь інший алгоритм для хешів, md5 - потенційно небезпечний.

17

Re: Питання по структурі таблиць і самої бд в postgresql

ну я поки що отак перевіряю все

CREATE OR REPLACE FUNCTION login(email_ text, password_ text)
RETURNS RECORD AS $$
 DECLARE
  myrow users%ROWTYPE;
  ref refcursor;
  salt text;
  hash text;
 BEGIN

    SELECT * INTO myrow FROM users WHERE email=email_;
    salt = substring(myrow.password,0,5);
    hash = salt||md5(password_||salt);

    IF hash=myrow.password THEN
        RETURN myrow;
    ELSE
        RETURN 'ERROR';
    END IF;

 END;
 $$ LANGUAGE plpgsql;

тільки от разом з цим потрібно повертати сам запис з даними про користувача, але при цьому потрібно виключити з запису хеш, та й не зрозуміло, що повертати, коли запису з таким паролем та логіном не знайдеться в табличці, адже просто ERROR повернути не вийде, тому що тип, що повертає ця функція - RECORD.
І навіть якщо повертати запис, то він повертається як колонка з купою даних http://puu.sh/ptjNw/c0c9ee3778.png, так що ще й прийдеться парсити цей рядочок

Говоріть українською! Живіть українською! Відчувайте українською!

18

Re: Питання по структурі таблиць і самої бд в postgresql

гаразд, замінив md5 на

crypt('password', gen_salt('bf'))

, тепер хеш виглядає так

$2a$06$GDnT.FlVx.FommI45zqB.ejk0QH26dqQt4lj4gWJ5Fmjchk2nQId2

Говоріть українською! Живіть українською! Відчувайте українською!

19

Re: Питання по структурі таблиць і самої бд в postgresql

Ви не знаєте, як мені відправити щось назад на сервер, якщо сталося виключення?

INSERT INTO users VALUES(
  DEFAULT,
  email_, 
  crypt('password', gen_salt('bf')),
  ceo_name_,
  corp_name_,
  corp_type_,
  bank_account_,
  glad_coins_,
  max_units_,
  cur_units_,
  NOW(),
  false,
  account_type_);

  INSERT INTO log VALUES(
  ip_,
  NOW(),
  'REGISTRATION',
  0);

   EXCEPTION
    WHEN duplicate_column THEN
        RAISE NOTICE 'email already used';
    WHEN unique_violation THEN
        RAISE NOTICE 'ip already used';

Тут я запихую в таблицю users новий запис, але email у нас unique, і от в EXCEPTION я кажу, що якщо сталася помилка duplicate_column, то я б хтів відправити на сервер якесь повідомлення. Я пробував SELECT - нуль емоцій, пробував RAISE - теж нічого.
Також в цій процедурі я запихую новий запис в таблицю log, але там же Primary Keys на всіх стовбчиках, і якщо я намагаюсь запихнути такий самий IP, який там вже є, то воно матюкається на то. EXCEPTION WHEN unique_violation наче перехоплює це, але знову таки, воно нічого не повертає

Говоріть українською! Живіть українською! Відчувайте українською!

20

Re: Питання по структурі таблиць і самої бд в postgresql

а нє, помилочка вийшла.

WHEN unique_violation THEN

спрацьовує в двох випадках, тобто тепер я навіть не можу вияснити, чи то проблема в IP, чи в Email

Говоріть українською! Живіть українською! Відчувайте українською!