user

Carnivora Users: Users own things objects in the DB, and they can login into frontends (edentata)

Tables

user.deputy

Deputies for users

Primary key
  • deputy
  • represented
Columns

user.session

User login sessions

Primary key
  • id
Columns

user.user

Users

Users with password set to NULL can be used as groups.

Primary key
  • owner
Columns
  • option jsonb

    Free options in JSON format

    Default
    '{}'
  • owner user.t_user

    User name, login name

  • password NULL | commons.t_password

    Unix shadow crypt format, NULL value disables login

  • contact_email NULL | email.t_address

    Optional contact email address, can be used as login name

Functions

user._get_login

Shows informations for the current user login. Throws an exception if no login is associated to the current database connection.

Parameters
None
Returns
TABLE
Returned columns
IF (SELECT TRUE FROM "user"."session"
   WHERE "id"="user"._session_id())
THEN
   RETURN QUERY SELECT t.owner, t.act_as FROM "user"."session" AS t
       WHERE "id"="user"._session_id();
ELSE
   RAISE 'Database connection is not associated to a user login.'
       USING HINT := 'Use user.ins_login(...) first.';
END IF;

user._login_user

Shows informations for the current user login. Throws an exception if no login is associated to the current database connection.

Parameters
None
Returns
user.t_user
RETURN (SELECT owner FROM "user"._get_login());

user._session_id

Gives an id for the database connection that is unique over all database connections. It is used to identify user logins.

Not sure if this stays unique with distributed infrastructure!

Parameters
None
Returns
varchar
RETURN pg_backend_pid()::varchar;

user.del_login

Try to logout

Parameters
None
Returns
void
Execute privilege
DELETE FROM "user".session WHERE id = "user"._session_id();

IF NOT FOUND THEN
   RAISE 'Carnivora: user logout failed, not logged in'
    USING DETAIL = '$carnivora:user:logout_failed$';
END IF;

user.ins_deputy

Act as deputy

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


UPDATE "user".session AS t
    SET act_as = p_act_as
    FROM "user".deputy AS s
    WHERE
        s.deputy = t.owner AND
        s.represented = p_act_as AND
        t.id = "user"._session_id() AND
        t.owner = v_owner;

IF NOT FOUND THEN
    RAISE 'Acting as deputy failed.'
        USING DETAIL := '$carnivora:user:deputy_failed$';
END IF;

user.ins_login

Try to bind database connection to new user session.

Parameters
Variables defined for body
Returns
TABLE
Returned columns
Execute privilege
SELECT owner INTO v_login_owner FROM "user"."user" AS t
       WHERE
           p_login IS NOT NULL AND
           t.password IS NOT NULL AND
           lower(p_login) IN (owner, contact_email) AND
           commons._passwords_equal(p_password, t.password);

IF v_login_owner IS NOT NULL THEN
   INSERT INTO "user"."session" (owner, act_as) VALUES (v_login_owner, v_login_owner);
   RETURN QUERY SELECT v_login_owner;
ELSE
   RAISE 'Carnivora: invalid user login'
    USING DETAIL = '$carnivora:user:login_invalid$';
END IF;

user.sel_deputy

sel deputy

Parameters
None
Variables defined for body
Returns
TABLE
Returned columns
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


RETURN QUERY
    SELECT t.represented FROM "user".deputy AS t
    WHERE t.deputy = "user"._login_user()
    ORDER BY t.represented;

user.upd_user

change user passwd

Parameters
Variables defined for body
Returns
void
Execute privilege
-- begin userlogin prelude
v_owner := (SELECT t.act_as FROM "user"._get_login() AS t);
-- end userlogin prelude


UPDATE "user".user
    SET password = commons._hash_password(p_password)

WHERE
    owner = "user"._login_user();

Domains

user.t_user

Username

Checks
  • valid_characters

    Only lower-case letters, numbers and .-_

    VALUE ~ '^[a-z0-9.\-_]+$'

Roles

userlogin

Do user actions via this group

Login
Disabled

system

Highly priviledged user

Login
Disabled