D9ZcaFbQ

1--
2-- Create user table
3--
4
5DROP TABLE IF EXISTS users;
6
7DROP SEQUENCE IF EXISTS public.user_seq;
8
9CREATE SEQUENCE public.user_seq
10  INCREMENT BY 1
11  START WITH 1
12  NO MAXVALUE
13  NO MINVALUE
14  CACHE 1;
15
16CREATE TABLE users (
17    "id" integer DEFAULT nextval('user_seq'::regclass) NOT NULL,
18    "username" character varying(30) NOT NULL,
19    "printName" character varying(50),
20    "password" character varying(80) NOT NULL,
21    "accessLevel" integer NOT NULL
22);
23
24INSERT INTO users ("id", "username", "password", "accessLevel")
25VALUES (1, 'admin', crypt('passwordgoeshere', gen_salt('bf')), 99);
26
27CREATE OR REPLACE FUNCTION login(_userid text, _pwd text, OUT _username text)
28 RETURNS TEXT
29 LANGUAGE plpgsql
30 SECURITY DEFINER
31AS $$
32BEGIN
33  SELECT username into _username from users 
34    WHERE users.username=lower(_userid)
35    AND password = crypt(_pwd, users, password);
36END;
37$$
38
39REVOKE SELECT (password) ON users FROM public;
40	
41DROP TABLE
42DROP SEQUENCE
43CREATE SEQUENCE
44CREATE TABLE
45INSERT 0 1
46ERROR:  syntax error at or near "REVOKE"
47LINE 12: REVOKE SELECT (password) ON users FROM public;
48         ^