Re: Almost done with manual for Gentoo - but need some help

["David A. Bandel" <..hidden..>]

On 9/18/07, Joseph <..hidden..> wrote:
> On Tue, 2007-09-18 at 09:39 -0700, Joshua D. Drake wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > Jeff Kowalczyk wrote:
> > > Joshua D. Drake wrote:
> > >>> At this point to change or setup password for this user "ledgersmb"
> > >>> and database "ledgersmb_db" do I type:
> > >>> UPDATE users_conf SET password = md5('MYPASSWORD') WHERE id = 1;
> > >> Wrong... use:
> >
> > >   If you prefer to work in postgresql's psql console, the equivalent SQL
> > >   statement to create the ledgersmb role is:
> > >
> > >   => CREATE ROLE ledgersmb LOGIN PASSWORD 'MYROLEPASSWORD' NOINHERIT
> > >   CREATEDB;
> > >
> > >   Further commands and database interaction should be conducted using
> > >   the new LedgerSMB admin role 'ledgersmb'.
> > >
> > >
> > > Do we need a note for postgresql-8.0?
> >
> > Yeah probably, as 8.0 as USER/GROUP versus ROLE. However it should be
> > noted that as of 1.3, 8.0.x is deprecated.
> >
> > Joshua D. Drake
>
> My md5 authentication somehow is not working:
>
> If I change in pg_hba.conf to:
> local   all         all                                      trust
> host    all         all         127.0.0.1/32        trust
>
> I can login from Firefox create dataset etc.
>
> If I change pg_hba.conf to md5:
> local     all         all                                  md5
> 10.0.0.103    all   all                                  md5
>  host    all         all         127.0.0.1/32             md5
>
> (I'm login in from computer 10.0.0.103 hence the extra line).
> but I can not login.
>
> Here is my beginning:
> For postgresql 8.0.x
>
> $ createuser --createdb -U postgres --pwprompt --encrypted ledgersmb
> Enter password for new user:
> Enter it again:
> Shall the new user be allowed to create more new users? (y/n) y
> CREATE USER
>
> $ createdb -U ledgersmb -O ledgersmb ledgersmb_db
> CREATE DATABASE
>
> $ psql -U ledgersmb -d ledgersmb_db
> -f /usr/local/ledgersmb/sql/Pg-central.sql
>
> $ psql -U ledgersmb -d ledgersmb_db
> ledgersmb_db=# UPDATE users_conf SET password = md5('syscon') WHERE id = 1;
> ledgersmb_db=# \q
>       *
> [globaldb]
> # These paramaters *must* be set correctly
> # for LedgerSMB >= 1.2 to work
> DBname       = ledgersmb_db
> DBhost       = localhost
> DBport       = 5432
> DBUserName   = ledgersmb
> DBPassword   = syscon
>
> Where is my mistake?
>
> I've check my posgresql and it is compiled with "ssl" support so it
> should support md5 as well.
> [ebuild   R   ] dev-db/postgresql-8.0.13  USE="nls pam perl python
> readline ssl xml zlib -doc -kerberos -pg-intdatetime (-selinux) -tcl
> -test" 0 kB
> --

This "triple user" concept is going to cross someone's eyes.

You have 3 users here:
UNIX system user:  this is the user who owns the Apache process,
probably apache or www-data depending on your distro (could be some
other user though)

PostgreSQL user:  that user authorized to connect to the server and
request a connection to the database

Database user:  user authorized to: View, modify, create/destroy data.

Then LedgerSMB also has users with certain privileges in the tables
(on top of all the above).

The pg_hba.conf table is used by the server to see who is connecting
to the server.  This will normally be the UNIX user unless you pass
the -U parameter during the connect.  So the pg_hba.conf file must
reference the owner of the web server process (because that's who is
posting the data).  This is why trust works, but md5 doesn't.

If you make the Apache process owned by ledgersmb, you will at least
be taking the first step toward being permitted to connect to the
server -- the other part being you must pass in the md5 password (not
sure if Postgres will force a password prompt through Apache).  On a
command line you would either use --password or hope Postgres knows to
prompt.  What might work better for you is to launch Apache via a
wrapper that sets PGUSER and that user has a .pgpass file to pass in
the password. (Create a UNIX user ledgersmb, create a wrapper script
or just add two lines: PGUSER="ledgersmb";export PGUSER; to your
apache initialization file and ensure in ledgersmb's home directory
you have a .pgpass file with appropriate permissions, with the
password inside).

Otherwise, you could use the "ident sameuser", in which case you need
to ensure you're running ident and ident passes the Apache process UID
in to Postgres.

Or just use trust.

Once inside the server, you will connect to the appropriate table.
That connection comes via the LedgerSMB software which passes the
parameters from the user_conf table in the ledgersmb database.  That
is, the software, once connected to the server (as above), grabs a
username and password from the ledgersmb database user_conf table and
uses that to authenticate to the server the information to connect to
a table.

UNIX user (via Apache process)
Postgres server
appropriate table

When you are creating the user/password in ledgersmb.user_conf, that
is for the database connection, not the server connection.

Now, which part was clear as mud?  It may take a few readings to wrap
your head around all the layers of authentication that go on here.

HTH,

David A. Bandel
-- 
Focus on the dream, not the competition.
            - Nemesis Air Racing Team motto