Problem with Snap.Snaplet.Auth.Backends.PostgresqlSimple

[Tero Laitinen]

Hi,

I'm having some trouble with getting postgresql auth backend to work.

First minor issue with testing was that connection to Postgresql server does seem to work without password even though I have 

local all all trust

in /etc/postgresql/9.1/pg_hba.conf

$ snap-test

no port specified, defaulting to port 8000

snap-test: 

Initializer threw an exception...

SqlError {sqlState = "", sqlNativeError = -1, sqlErrorMsg = "fe_sendauth: no password supplied\n"}

Maybe it's a configuration issue. Anyway, after setting a password I managed to start the application

$ snap-test

no port specified, defaulting to port 8000

NOTICE:  CREATE TABLE will create implicit sequence "snap_auth_user_uid_seq" for serial column "snap_auth_user.uid"

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "snap_auth_user_pkey" for table "snap_auth_user"

NOTICE:  CREATE TABLE / UNIQUE will create implicit index "snap_auth_user_login_key" for table "snap_auth_user"

Initializing app @ /

Initializing heist @ /

...loaded 7 templates from /home/tero/af/source/test/snaplets/heist/templates

Initializing CookieSession @ /sess

Initializing postgresql-simple @ /db

Initializing postgresql-auth @ /auth

Listening on http://0.0.0.0:8000/

After this I created a test user "test" and "test".

In the browser and in the access log everything seems to work okay:

$ cat log/access.log 

127.0.0.1 - - [18/Dec/2012:16:37:06 +0200] "GET / HTTP/1.1" 200 - - "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

127.0.0.1 - - [18/Dec/2012:16:37:06 +0200] "GET /screen.css HTTP/1.1" 200 - "http://localhost:8000/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

127.0.0.1 - - [18/Dec/2012:16:37:06 +0200] "GET /favicon.ico HTTP/1.1" 404 - - "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

127.0.0.1 - - [18/Dec/2012:16:37:08 +0200] "GET /new_user HTTP/1.1" 200 - "http://localhost:8000/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

127.0.0.1 - - [18/Dec/2012:16:37:08 +0200] "GET /favicon.ico HTTP/1.1" 404 - - "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

127.0.0.1 - - [18/Dec/2012:16:37:10 +0200] "POST /new_user HTTP/1.1" 302 0 "http://localhost:8000/new_user" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

127.0.0.1 - - [18/Dec/2012:16:37:10 +0200] "GET / HTTP/1.1" 200 - "http://localhost:8000/new_user" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

127.0.0.1 - - [18/Dec/2012:16:37:10 +0200] "GET /favicon.ico HTTP/1.1" 404 - - "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

127.0.0.1 - - [18/Dec/2012:16:37:13 +0200] "POST /login HTTP/1.1" 200 - "http://localhost:8000/" "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

127.0.0.1 - - [18/Dec/2012:16:37:13 +0200] "GET /favicon.ico HTTP/1.1" 404 - - "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.11 (KHTML, like Gecko) Chrome/23.0.1271.97 Safari/537.11"

However, nothing is added to the table snap_auth_user:

test=# select count(*) from snap_auth_user;

 count 

-------

     0

(1 row)

and the login obviously fails.

Any ideas?

[Leon Smith]

The snap_auth_user table is irrelevant here. You probably need to
create a database user that the webapp can connect as, and this
database user will need appropriate permissions.

http://www.postgresql.org/docs/9.1/static/sql-createuser.html

All that the "local all all trust" line does is allow anybody to
connect via unix domain sockets and assert they are any database user
they wish to be, including the default superuser account "postgres".
If you don't specify a username in the connection string, then
libpq will use the name of the local unix user account that the
process is running as.

http://www.postgresql.org/docs/9.1/static/libpq-connect.html

It may be better to use "local all all ident" or somesuch, this
allows local unix users to connect to postgres via unix domain sockets
as the database user of the same name.

Best,
Leon

> --
>
>
>

[Leon Smith]

Ok, I reviewed what snaplet-postgresql-simple does to manage the
connection string, and it goes through ConnectInfo (whereas I mostly
ignore that myself and use my own connection strings.) The problem
is probably with that; among other things I don't think you can
connect via unix domain sockets via ConnectInfo records IIRC.

It would be a good idea to be able to supply snaplet-postgresql-simple
with a raw connection string(s) to use; this would allow the full
range of connection and authentication options to be used.

I mean, for local development an empty string often suffices, and if
you have things set up reasonably it just works.

Best,
Leon

[Tero Laitinen]

If the application is able to create tables in a database, doesn't this imply that it should also be able to insert rows in them?

The HTTP request for creating a new user returned without error (code 200 OK) but no user was created. This should not happen. If a row could not be inserted for some reason, some kind of error message should be shown to the user or at least in the server logs. 

[Leon Smith]

Yeah, you are right. My bad.

It would seem that the issue would be with Snap then, possibly
relating to your use of it. And on that count, I can't really help.
Hopefully Doug will reply to this message soon.

(And doug, I'd also highlight my comment that the
snaplet-postgresql-simple should allow users to specify connection
strings, not just connectInfo records.)

> --
>
>
>

[MightyByte]

I'll try to look at this soon.

--

[MightyByte]

I'm working on shoring up snap issues and just now got back to looking at this.  It seems like this is an issue specific to snaplet-postgresql-simple.  Could you make a more detailed description of how to reproduce this and open an issue for snaplet-postgresql-simple?  A patch allowing the use of connection strings would be even better. :)