Search code examples
djangopostgresqlauthenticationpycryptopostgresql-9.1

How implement the same password code in postgres as in django?


I need acces to the same BD from both django and my custom app.

I like to implement the same functionality on postgress 9 as in django.

This is my try:

CREATE EXTENSION pgcrypto
  SCHEMA public;
-- Genera una cadena aleatoria del tamaño especificado
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;

-- Encripta con SHA1 una cadena y retorna el tipo de algoritmo + salt + hash
CREATE OR REPLACE FUNCTION encryp_text(_text text)
RETURNS TEXT
AS $$
DECLARE
    hash text := '';
    salt text := '';
BEGIN
    salt := random_string(12);
    hash := encode( digest(random_string(12) || _text, 'SHA1'), 'hex');

    RETURN 'sha1$' || salt || '$' || hash;
END;
$$
LANGUAGE 'plpgsql';


-- Resetea el pwd del usuario
CREATE OR REPLACE FUNCTION create_user (_username text, _password text, name text, lastname text, email text, isadmin bool, isstaff bool)
RETURNS BOOLEAN
AS $$
BEGIN
    IF isadmin THEN
        isstaff := isadmin;
    END IF;

    INSERT INTO auth_user(
            username, 
            first_name, 
            last_name, 
            email, 
            password, 
            is_staff, 
            is_active, 
            is_superuser)
    VALUES (_username, 
            name,
            lastname,
            email,
            encryp_text(_password),
            isstaff, 
            true,
            isadmin);
END;
$$
LANGUAGE 'plpgsql';
SELECT create_user('sample','123','sample','user','s@s.com',true,true)

However, in django, when try to autenticate:

from django.contrib.auth import authenticate
authenticate(username='sample', password='123')

It fails. What I need to do?

UPDATE:

Same password:

Django:sha1$46uim9Staj7A$d472909885d27a21bc6e489641e27cc6e4ed25b6 Postgress:sha1$CP5CDALuPntn$d85f6aec18ae781c02cddbaa53e7c92e1b2c7ab1

UPDATE: I forget about this questions. Was solved as show on How reimplement BCryptPasswordHasher of django with postgres 9.1 and upgrade to new bcrypt method.


Solution

  • As requested, I solve this questions as

    -- Genera una cadena aleatoria del tamaño especificado
    CREATE OR REPLACE FUNCTION random_string(length INTEGER, OUT 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;
    
      RETURNS =  result;
    END;
    $$
    LANGUAGE plpgsql;
    
    -- Encripta con SHA1 una cadena y retorna el tipo de algoritmo + salt + hash
    CREATE OR REPLACE FUNCTION encryp_TEXT(_TEXT TEXT, OUT RETURNS TEXT)
    AS $$
    DECLARE
        hash TEXT := '';
        salt TEXT := '';
    BEGIN
        salt := random_string(12);
        hash := encode( libs.digest(salt || _TEXT, 'sha1'), 'hex');
    
        RETURNS =  'sha1$' || salt || '$' || hash;
    END;
    $$
    LANGUAGE 'plpgsql';
    

    However, this is not longer rigth with new django (1.5). The correct version for that is in How reimplement BCryptPasswordHasher of django with postgres 9.1

    Is now:

    -- Encripta con bcrypto una cadena y retorna el tipo de algoritmo + salt + hash, django compatible
    CREATE OR REPLACE FUNCTION encryp_text(_TEXT TEXT, OUT RETURNS TEXT)
    AS $$
    DECLARE
        hash TEXT := '';
        salt TEXT := '';
    BEGIN
        salt := random_string(12);
        hash := libs.crypt(_TEXT,salt);
    
        RETURNS =  'bcrypt$' || hash;
    END;
    $$
    LANGUAGE 'plpgsql';