server_access

Server Access

Explicit passwd entries for shell acounts and sftp.

This module sends the following signals:
  • server_access/sftp
  • server_access/ssh

Tables

server_access.user

unix user

Primary key
  • user
Foreign keys
Columns
  • service_entity_name dns.t_hostname

    Service entity name

  • service commons.t_key

    Service (e.g. email, jabber)

  • subservice commons.t_key

    Subservice (e.g. account, alias)

  • backend_status NULL | backend.t_status

    Status of database entry in backend. NULL: nothing pending, ‘ins’: entry not present on backend client, ‘upd’: update pending on backend client, ‘del’: deletion peding on backend client.

    Default
    'ins'
  • owner user.t_user

    Owner

    References user.user.owner

    On Update: CASCADE

  • uid integer

    Unix user identifier

    Default
    nextval('commons.uid')
  • user server_access.t_user

    User

  • password NULL | commons.t_password

    Unix shadow crypt format

Functions

server_access.del_user

delete

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


BEGIN
    -- perform DELETE to trigger potential foreign key errors
    DELETE FROM server_access.user
    WHERE
        "user" = p_user AND
        service_entity_name = p_service_entity_name AND
        owner = v_owner;

    -- if not failed yet, emulate rollback of DELETE
    RAISE transaction_rollback;
EXCEPTION
    WHEN transaction_rollback THEN
        UPDATE server_access.user
            SET backend_status = 'del'
        WHERE
            "user" = p_user AND
            service_entity_name = p_service_entity_name AND
            owner = v_owner
        RETURNING subservice INTO v_subservice;

        PERFORM backend._conditional_notify_service_entity_name(
             FOUND,  p_service_entity_name, 'server_access', v_subservice
         );
END;

server_access.ins_user

ins user

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


IF p_password IS NULL THEN
    v_password := NULL;
ELSE
    v_password := commons._hash_password(p_password);
END IF;

INSERT INTO server_access.user
    (service, subservice, service_entity_name, "user", password, owner)
VALUES
    ('server_access', p_subservice, p_service_entity_name, p_user, v_password, v_owner);

PERFORM backend._notify_service_entity_name(p_service_entity_name, 'server_access', p_subservice);

server_access.sel_user

sel user

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.user,
        t.password IS NOT NULL,
        t.service,
        t.subservice,
        t.service_entity_name,
        t.backend_status
    FROM
        server_access.user AS t
    WHERE
        owner = v_owner
    ORDER BY backend_status, "user"
;

server_access.srv_user

backend server_access.user

Parameters
Returns
TABLE
Returned columns
Execute privilege
PERFORM backend._get_login();


RETURN QUERY
    WITH

    -- DELETE
    d AS (
        DELETE FROM server_access.user AS t
        WHERE
            backend._deleted(t.backend_status) AND
            backend._machine_priviledged_service(t.service, t.service_entity_name)
    ),

    -- UPDATE
    s AS (
        UPDATE server_access.user AS t
            SET backend_status = NULL
        WHERE
            backend._machine_priviledged_service(t.service, t.service_entity_name) AND
            backend._active(t.backend_status)
    )

    -- SELECT
    SELECT
        t.user,
        t.password,
        t.service,
        t.subservice,
        t.service_entity_name,
        t.backend_status,
        t.uid
    FROM server_access.user AS t

    WHERE
        backend._machine_priviledged_service(t.service, t.service_entity_name) AND
        (backend._active(t.backend_status) OR p_include_inactive);

server_access.upd_user

passwd user

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


IF p_password IS NOT NULL THEN
    v_password := commons._hash_password(p_password);
END IF;

UPDATE server_access.user
SET
    password = v_password,
    backend_status = 'upd'
WHERE
    "user" = p_user AND
    service_entity_name = p_service_entity_name AND
    owner = v_owner
RETURNING subservice INTO v_subservice;

PERFORM backend._conditional_notify_service_entity_name(
    FOUND, p_service_entity_name, 'server_access', v_subservice
);

Domains

server_access.t_user

Unix user. This type only allows a subset of those names allowed by POSIX.

Checks