Jak utworzyć losowy ciąg, który będzie pasował do identyfikatora sesji w PostgreSQL?

Chciałbym utworzyć losowy ciąg znaków do weryfikacji sesji przy użyciu PostgreSQL. Wiem, że mogę uzyskać losową liczbę SELECT random(), więc próbowałem SELECT md5(random()), ale to nie działa. W jaki sposób mogę to zrobić?

Proponuję takie proste rozwiązanie:

To dość prosta funkcja, która zwraca losowy ciąg o podanej długości:

Create or replace function random_string(length integer) returns text as
$$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';
  result text := '';
  i integer := 0;
begin
  if length < 0 then
    raise exception 'Given length cannot be less than 0';
  end if;
  for i in 1..length loop
    result := result || chars[1+random()*(array_length(chars, 1)-1)];
  end loop;
  return result;
end;
$$ language plpgsql;

I użycie:

select random_string(15);

Przykładowe dane wyjściowe:

select random_string(15) from generate_series(1,15);

  random_string
-----------------
 5emZKMYUB9C2vT6
 3i4JfnKraWduR0J
 R5xEfIZEllNynJR
 tMAxfql0iMWMIxM
 aPSYd7pDLcyibl2
 3fPDd54P5llb84Z
 VeywDb53oQfn9GZ
 BJGaXtfaIkN4NV8
 w1mvxzX33NTiBby
 knI1Opt4QDonHCJ
 P9KC5IBcLE0owBQ
 vvEEwc4qfV4VJLg
 ckpwwuG8YbMYQJi
 rFf6TchXTO3XsLs
 axdQvaLBitm6SDP
(15 rows)

Możesz naprawić swoją początkową próbę w następujący sposób:

SELECT md5(random()::text);

Znacznie prostsze niż niektóre inne sugestie. :-)

Opierając się na rozwiązaniu Marcina, możesz to zrobić, aby użyć dowolnego alfabetu (w tym przypadku wszystkich 62 znaków alfanumerycznych ASCII):

SELECT array_to_string(array 
       ( 
              select substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', trunc(random() * 62)::integer + 1, 1)
              FROM   generate_series(1, 12)), '');

Możesz uzyskać 128 bitów losowych z UUID. To jest metoda na wykonanie zadania w nowoczesnym PostgreSQL.

CREATE EXTENSION pgcrypto;
SELECT gen_random_uuid();

           gen_random_uuid            
--------------------------------------
 202ed325-b8b1-477f-8494-02475973a28f

Może być wart czytania docs na zbyt UUID

Typ danych uuid przechowuje unikalne identyfikatory uniwersalne (UUID), zgodnie z definicją zawartą w RFC 4122, ISO / IEC 9834-8: 2005 i powiązanych normach. (Niektóre systemy odnoszą się do tego typu danych jako do globalnego unikalnego identyfikatora lub zamiast tego GUID). Identyfikator ten to 128-bitowa liczba generowana przez algorytm wybrany w taki sposób, aby bardzo mało prawdopodobne było, że ten sam identyfikator zostanie wygenerowany przez kogokolwiek innego. w znanym wszechświecie przy użyciu tego samego algorytmu. Dlatego w przypadku systemów rozproszonych identyfikatory te zapewniają lepszą gwarancję niepowtarzalności niż generatory sekwencji, które są unikalne tylko w ramach jednej bazy danych.

Jak rzadkie jest zderzenie z UUID lub możliwe do przewidzenia? Zakładając, że są przypadkowe,

Aby mieć 1 na miliard szans na pojedynczy duplikat („kolizja”), trzeba by wygenerować około 100 bilionów identyfikatorów UUID w wersji 4. Szansa na jedną kolizję wzrasta do 50% dopiero po wygenerowaniu 261 UUID (2,3 x 10 ^ 18 lub 2,3 biliarda). Odnosząc te liczby do baz danych i biorąc pod uwagę kwestię, czy prawdopodobieństwo kolizji UUID wersji 4 jest znikome, rozważ plik zawierający 2,3 biliarda UUID wersji 4, z 50% szansą, że będzie zawierał jedną kolizję UUID. Miałoby 36 eksabajtów, zakładając brak innych danych lub narzutów, tysiące razy większe niż największe obecnie istniejące bazy danych, które są rzędu petabajtów. Przy szybkości 1 miliarda identyfikatorów UUID generowanych na sekundę wygenerowanie identyfikatorów UUID dla pliku zajęłoby 73 lata. Wymagałoby to również około 3. 6 milionów 10-terabajtowych dysków twardych lub kaset z taśmami do ich przechowywania, przy założeniu braku kopii zapasowych lub nadmiarowości. Odczytanie pliku z typową szybkością transferu „dysk do bufora” wynoszącą 1 gigabit na sekundę wymagałoby ponad 3000 lat dla pojedynczego procesora. Ponieważ współczynnik nieodwracalnych błędów odczytu dysków wynosi w najlepszym przypadku 1 bit na 1018 bitów odczytu, podczas gdy plik zawierałby około 1020 bitów, zwykłe odczytanie pliku raz od końca do końca spowodowałoby co najmniej około 100 razy więcej błędów. czytaj UUID niż duplikaty. Błędy pamięci masowej, sieci, zasilania i inne błędy sprzętu i oprogramowania byłyby niewątpliwie tysiące razy częstsze niż problemy z powielaniem identyfikatorów UUID. szybkość transferu 1 gigabita na sekundę wymagałaby ponad 3000 lat dla pojedynczego procesora. Ponieważ współczynnik nieodwracalnych błędów odczytu dysków wynosi w najlepszym przypadku 1 bit na 1018 bitów odczytu, podczas gdy plik zawierałby około 1020 bitów, zwykłe odczytanie pliku raz od końca do końca spowodowałoby co najmniej około 100 razy więcej błędów. czytaj UUID niż duplikaty. Błędy pamięci masowej, sieci, zasilania i inne błędy sprzętu i oprogramowania byłyby niewątpliwie tysiące razy częstsze niż problemy z powielaniem identyfikatorów UUID. szybkość transferu 1 gigabita na sekundę wymagałaby ponad 3000 lat dla pojedynczego procesora. Ponieważ współczynnik nieodwracalnych błędów odczytu dysków wynosi w najlepszym przypadku 1 bit na 1018 bitów odczytu, podczas gdy plik zawierałby około 1020 bitów, zwykłe odczytanie pliku raz od końca do końca spowodowałoby co najmniej około 100 razy więcej błędów. czytaj UUID niż duplikaty. Błędy pamięci masowej, sieci, zasilania i inne błędy sprzętu i oprogramowania byłyby niewątpliwie tysiące razy częstsze niż problemy z powielaniem identyfikatorów UUID.

źródło: wikipedia

W podsumowaniu,

• UUID jest znormalizowany.
• gen_random_uuid()to 128 bitów losowych przechowywanych w 128 bitach (2 ** 128 kombinacji). 0-odpadów.
• random() generuje tylko 52 bity losowe w PostgreSQL (2 ** 52 kombinacje).
• md5()przechowywany jako UUID ma 128 bitów, ale może być tylko tak losowy, jak jego wejście ( 52 bity, jeśli jest używanyrandom() )
• md5()przechowywany jako tekst ma 288 bitów, ale może być tylko tak losowy, jak jego wejście ( 52 bity, jeśli jest używanyrandom() ) - ponad dwukrotnie większy niż UUID i ułamek losowości)
• md5() jako skrót może być tak zoptymalizowany, że nie robi wiele efektywnie.
• Identyfikator UUID jest bardzo wydajny w przypadku przechowywania: PostgreSQL zapewnia typ o dokładnie 128 bitach. W przeciwieństwie do texti varcharitp., Które są przechowywane jako wartość, varlenaktóra ma narzut na długość ciągu.
• Sprytny UUID PostgreSQL zawiera kilka domyślnych operatorów, rzutowania i funkcji.

Ostatnio bawiłem się PostgreSQL i myślę, że znalazłem trochę lepsze rozwiązanie, używając tylko wbudowanych metod PostgreSQL - bez pl / pgsql. Jedynym ograniczeniem jest to, że obecnie generuje tylko łańcuchy UPCASE, liczby lub łańcuchy z małych liter.

template1=> SELECT array_to_string(ARRAY(SELECT chr((65 + round(random() * 25)) :: integer) FROM generate_series(1,12)), '');
 array_to_string
-----------------
 TFBEGODDVTDM

template1=> SELECT array_to_string(ARRAY(SELECT chr((48 + round(random() * 9)) :: integer) FROM generate_series(1,12)), '');
 array_to_string
-----------------
 868778103681

Drugi argument generate_seriesmetody określa długość ciągu.

Proszę użyć string_agg!

SELECT string_agg (substr('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', ceil (random() * 62)::integer, 1), '')
FROM   generate_series(1, 45);

Używam tego z MD5 do generowania UUID również. Chcę tylko losowej wartości z większą liczbą bitów niż random ()liczba całkowita.

Chociaż domyślnie nie jest aktywne, możesz aktywować jedno z podstawowych rozszerzeń:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Wtedy twoja instrukcja staje się prostym wywołaniem gen_salt (), które generuje losowy ciąg:

select gen_salt('md5') from generate_series(1,4);

 gen_salt
-----------
$1$M.QRlF4U
$1$cv7bNJDM
$1$av34779p
$1$ZQkrCXHD

Wiodąca liczba to identyfikator skrótu. Dostępnych jest kilka algorytmów, każdy z własnym identyfikatorem:

• md5: 1 $
• bf: 2 $ a 06 $
• des: brak identyfikatora
• xdes: _J9 ..

Więcej informacji o rozszerzeniach:

• pgCrypto: http://www.postgresql.org/docs/9.2/static/pgcrypto.html
• Zawarte rozszerzenia: http://www.postgresql.org/docs/9.2/static/contrib.html

EDYTOWAĆ

Jak wskazał Evan Carrol, od wersji 9.4 można używać gen_random_uuid()

http://www.postgresql.org/docs/9.4/static/pgcrypto.html

Nie sądzę, że szukasz przypadkowego ciągu jako takiego. To, czego potrzebujesz do weryfikacji sesji, to ciąg, który gwarantuje unikalność. Czy przechowujesz informacje dotyczące weryfikacji sesji na potrzeby audytu? W takim przypadku ciąg musi być unikalny między sesjami. Znam dwa, raczej proste podejścia:

1. Użyj sekwencji. Dobry do użytku w pojedynczej bazie danych.
2. Użyj UUID. Uniwersalne rozwiązanie, które sprawdza się również w środowiskach rozproszonych.

UUID są zagwarantowane być unikalny ze względu na ich algorytm do wytwarzania; skutecznie jest to niezwykle mało prawdopodobne, że wygenerujesz dwie identyczne liczby na dowolnej maszynie, kiedykolwiek, kiedykolwiek (zwróć uwagę, że jest to znacznie silniejsze niż w przypadku ciągów losowych, które mają znacznie mniejszą częstotliwość niż UUID).

Aby używać identyfikatorów UUID, musisz załadować rozszerzenie uuid-ossp. Po zainstalowaniu wywołaj dowolną z dostępnych funkcji uuid_generate_vXXX () w wywołaniach SELECT, INSERT lub UPDATE. Typ uuid to 16-bajtowa liczba, ale ma również reprezentację w postaci ciągu.

Parametr INTEGER określa długość łańcucha. Gwarantowane pokrycie wszystkich 62 znaków alfanumerycznych z jednakowym prawdopodobieństwem (w przeciwieństwie do niektórych innych rozwiązań krążących po Internecie).

CREATE OR REPLACE FUNCTION random_string(INTEGER)
RETURNS TEXT AS
$BODY$
SELECT array_to_string(
    ARRAY (
        SELECT substring(
            '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
            FROM (ceil(random()*62))::int FOR 1
        )
        FROM generate_series(1, $1)
    ), 
    ''
)
$BODY$
LANGUAGE sql VOLATILE;

@Kavius ​​zaleca używanie pgcrypto, ale zamiast tego gen_salt, co z tym gen_random_bytes? A może sha512zamiast tego md5?

create extension if not exists pgcrypto;
select digest(gen_random_bytes(1024), 'sha512');

Dokumenty:

F.25.5. Funkcje danych losowych

gen_random_bytes (count integer) zwraca bajty

Zwraca liczbę losowych bajtów silnych pod względem kryptograficznym. Jednocześnie można wyodrębnić maksymalnie 1024 bajty. Ma to na celu uniknięcie opróżnienia puli generatora losowości.

select * from md5(to_char(random(), '0.9999999999999999'));

select encode(decode(md5(random()::text), 'hex')||decode(md5(random()::text), 'hex'), 'base64')