forked from vernemq/docker-vernemq
-
Notifications
You must be signed in to change notification settings - Fork 0
/
common-interface.sql
79 lines (68 loc) · 2.52 KB
/
common-interface.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
CREATE TABLE authentication (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
role VARCHAR(50) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE merchants (
id SERIAL PRIMARY KEY,
authentication_id INT UNIQUE NOT NULL,
merchant_name VARCHAR(255) NOT NULL,
merchant_category VARCHAR(100),
/* Other merchant-specific fields */
FOREIGN KEY (authentication_id) REFERENCES authentication(id)
);
CREATE TABLE iot_devices (
id SERIAL PRIMARY KEY,
authentication_id INT UNIQUE NOT NULL,
device_name VARCHAR(255) NOT NULL,
device_type VARCHAR(100),
/* Other IoT device-specific fields */
FOREIGN KEY (authentication_id) REFERENCES authentication(id)
);
CREATE TABLE Users (
id SERIAL PRIMARY KEY,
authentication_id INT UNIQUE NOT NULL,
user_name VARCHAR(255) NOT NULL,
user_type VARCHAR(100),
/* Other user-specific fields */
FOREIGN KEY (authentication_id) REFERENCES authentication(id)
);
/*
Easily add new entity types if needed by creating additional tables.
Query and manage entity-specific data independently.
- how will my login & signup work with this?
when we do a sign up, it will create a transaction i.e it will create an entry in authentication table & user table both atomically.
when we do a login, it will check if the username & password from authentication table. if it is correct, it will return the corresponding DTO from table.
- How do we manage mqtt auth with this?
every entry in {user, iot_devices, merchants} tables will trigger a function to create a new entry in vmq_auth_acl table.
vernemq will use this table to authenticate mqtt clients.
*/
CREATE OR REPLACE FUNCTION make_acl_entry()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO vmq_auth_acl (column1, column2, ...)
VALUES (NEW.column1, NEW.column2, ...);
/*
will add other necessary columns here
*/
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER insert_trigger
AFTER INSERT ON iot_devices
FOR EACH ROW
EXECUTE FUNCTION make_acl_entry();
CREATE EXTENSION pgcrypto;
CREATE TABLE IF NOT EXISTS vmq_auth_acl (
mountpoint character varying(10) NOT NULL,
client_id character varying(128) NOT NULL,
username character varying(128) NOT NULL,
password character varying(128),
publish_acl json,
subscribe_acl json,
CONSTRAINT vmq_auth_acl_primary_key PRIMARY KEY (mountpoint, client_id, username)
);