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

Question

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','[email protected]',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.

If you create a normal django User in manage.py shell & set it's password & save, does authenticate() work on that account? Can you include the output from psql of select * from auth_user where username in ('sample', 'sample_from_django_shell')? — AdamKG
– AdamKG, Commented Nov 17, 2011 at 20:20
Wouldn't it be easier if you create a before insert trigger on your users table?. That way every time you insert a new record it will encrypt the password. To do this you could use crypt() and gen_salt() from pgcrypto — webjunkie
– webjunkie, Commented Nov 17, 2011 at 20:56
Can you provide specific passwords that work with the hashes? Presumably throwaway ones? — Chris Travers
– Chris Travers, Commented Mar 23, 2013 at 9:02
Make more sense upvote the linked answers than duplicate it here? — mamcx
– mamcx, Commented Mar 30, 2013 at 16:29

Chris Travers · Accepted Answer · 2013-03-26 10:18:26Z

As it stands there is no way for us to really critique your code per se. However to re-use logins, my recommendation is to simply hit the same routines that Django uses, porting them to a new language or framework as necessary. This means you have a single point of authority regarding security.

Note you cannot use SHA hashed passwords (any algorithm) with PostgreSQL per se because PostgreSQL stores the passwords as md5 hashes salted with the username. Unless you have access to the unhashed username you cannot alter the PostgreSQL user account effectively. You might however be able to write a function which both changes the PostgreSQL and Django password from the same inputs but you need to be careful about statement logging. Also note that ALTER USER is not parameterized in PostgreSQL so you end up having to use pl/pgsql and do something like:

  EXECUTE $e$ ALTER USER $e$ || quote_ident(in_username) || $e$ WITH PASSWORD $e$ 
      || quote_literal(in_password);

Community · Accepted Answer · 2017-05-23 11:55:12Z

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';

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related