36 lines
895 B
SQL
36 lines
895 B
SQL
CREATE TABLE public.users_t (
|
|
id SERIAL NOT NULL,
|
|
username VARCHAR(25) NOT NULL,
|
|
pw VARCHAR(512) NOT NULL,
|
|
super INTEGER DEFAULT 0 NOT NULL,
|
|
CONSTRAINT users_t_pk PRIMARY KEY (id),
|
|
CONSTRAINT users_t_uk_username UNIQUE (username)
|
|
);
|
|
|
|
CREATE TABLE public.acls_t (
|
|
id SERIAL NOT NULL,
|
|
"user" INTEGER NOT NULL,
|
|
topic VARCHAR(512) NOT NULL,
|
|
rw INTEGER DEFAULT 5 NOT NULL,
|
|
CONSTRAINT acls_t_pk PRIMARY KEY (id),
|
|
CONSTRAINT acls_t_fk_user FOREIGN KEY ("user") REFERENCES users_t(id)
|
|
);
|
|
|
|
CREATE OR REPLACE VIEW users AS
|
|
SELECT users_t.username,
|
|
users_t.pw,
|
|
users_t.super
|
|
FROM users_t;
|
|
|
|
CREATE OR REPLACE VIEW acls AS
|
|
SELECT a.topic,
|
|
a.rw,
|
|
u.username
|
|
FROM users_t u,
|
|
acls_t a
|
|
WHERE a."user" = u.id;
|
|
|
|
CREATE USER mosquittoauth;
|
|
GRANT SELECT ON users, acls TO mosquittoauth;
|
|
|