Differences From
Artifact [097969b0cb]:
1 -\prompt 'domain name: ' domain
2 -\prompt 'instance name: ' inst
3 -\prompt 'bind to socket: ' bind
4 -\qecho 'how locked down should this server be? public = anyone can see public timeline and tweets, private = anyone can see tweets with a link but login required for everything else, lockdown = login required for all activities, isolate = like lockdown but with federation protocols completely disabled'
5 -\prompt 'security mode: ' secmode
6 -\qecho 'should user self-registration be allowed? yes or no'
7 -\prompt 'registration: ' regpol
8 -\qecho 'by default, parsav tracks rights on its own. you can override this later by replacing the rights table with a view, but you''ll then need to set appropriate rules on the view to allow administrators to modify rights from the web UI, or set the rights-readonly flag in the config table to true. for now, enter the name of an actor who will be granted full rights when she logs in and identified as the server owner.'
9 -\prompt 'master actor: ' admin
10 -\qecho 'you will need to create an authentication view named parsav_auth mapping your user database to something parsav can understand; see auth.sql for an example.'
11 -
12 -begin;
13 -
14 -drop table if exists parsav_config;
15 -create table if not exists parsav_config (
1 +create table parsav_config (
16 2 key text primary key,
17 3 value text
18 4 );
19 5
20 -insert into parsav_config (key,value) values
21 - ('bind',:'bind'),
22 - ('domain',:'domain'),
23 - ('instance-name',:'inst'),
24 - ('policy-security',:'secmode'),
25 - ('policy-self-register',:'regpol'),
26 - ('master',:'admin'),
27 - ('server-secret', encode(
28 - digest(int8send((2^63 * (random()*2 - 1))::bigint),
29 - 'sha512'), 'base64'));
6 +insert into parsav_config (key,value) values ('schema-version','1'),
7 + ('credential-store','managed');
8 +-- ('bind',:'bind'),
9 +-- ('domain',:'domain'),
10 +-- ('instance-name',:'inst'),
11 +-- ('policy-security',:'secmode'),
12 +-- ('policy-self-register',:'regpol'),
13 +-- ('master',:'admin'),
30 14
31 15 -- note that valid ids should always > 0, as 0 is reserved for null
32 16 -- on the client side, vastly simplifying code
33 -drop table if exists parsav_servers cascade;
34 17 create table parsav_servers (
35 18 id bigint primary key default (1+random()*(2^63-1))::bigint,
36 19 domain text not null,
37 20 key bytea,
38 21 knownsince timestamp,
39 22 parsav boolean -- whether to use parsav protocol extensions
40 23 );
41 24
42 -drop table if exists parsav_actors cascade;
43 25 create table parsav_actors (
44 26 id bigint primary key default (1+random()*(2^63-1))::bigint,
45 27 nym text,
46 28 handle text not null, -- nym [@handle@origin]
47 29 origin bigint references parsav_servers(id)
48 30 on delete cascade, -- null origin = local actor
49 31 knownsince timestamp,
50 32 bio text,
51 33 avataruri text, -- null if local
52 34 rank smallint not null default 0,
53 35 quota integer not null default 1000,
54 36 key bytea, -- private if localactor; public if remote
55 - title text,
37 + epithet text,
38 + authtime timestamp not null default now(), -- cookies earlier than this timepoint will not be accepted
56 39
57 40 unique (handle,origin)
58 41 );
59 42
60 -drop table if exists parsav_rights cascade;
61 43 create table parsav_rights (
62 44 key text,
63 45 actor bigint references parsav_actors(id)
64 46 on delete cascade,
65 47 allow boolean not null,
66 48 scope bigint, -- for future expansion
67 49
68 50 primary key (key,actor)
69 51 );
70 52
71 -insert into parsav_actors (handle,rank,quota) values (:'admin',1,0);
72 -insert into parsav_rights (actor,key,allow)
73 - select (select id from parsav_actors where handle=:'admin'), a.column1, a.column2 from (values
74 - ('purge',true),
75 - ('config',true),
76 - ('censor',true),
77 - ('suspend',true),
78 - ('cred',true),
79 - ('elevate',true),
80 - ('demote',true),
81 - ('rebrand',true)
82 - ) as a;
83 -
84 -drop table if exists parsav_posts cascade;
85 53 create table parsav_posts (
86 54 id bigint primary key default (1+random()*(2^63-1))::bigint,
87 55 author bigint references parsav_actors(id)
88 56 on delete cascade,
89 57 subject text,
90 58 acl text not null default 'all', -- just store the script raw 🤷
91 59 body text,
................................................................................
97 65
98 66 convoheaduri text
99 67 -- only used for tracking foreign conversations and tying them to post heads;
100 68 -- local conversations are tracked directly and mapped to URIs based on the
101 69 -- head's ID. null if native tweet or not the first tweet in convo
102 70 );
103 71
104 -drop table if exists parsav_conversations cascade;
105 -
106 -drop table if exists parsav_rels cascade;
107 72 create table parsav_rels (
108 73 relator bigint references parsav_actors(id)
109 74 on delete cascade, -- e.g. follower
110 75 relatee bigint references parsav_actors(id)
111 76 on delete cascade, -- e.g. followed
112 77 kind smallint, -- e.g. follow, block, mute
113 78
114 79 primary key (relator, relatee, kind)
115 80 );
116 81
117 -drop table if exists parsav_acts cascade;
118 82 create table parsav_acts (
119 83 id bigint primary key default (1+random()*(2^63-1))::bigint,
120 84 kind text not null, -- like, react, so on
121 85 time timestamp not null default now(),
122 86 actor bigint references parsav_actors(id)
123 87 on delete cascade,
124 88 subject bigint -- may be post or act, depending on kind
125 89 );
126 90
127 -drop table if exists parsav_log cascade;
128 91 create table parsav_log (
129 92 -- accesses are tracked for security & sending delete acts
130 93 id bigint primary key default (1+random()*(2^63-1))::bigint,
131 94 time timestamp not null default now(),
132 95 actor bigint references parsav_actors(id)
133 96 on delete cascade,
134 97 post bigint not null
135 98 );
136 99
137 -drop table if exists parsav_attach cascade;
138 100 create table parsav_attach (
139 101 id bigint primary key default (1+random()*(2^63-1))::bigint,
140 102 birth timestamp not null default now(),
141 103 content bytea not null,
142 104 mime text, -- null if unknown, will be reported as x-octet-stream
143 105 description text,
144 106 parent bigint -- post id, or userid for avatars
145 107 );
146 108
147 -drop table if exists parsav_circles cascade;
148 109 create table parsav_circles (
149 110 id bigint primary key default (1+random()*(2^63-1))::bigint,
150 111 owner bigint not null references parsav_actors(id),
151 112 name text not null,
152 113 members bigint[] not null default array[]::bigint[],
153 114
154 115 unique (owner,name)
155 116 );
156 117
157 -drop table if exists parsav_rooms cascade;
158 118 create table parsav_rooms (
159 119 id bigint primary key default (1+random()*(2^63-1))::bigint,
160 120 origin bigint references parsav_servers(id),
161 121 name text not null,
162 122 description text not null,
163 123 policy smallint not null
164 124 );
165 125
166 -drop table if exists parsav_room_members cascade;
167 126 create table parsav_room_members (
168 127 room bigint references parsav_rooms(id),
169 128 member bigint references parsav_actors(id),
170 129 rank smallint not null default 0,
171 130 admin boolean not null default false, -- non-admins with rank can only moderate + invite
172 131 title text, -- admin-granted title like reddit flair
173 132 vouchedby bigint references parsav_actors(id)
174 133 );
175 134
176 -drop table if exists parsav_invites cascade;
177 135 create table parsav_invites (
178 136 id bigint primary key default (1+random()*(2^63-1))::bigint,
179 137 -- when a user is created from an invite, the invite is deleted and the invite
180 138 -- ID becomes the user ID. privileges granted on the invite ID during the invite
181 139 -- process are thus inherited by the user
182 140 issuer bigint references parsav_actors(id),
183 141 handle text, -- admin can lock invite to specific handle
184 142 rank smallint not null default 0,
185 143 quota integer not null default 1000
186 144 );
187 145
188 -drop table if exists parsav_interventions cascade;
189 146 create table parsav_interventions (
190 147 id bigint primary key default (1+random()*(2^63-1))::bigint,
191 148 issuer bigint references parsav_actors(id) not null,
192 149 scope bigint, -- can be null or room for local actions
193 150 nature smallint not null, -- silence, suspend, disemvowel, etc
194 151 victim bigint not null, -- could potentially target group as well
195 152 expire timestamp -- auto-expires if set
196 153 );
197 154
198 -end;
155 +-- create a temporary managed auth table; we can delete this later
156 +-- if it ends up being replaced with a view
157 +%include pgsql-auth.sql%