PostgreSQL에서 세션 ID에 적합한 임의의 문자열을 어떻게 생성합니까?

PostgreSQL에서 세션 ID에 적합한 임의의 문자열을 어떻게 생성합니까?

---

PostgreSQL을 사용하여 세션 확인에 사용할 임의의 문자열을 만들고 싶습니다. 를 사용하여 난수를 얻을 수 있다는 것을 알고 SELECT random()있으므로을 시도 SELECT md5(random())했지만 작동하지 않습니다. 어떻게 할 수 있습니까?

---

이 간단한 해결책을 제안합니다.

이것은 주어진 길이의 임의의 문자열을 반환하는 아주 간단한 함수입니다.

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;

그리고 사용법 :

select random_string(15);

출력 예 :

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)

---

다음과 같이 초기 시도를 수정할 수 있습니다.

SELECT md5(random()::text);

다른 제안보다 훨씬 간단합니다. :-)

---

Marcin의 솔루션을 기반으로이 작업을 수행하여 임의의 알파벳을 사용할 수 있습니다 (이 경우 62 개의 ASCII 영숫자 문자).

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

---

UUID에서 128 비트를 무작위로 얻을 수 있습니다. 이것은 최신 PostgreSQL에서 작업을 수행하는 방법입니다.

CREATE EXTENSION pgcrypto;
SELECT gen_random_uuid();

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

수 있음 도 UUID에 문서를 읽을 가치

The data type uuid stores Universally Unique Identifiers (UUID) as defined by RFC 4122, ISO/IEC 9834-8:2005, and related standards. (Some systems refer to this data type as a globally unique identifier, or GUID, instead.) This identifier is a 128-bit quantity that is generated by an algorithm chosen to make it very unlikely that the same identifier will be generated by anyone else in the known universe using the same algorithm. Therefore, for distributed systems, these identifiers provide a better uniqueness guarantee than sequence generators, which are only unique within a single database.

How rare is a collision with UUID, or guessable? Assuming they're random,

About 100 trillion version 4 UUIDs would need to be generated to have a 1 in a billion chance of a single duplicate ("collision"). The chance of one collision rises to 50% only after 261 UUIDs (2.3 x 10^18 or 2.3 quintillion) have been generated. Relating these numbers to databases, and considering the issue of whether the probability of a Version 4 UUID collision is negligible, consider a file containing 2.3 quintillion Version 4 UUIDs, with a 50% chance of containing one UUID collision. It would be 36 exabytes in size, assuming no other data or overhead, thousands of times larger than the largest databases currently in existence, which are on the order of petabytes. At the rate of 1 billion UUIDs generated per second, it would take 73 years to generate the UUIDs for the file. It would also require about 3.6 million 10-terabyte hard drives or tape cartridges to store it, assuming no backups or redundancy. Reading the file at a typical "disk-to-buffer" transfer rate of 1 gigabit per second would require over 3000 years for a single processor. Since the unrecoverable read error rate of drives is 1 bit per 1018 bits read, at best, while the file would contain about 1020 bits, just reading the file once from end to end would result, at least, in about 100 times more mis-read UUIDs than duplicates. Storage, network, power, and other hardware and software errors would undoubtedly be thousands of times more frequent than UUID duplication problems.

source: wikipedia

In summary,

• UUID is standardized.
• gen_random_uuid() is 128 bits of random stored in 128 bits (2**128 combinations). 0-waste.
• random() only generates 52 bits of random in PostgreSQL (2**52 combinations).
• md5() stored as UUID is 128 bits, but it can only be as random as its input (52 bits if using random())
• md5() stored as text is 288 bits, but it only can only be as random as its input (52 bits if using random()) - over twice the size of a UUID and a fraction of the randomness)
• md5() as a hash, can be so optimized that it doesn't effectively do much.
• UUID is highly efficient for storage: PostgreSQL provides a type that is exactly 128 bits. Unlike text and varchar, etc which store as a varlena which has overhead for the length of the string.
• PostgreSQL nifty UUID comes with some default operators, castings, and features.

---

I was playing with PostgreSQL recently, and I think I've found a little better solution, using only built-in PostgreSQL methods - no pl/pgsql. The only limitation is it currently generates only UPCASE strings, or numbers, or lower case strings.

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

The second argument to the generate_series method dictates the length of the string.

---

While not active by default, you could activate one of the core extensions:

CREATE EXTENSION IF NOT EXISTS pgcrypto;

Then your statement becomes a simple call to gen_salt() which generates a random string:

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

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

The leading number is a hash identifier. Several algorithms are available each with their own identifier:

• md5: $1$
• bf: $2a$06$
• des: no identifier
• xdes: _J9..

More information on extensions:

• pgCrypto: http://www.postgresql.org/docs/9.2/static/pgcrypto.html
• Included Extensions: http://www.postgresql.org/docs/9.2/static/contrib.html

---

EDIT

As indicated by Evan Carrol, as of v9.4 you can use gen_random_uuid()

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

---

Please use string_agg!

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

I'm using this with MD5 to generate a UUID also. I just want a random value with more bits than a random () integer.

---

I do not think that you are looking for a random string per se. What you would need for session verification is a string that is guaranteed to be unique. Do you store session verification information for auditing? In that case you need the string to be unique between sessions. I know of two, rather simple approaches:

1. Use a sequence. Good for use on a single database.
2. Use an UUID. Universally unique, so good on distributed environments too.

UUIDs are guaranteed to be unique by virtue of their algorithm for generation; effectively it is extremely unlikely that you will generate two identical numbers on any machine, at any time, ever (note that this is much stronger than on random strings, which have a far smaller periodicity than UUIDs).

You need to load the uuid-ossp extension to use UUIDs. Once installed, call any of the available uuid_generate_vXXX() functions in your SELECT, INSERT or UPDATE calls. The uuid type is a 16-byte numeral, but it also has a string representation.

---

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

---

The INTEGER parameter defines the length of the string. Guaranteed to cover all 62 alphanum characters with equal probability (unlike some other solutions floating around on the Internet).

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 recommended using pgcrypto, but instead of gen_salt, what about gen_random_bytes? And how about sha512 instead of md5?

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

Docs:

F.25.5. Random-Data Functions

gen_random_bytes(count integer) returns bytea

Returns count cryptographically strong random bytes. At most 1024 bytes can be extracted at a time. This is to avoid draining the randomness generator pool.

---

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

참고URL : https://stackoverflow.com/questions/3970795/how-do-you-create-a-random-string-thats-suitable-for-a-session-id-in-postgresql