user¶
Carnivora Users: Users own things objects in the DB, and they can login into frontends (edentata)
Schema Contents
Tables¶
user.deputy
¶
Deputies for users
- Primary key
- deputy
- represented
- Columns
deputy
user.t_userDeputy
References user.user.owner
On Delete: CASCADE
On Update: CASCADE
represented
user.t_userUser for which the deputy can act
References user.user.owner
On Delete: CASCADE
On Update: CASCADE
user.session
¶
User login sessions
- Primary key
- id
- Columns
owner
user.t_userOwner
References user.user.owner
On Delete: CASCADE
On Update: CASCADE
id
varcharSession id
- Default
"user"._session_id()
act_as
user.t_userAct as
started
timestampSession started at this time
- Default
CURRENT_TIMESTAMP
user.user
¶
Users
Users with password set to NULL
can be used as groups.
- Primary key
- owner
- Columns
option
jsonbFree options in JSON format
- Default
'{}'
owner
user.t_userUser name, login name
password
NULL | commons.t_passwordUnix shadow crypt format, NULL value disables login
contact_email
NULL | email.t_addressOptional 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
owner
user.t_useract_as
user.t_user
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
p_act_as
user.t_user
- Variables defined for body
v_owner
user.t_user
- 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
p_login
varcharp_password
commons.t_password_plaintext
- Variables defined for body
v_login_owner
user.t_user
- Returns
- TABLE
- Returned columns
user
user.t_user
- 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
v_owner
user.t_user
- Returns
- TABLE
- Returned columns
represented
user.t_user
- 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
p_password
commons.t_password_plaintext
- Variables defined for body
v_owner
user.t_user
- 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.\-_]+$'