Carnivora Commons
Usefull templates, functions and domains.
Schema Contents
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)
Converts a unicode domain name to IDN (ASCII)
Currently using IDNA2003.
- Parameters
p_domain
:ref:`varchar <DOMAIN-varchar>`
- 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()
Converts a JSONB array to a PostgreSQL text[] array
- Parameters
p_jsonb
:ref:`jsonb <DOMAIN-jsonb>`
- Returns
- text[]
RETURN ARRAY(SELECT jsonb_array_elements_text(p_jsonb));
Compares a plaintext password with an arbitrary 'crypt(3)' hashed password.
Uses <https://docs.python.org/3/library/hmac.html>
- Parameters
p_password_plaintext
:ref:`commons.t_password_plaintext <DOMAIN-commons.t_password_plaintext>`p_password_hash
:ref:`commons.t_password <DOMAIN-commons.t_password>`
- 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)
Raised whenever a operation on an object failes because it is not owned by the user or it is not found.
- Parameters
p_raise
:ref:`boolean <DOMAIN-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;
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
);
Returns a random uuid
- Parameters
- None
- Returns
- uuid
RETURN public.uuid_generate_v4();
Port
- Checks
invalid_port
Only allow port values
VALUE BETWEEN 0 AND 65535
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}$'
Password in plaintext
- Checks
minimum password length 8
Ensures that passwords at least have 8 chars
character_length(VALUE) >= 8
Key
Varchar only with HEX values
- Checks
invalid characters
Only allows numbers and chars a-f for hex representation
VALUE ~ '^[0-9a-f]*$'
Unix user id