User management tutorial broken

[TomChv]

Context

I'm learning postgREST so it's maybe my fault but then I'll certainly open a PR to update doc regarding to my issue. I'm trying to create a REST API with basic auth system.

I've copy past the SQL given in the tutorial but it's doesn't work :/

Reproduce

SQL schema

-- We put things inside the basic_auth schema to hide
-- them from public view. Certain public procs/views will
-- refer to helpers and tables inside.
create schema if not exists basic_auth;

create table if not exists
basic_auth.users (
  email    text primary key check ( email ~* '^.+@.+\..+$' ),
  pass     text not null check (length(pass) < 512),
  role     name not null check (length(role) < 512)
);

create or replace function
basic_auth.check_role_exists() returns trigger
  language plpgsql
  as $$
begin
  if not exists (select 1 from pg_roles as r where r.rolname = new.role) then
    raise foreign_key_violation using message =
      'unknown database role: ' || new.role;
    return null;
  end if;
  return new;
end
$$;

drop trigger if exists ensure_user_role_exists on basic_auth.users;
create constraint trigger ensure_user_role_exists
  after insert or update on basic_auth.users
  for each row
  execute procedure basic_auth.check_role_exists();

create extension if not exists pgcrypto;

create or replace function
basic_auth.encrypt_pass() returns trigger
  language plpgsql
  as $$
begin
  if tg_op = 'INSERT' or new.pass <> old.pass then
    new.pass = crypt(new.pass, gen_salt('bf'));
  end if;
  return new;
end
$$;

CREATE TYPE basic_auth.jwt_token AS (
  token text
);

drop trigger if exists encrypt_pass on basic_auth.users;
create trigger encrypt_pass
  before insert or update on basic_auth.users
  for each row
  execute procedure basic_auth.encrypt_pass();

create or replace function
basic_auth.user_role(email text, pass text) returns name
  language plpgsql
  as $$
begin
  return (
  select role from basic_auth.users
   where users.email = user_role.email
     and users.pass = crypt(user_role.pass, users.pass)
  );
end;
$$;

create or replace function
login(email text, pass text) returns basic_auth.jwt_token
  language plpgsql
  as $$
declare
  _role name;
  result basic_auth.jwt_token;
begin
  -- check email and password
  select basic_auth.user_role(email, pass) into _role;
  if _role is null then
    raise invalid_password using message = 'invalid user or password';
  end if;

  select sign(
      row_to_json(r), 'mysecret'
    ) as token
    from (
      select _role as role, login.email as email,
         extract(epoch from now())::integer + 60*60 as exp
    ) r
    into result;
  return result;
end;
$$;

-- the names "anon" and "authenticator" are configurable and not
-- sacred, we simply choose them for clarity
create role anon;
create role authenticator noinherit;
grant anon to authenticator;

grant usage on schema public, basic_auth to anon;
grant select on table pg_authid, basic_auth.users to anon;
grant execute on function login(text,text) to anon;

Postgrest configuration

# Db configuration
db-uri = # URI
db-schema = "basic_auth"
db-anon-role = "anon"

# Log configuration
log-level = "info"

Postgrest version

postgrest -h
# Usage: postgrest [-e|--example] [--dump-config | --dump-schema] FILENAME
#  PostgREST 9.0.0 / create a REST API to an existing Postgres database

Issue

Here's my request :

curl -X POST -H "Content-Type: application/json" --data-raw '{"email":"[email protected]","pass":123456}' localhost:3000/rpc/login
{"message":"Could not find the basic_auth.login(email, pass) function or the basic_auth.login function with a single unnamed json or jsonb parameter in the schema cache","hint":"If a new function was created in the database with this name and parameters, try reloading the schema cache."}

Did someone has met this issue? I can't figure out a solution.

[wolfgangwalther]

Did you try reloading the schema cache after creating the login function?

https://postgrest.org/en/stable/schema_cache.html#stale-function-signature

https://postgrest.org/en/stable/schema_cache.html#the-stale-schema-cache

https://postgrest.org/en/stable/schema_cache.html#schema-reloading

[TomChv]

Thanks for your answers @wolfgangwalther :rocket:

Yes I did :

# Reload cache
killall -SIGUSR1 postgrest

# Rerun query
curl -X POST -H "Content-Type: application/json" --data-raw '{"email":"[email protected]","pass":123456}' localhost:3000/rpc/login
{"message":"Could not find the basic_auth.login(email, pass) function or the basic_auth.login function with a single unnamed json or jsonb parameter in the schema cache","hint":"If a new function was created in the database with this name and parameters, try reloading the schema cache."}

PostgREST log

24/Dec/2021:14:33:50 +0100: Attempting to connect to the database...
24/Dec/2021:14:33:50 +0100: Connection successful
24/Dec/2021:14:33:50 +0100: Listening on port 3000
24/Dec/2021:14:33:50 +0100: Config re-loaded
24/Dec/2021:14:33:50 +0100: Listening for notifications on the pgrst channel
24/Dec/2021:14:33:50 +0100: Schema cache loaded
24/Dec/2021:14:34:05 +0100: Attempting to connect to the database...
24/Dec/2021:14:34:05 +0100: Connection successful
24/Dec/2021:14:34:05 +0100: Config re-loaded
24/Dec/2021:14:34:05 +0100: Schema cache loaded
127.0.0.1 - - [24/Dec/2021:14:34:19 +0100] "POST /rpc/login HTTP/1.1" 404 - "" "curl/7.77.0"

I also tried to delete / recreate my database and stop / rerun postgrest but I still meet that issue.

Here's how I start my database :

docker run --rm --name workshop-postgrest \
  -e POSTGRES_USER=<username> \
  -e POSTGRES_PASSWORD=<password> \
  -e POSTGRES_DB=workshop-postgrest \
  -p 5432:5432 \
  -d postgres

It didn't solved my issue, do you have another idea?

[laurenceisla]

Maybe the function is being created in the public schema by default. Try specifying the basic_auth schema instead:

create or replace function
basic_auth.login(email text, pass text) returns basic_auth.jwt_token  -- Add basic_auth here
  language plpgsql
  ...

[wolfgangwalther]

A good find, @laurenceisla. However, I think the inconsistency is here:

-- We put things inside the basic_auth schema to hide
-- them from public view.
....
db-schema = "basic_auth"

The way the code is set up, db-schema should be set to public in this case.

[steve-chavez]

db-schema = "basic_auth" The way the code is set up, db-schema should be set to public in this case.

Yes, basic_auth was never meant to be exposed on the API. A function like public.login should use basic_auth internally.

[clust3rsekt0r]

Hi, sorry I'm new to everything here in the process of learning, I was reading basic user authentication topic, and there is a problem with function login,

begin
  -- check email and password
  select basic_auth.user_role(email, pass) into _role;
  if _role is null then
    raise invalid_password using message = 'invalid user or password';
  end if;

basic_auth.user_role table does not exist on the schema, it is just the table users I fix and put the users table, but yet the problem remain the same it seems it cannot get the values of email and pass, the columns do not exist.

[TomChv]

Hey!

What did you do? Can you share us a complete repro case?

[clust3rsekt0r]

Hi, this is my setup,

I'm using postgresql 14.2 docker alpine docker, and current version for docker PostgREST these are my docker-compose settings, everything works fine except the authentication, with the actual documentation LOGIN function I get the regular error from the api.

'invalid user or password'

after I change the login code basic_auth.user_role which do not exist in the schema for a regular select pointing to basic_auth.users I get this response from the api.

{
    "hint": null,
    "details": "It could refer to either a PL/pgSQL variable or a table column.",
    "code": "42702",
    "message": "column reference \"email\" is ambiguous"
}

$$
declare
  _role name;
  result basic_auth.jwt_token;
begin
  -- check email and password
  select email, pass from basic_auth.users into _role;
  if _role is null then
    raise invalid_password using message = 'invalid user or password';
  end if;

This is my docker-compose.yml configuration.

version: '3'
services:
  server:
    image: postgrest/postgrest
    ports:
      - "3000:3000"
    environment:
      PGRST_DB_URI: postgres://postgres:mypassword@db:5432/framy
      PGRST_DB_SCHEMAS: "public, basic_auth"
      PGRST_DB_ANON_ROLE: postgres #In production this role should not be the same as the one used for the connection
      PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
    depends_on:
      - db
  db:
    image: postgres:14.2-alpine
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: framy
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: mypassword

    volumes:
      - "./pgdata:/var/lib/postgresql/data"

[clust3rsekt0r]

I was able to fix the problem in the login function, now I get this error from the function

I change this line on the function: -- check email and password SELECT basic_auth.user_role(email, pass) INTO _role;

for:

SELECT users.email, users.pass from basic_auth.users INTO _role;

Now I get this error:

select sign( row_to_json(r), 'Sek!(&(T0RClus!(&(T0R' ) as token from ( select _role as role, login.email as email, extract(epoch from now())::integer + 60*60 as exp ) r CONTEXT: PL/pgSQL function login(text,text) line 12 at SQL statement SQL state: 42883

[wolfgangwalther]

I checked the user management tutorials - as of now, they seem to work alright.