commons

Carnivora Commons

Usefull templates, functions and domains.

Functions

commons._hash_password

SHA512 hash of the password with 16 charcters random salt. The returned format is the traditional ‘crypt(3)’ format.

Parameters
Language
plpython3u
Returns
commons.t_password
import crypt

return crypt.crypt(p_password, crypt.METHOD_SHA512)

commons._idn

Converts a unicode domain name to IDN (ASCII)

Currently using IDNA2003.

Parameters
Language
plpython3u
Returns
varchar
Execute privilege
if p_domain is None:
   return None

if p_domain.lower() != p_domain:
    raise plpy.Error('Only lower case IDNs are allowed and can be handled.')

return p_domain.encode('idna').decode()

commons._jsonb_to_array

Converts a JSONB array to a PostgreSQL text[] array

Parameters
Returns
text[]
RETURN ARRAY(SELECT jsonb_array_elements_text(p_jsonb));

commons._passwords_equal

Compares a plaintext password with an arbitrary ‘crypt(3)’ hashed password.

Uses <https://docs.python.org/3/library/hmac.html>

Parameters
Language
plpython3u
Returns
boolean
import crypt
from hmac import compare_digest as compare_hash

# Giving crypt.crypt the full hash as second argument fixes the use of the
# right salt and algorithm. Using compare_hash to avoid timing attacks.
return compare_hash(crypt.crypt(p_password_plaintext, p_password_hash), p_password_hash)

commons._raise_inaccessible_or_missing

Raised whenever a operation on an object failes because it is not owned by the user or it is not found.

Parameters

  • p_raise boolean

    Controls if the exception is raised

Returns
void
IF NOT COALESCE(p_raise, FALSE) THEN
    RAISE 'Object inaccessible or missing'
        USING DETAIL = '$carnivora:commons:inaccessible_or_missing$';
END IF;

commons._reverse_array

Copied from <https://wiki.postgresql.org/wiki/Array_reverse>

Parameters
Language
sql
Returns
anyarray
Execute privilege
SELECT
    ARRAY(
        SELECT $1[i]
        FROM generate_subscripts($1,1) AS s(i)
        ORDER BY i DESC
    );

commons._uuid

Returns a random uuid

Parameters
None
Returns
uuid
RETURN public.uuid_generate_v4();

Domains

commons.t_port

Port

Checks
  • invalid_port

    Only allow port values

    VALUE BETWEEN 0 AND 65535

commons.t_password

unix hash thingy

Todo

propper checking of format

Checks
  • crypt(3) password format

    Only allows SHA512 strings.

    VALUE ~ '^\$6\$[.\/a-zA-Z0-9]{8,16}\$[.\/a-zA-Z0-9]{86}$'

commons.t_password_plaintext

Password in plaintext

Checks
  • minimum password length 8

    Ensures that passwords at least have 8 chars

    character_length(VALUE) >= 8

commons.t_hexvarchar

Varchar only with HEX values

Checks
  • invalid characters

    Only allows numbers and chars a-f for hex representation

    VALUE ~ '^[0-9a-f]*$'

Sequences

commons.uid

Unix user id