Initial commit

1 files changed, 34 insertions, 0 deletions

diff --git a/sql/schema.sql b/sql/schema.sql
new file mode 100644
index 0000000..76916c1
--- /dev/null
+++ b/sql/schema.sql
@@ -0,0 +1,34 @@
+-- Schema and table definitions for faktura
+--
+-- SPDX-FileCopyrightText: 2024 Eilertsens Kodeknekkeri
+-- SPDX-FileCopyrightText: 2024 Harald Eilertsen
+--
+-- SPDX-License-Identifier: AGPL-3.0-or-later
+
+create schema api;
+
+create table api.clients (
+  id serial primary key,
+  name text not null,
+  contact text,
+  email text not null,
+  phone text,
+  address text,
+  vat boolean
+);
+
+-- Set up web user for anonymous requests
+create role web_anon nologin;
+grant usage on schema api to web_anon;
+grant select on api.clients to web_anon;
+
+-- Set up trusted user for modifications
+create role faktura_user nologin;
+grant usage on schema api to faktura_user;
+grant all on api.clients to faktura_user;
+grant usage, select on sequence api.clients_id_seq to faktura_user;
+
+-- Set up authenticator user for db login
+create role authenticator noinherit login password 'password';
+grant web_anon to authenticator;
+grant faktura_user to authenticator;