55 lines
1.9 KiB
SQL
55 lines
1.9 KiB
SQL
-- Create tables to store users, roles and the assignment of roles to users.
|
|
|
|
CREATE TABLE users (
|
|
user_id UUID PRIMARY KEY NOT NULL,
|
|
user_name TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT,
|
|
created_at_utc TIMESTAMP NOT NULL,
|
|
last_login_utc TIMESTAMP,
|
|
locked BOOLEAN NOT NULL DEFAULT FALSE
|
|
);
|
|
|
|
COMMENT ON TABLE users IS 'All users known to the system.';
|
|
|
|
COMMENT ON COLUMN users.user_id IS 'A UUID given to the user. Stays with the user even after the user is renamed.';
|
|
|
|
COMMENT ON COLUMN users.user_name IS 'A textual user name for the user. Might be kept stable but some deployments may support renames.';
|
|
|
|
COMMENT ON COLUMN users.password_hash IS 'Hash of the user''s password. Null if the user does not have a password set.';
|
|
|
|
COMMENT ON COLUMN users.created_at_utc IS 'When the user was created, in UTC.';
|
|
|
|
COMMENT ON COLUMN users.last_login_utc IS 'When the user last logged in, in UTC.';
|
|
|
|
COMMENT ON COLUMN users.locked IS 'Whether the user has been locked.';
|
|
|
|
CREATE TABLE roles (
|
|
role_id TEXT PRIMARY KEY NOT NULL,
|
|
role_name TEXT NOT NULL
|
|
);
|
|
|
|
COMMENT ON TABLE roles IS 'A role is akin to a group or a permission. Users can be restricted depending on what roles they have.';
|
|
|
|
COMMENT ON COLUMN roles.role_id IS 'Unique code for the role. [a-zA-Z0-9_:-]+';
|
|
|
|
COMMENT ON COLUMN roles.role_name IS 'Human-friendly name for the role.';
|
|
|
|
CREATE TABLE users_roles (
|
|
user_id UUID NOT NULL,
|
|
role_id TEXT NOT NULL,
|
|
|
|
granted_at_utc TIMESTAMP NOT NULL,
|
|
|
|
PRIMARY KEY (user_id, role_id),
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id),
|
|
FOREIGN KEY (role_id) REFERENCES roles(role_id)
|
|
);
|
|
|
|
COMMENT ON TABLE users_roles IS 'Association of users and their roles.';
|
|
|
|
COMMENT ON COLUMN users_roles.user_id IS 'User ID that has been assigned a role.';
|
|
|
|
COMMENT ON COLUMN users_roles.role_id IS 'Role ID that has been assigned to the user.';
|
|
|
|
COMMENT ON COLUMN users_roles.granted_at_utc IS 'When the role was assigned to the user.';
|