--
-- Create user table
--

DROP TABLE IF EXISTS users;

DROP SEQUENCE IF EXISTS public.user_seq;

CREATE SEQUENCE public.user_seq
  INCREMENT BY 1
  START WITH 1
  NO MAXVALUE
  NO MINVALUE
  CACHE 1;

CREATE TABLE users (
    "id" integer DEFAULT nextval('user_seq'::regclass) NOT NULL,
    "username" character varying(30) NOT NULL,
    "printName" character varying(50),
    "password" character varying(80) NOT NULL,
    "accessLevel" integer NOT NULL
);

INSERT INTO users ("id", "username", "password", "accessLevel")
VALUES (1, 'admin', crypt('passwordgoeshere', gen_salt('bf')), 99);

CREATE OR REPLACE FUNCTION login(_userid text, _pwd text, OUT _username text)
 RETURNS TEXT
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $$
BEGIN
  SELECT username into _username from users
    WHERE users.username=lower(_userid)
    AND password = crypt(_pwd, users, password);
END;
$$

REVOKE SELECT (password) ON users FROM public;

DROP TABLE
DROP SEQUENCE
CREATE SEQUENCE
CREATE TABLE
INSERT 0 1
ERROR:  syntax error at or near "REVOKE"
LINE 12: REVOKE SELECT (password) ON users FROM public;
         ^