TSQLDBRestBridge - database user login and logout?
lazarus / 38-Networking and Web Programming

----------------------------------------------------------------------
tatamata · 2019-04-02 19:10

Hello,

I have made REST web server exposing all tables in a PostgreSQL
database, by using SQLDBRestBridge
(http://wiki.freepascal.org/SQLDBRestBridge).
If I provide "postgres" username and password in the Connection
property of TSQLDBRestDispatcher, anybody can get data in JSON format,
by enetering HTTP URL in browser, for example:
http://localhost:8081/mydatabase/mytable

If I delete username and password in the Connection property, compile
and start server, now data is not available, I get error:
{
  "error" : {
    "code" : 500,
    "message" : "An unexpected exception EDatabaseError occurred with
message: Connection to database failed (PostgreSQL: fe_sendauth: no
password supplied\n)"
  }
}

How do I pass database username and password when requesting the JSON
data from REST server?

----------------------------------------------------------------------
Leledumbo · 2019-04-03 04:45

The examples are quite clear... I guess...
http://wiki.freepascal.org/SQLDBRestBridge#In_a_fcl-web_application.
Security mechanism is handled by TRestBasicAuthenticator or whatever
auth mechanism you implement. You don't prevent others from connecting
to the database, you let them connect but then you check whether
they're allowed to access the data or not.

----------------------------------------------------------------------
tatamata · 2019-04-03 08:43

Quote from: Leledumbo on April 03, 2019, 06:45:37 amThe examples are
quite clear... I guess...
http://wiki.freepascal.org/SQLDBRestBridge#In_a_fcl-web_application.
Security mechanism is handled by TRestBasicAuthenticator or whatever
auth mechanism you implement. You don't prevent others from connecting
to the database, you let them connect but then you check whether
they're allowed to access the data or not.
i changed AuthenticateUserSQL from
select uID from users where (uLogin=:UserName) and
(uPassword=:Password)
to
select usename as uID from pg_user where (usename=:UserName) and
(passwd=:Password)
I get authentication pop-up window, enter username and password (the
pop-up window repeats three times?) and still cannot get the data,
even if using postgres user which has all authorization in the
database?
What do I miss?
Is AuthBasic related to OS users or database users?

----------------------------------------------------------------------
tatamata · 2019-04-03 20:21

Quote from: tatamata on April 03, 2019, 10:43:19 amQuote from:
Leledumbo on April 03, 2019, 06:45:37 amThe examples are quite
clear... I guess...
http://wiki.freepascal.org/SQLDBRestBridge#In_a_fcl-web_application.
Security mechanism is handled by TRestBasicAuthenticator or whatever
auth mechanism you implement. You don't prevent others from connecting
to the database, you let them connect but then you check whether
they're allowed to access the data or not.
i changed AuthenticateUserSQL from
select uID from users where (uLogin=:UserName) and
(uPassword=:Password)
to
select usename as uID from pg_user where (usename=:UserName) and
(passwd=:Password)
I get authentication pop-up window, enter username and password (the
pop-up window repeats three times?) and still cannot get the data,
even if using postgres user which has all authorization in the
database?
What do I miss?
Is AuthBasic related to OS users or database users?
Can someone explain how this "basic Authentication" works?

----------------------------------------------------------------------
engkin · 2019-04-04 03:01

Based on https://www.postgresql.org/docs/9.5/view-pg-user.html:
Quotepasswd    text    Not the password (always reads as ********)
pg_user does not have the password. You can still try ********, maybe.

Edit:
As a side note, after I saw your SQL:
select usename as uID from pg_user...

I went to read the code:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TRestBasicAuthenticator.AuthenticateUserUsingSQl(IO: TRestIO;  const
UN, PW: UTF8String; out UID: UTF8String): Boolean;...    If Result
then      UID:=Q.Fields[0].AsString;.. 
As you can see, it does not care about the name of the field.

----------------------------------------------------------------------
tatamata · 2019-04-04 08:07

Thanks engkin.
I have found this article: https://grokbase.com/t/postgresql/pgsql-
general/067e47x06z/how-to-read-cleartext-user-password-from-pgsql-
database
So I made the following postgres procedure (requires pg_crypto
extension):
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE OR
REPLACE FUNCTION public.authenticate_user_text(        ulogin name,  
    upassword name)    RETURNS text    LANGUAGE 'plpgsql'     COST
100    VOLATILE AS $BODY$DECLAREls_usename ALIAS FOR $1;ls_passwd
ALIAS FOR $2;BEGIN        IF EXISTS(SELECT 1 FROM pg_shadow WHERE    
  'md5'||encode(digest(ls_passwd||ls_usename , 'md5'), 'hex') =
passwd)THEN         RETURN ls_usename;ELSE        RETURN NULL;END
IF;END;$BODY$;
So, now I use following AuthenticateUserSQL: Code: SQL
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
authenticate_user_text(:UserName, :Password);
However, now I get JSON data whatever the username and password I
enter in the authentication pop-up window, even if username and
password don't exist. I have checked the postgres function itself, it
works OK, returning username if he exists and the password is OK or
NULL if either username or password is wrong.

I don't get it...

----------------------------------------------------------------------
tatamata · 2019-04-04 08:25

OK, I think I got it
I changed the authenticate SQL and now it works:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT au
FROM authenticate_user_text(:UserName, :Password) AS auWHERE au IS NOT
NULL;

----------------------------------------------------------------------
tatamata · 2019-04-04 08:49

One more question, in the Edge browser I get pop-up window for basic
authentication, but on Chrome browser there is no authentication
dialog. How to enable basic authentication window/dialog in Chrome
browser?

----------------------------------------------------------------------
Leledumbo · 2019-04-04 13:26

Quote from: tatamata on April 04, 2019, 10:49:24 amOne more question,
in the Edge browser I get pop-up window for basic authentication, but
on Chrome browser there is no authentication dialog. How to enable
basic authentication window/dialog in Chrome browser?
Now this is weird, can you get the HTTP response? Use developer tools
and check relevant request entry.

----------------------------------------------------------------------
tatamata · 2019-04-04 14:40

Quote from: Leledumbo on April 04, 2019, 03:26:10 pmQuote from:
tatamata on April 04, 2019, 10:49:24 amOne more question, in the Edge
browser I get pop-up window for basic authentication, but on Chrome
browser there is no authentication dialog. How to enable basic
authentication window/dialog in Chrome browser?
Now this is weird, can you get the HTTP response? Use developer tools
and check relevant request entry.
Screenshots with developer tool opened attached. I'm not sure what
exactly you wish me to send...

----------------------------------------------------------------------
Leledumbo · 2019-04-05 18:31

Quote from: tatamata on April 04, 2019, 04:40:21 pmScreenshots with
developer tool opened attached. I'm not sure what exactly you wish me
to send...
In Chrome's developer tools, go to network tab then issue the request.
Select the corresponding record there and check headers tab, make sure
general and response headers are visible. There should be 2 important
fields: status code with value 401 and www-authenticate with whatever
value there (doesn't really matter in this case). If any is not
fulfilled, Chrome will not display dialog box to input
username+password.