Re: [cobalt-users] RaQ4 - cobalt-psql Database setup & Interaction with system ?

[Duncan Laurie <duncan@xxxxxxxxxx>]

Hi Joerg,

| 
| This is the best support I ever Had. Thx a lot. If Ever you pass by
| Switzerland, let me know. I ow you a beer ;)
| 

I wouldn't quite call it support... in fact I need to remember and forward
the instructions to our support department.  ;)  I'm just the one
responsible for our particular postgresql setup, and with the various
postgresql problems we enountered on the RaQ3 I tried to make recovery as
painless as possible, even if it is an undocumented feature.  It hasn't
come up before now; this finally made me document how it works.  However
if I could find a way to Europe I would take you up on that beer...

|
| I have however another Question. Using psql, I'm not able to log in as
| used admin or postgres (Users automatically added by the scripts).
| 
| However, looking the pg_shadow Entry, postgres does not have a passwdord
| set, but admin does.
| 
| cobalt=# select * from pg_shadow;
|  usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |      passwd      | valuntil 
| ----------+----------+-------------+----------+----------+-----------+------------------+----------
|  postgres |       26 | t           | t        | t        | t         |                  | 
|  admin    |       27 | f           | f        | f        | f         | 12345bacdedf | 
| (2 rows)
| 
| PS: I falsified the password here ;)
| 
| Does the Password generated fo admin is a special one just for testing
| the availability of the postgres-sql server ?
| 
| Also, in not beeing able to create new databases cobalt seems to have
| tried to protect itself. But it didn't work right (I managed to get
| postgres-user access on it by fiddling around with pg_hba.conf).
| 
| Am I right in the assumption that the password in the pg_shadow has
| nothing to do with the System password and used solely for swatch ? and
| that an admin User is not able to connect the Database ?
| 

Oops, I seem to have left out some details in my last message...  You are
correct in that the admin password in pg_shadow is unrelated to the system
password.  It is generated when the database is initialized, and all this
work is done by a script in /usr/local/sbin/setup-postgres, which in turn
gets called by the initscript if the dir /var/lib/pgsql/data/ is missing.

Access to the database is controlled by the pg_hba.conf file.  By default
it contains the line "local all trust", which allows any local user to
connect to any database without authentication.  Also by default the
postgres superuser account has no password defined.  This combination is
particulary bad as it would allow anyone with shell access to administer
the entire postgresql setup.  So during initialization I change things
slightly by commenting this line, which prevents the postgres user from
logging in at all, and adding the "local cobalt crypt" line, which allows
only local connections with password authentication to the cobalt database.

However before this change is made the "cobalt" database is created, and
the SQL commands in /etc/cobalt/postgres.db are executed by the postgres
superuser.  This creates the admin account and the necessary tables
used by the GUI and swatch.  Then a password is generated (first 16 bytes
from md5sum of random seed) and saved in /etc/cobalt/.meta.id for use by
the perl module Cobalt::Meta.pm (for the GUI) and ActiveMonitor (swatch).
This file is mode 600 and owned by root, since both the admin web server
and the cron daemon run as root.  Luckily this whole process is only done
the first time you power on the system, or if you choose to reset it
by removing the data directory.

As for making changes to postgresql (adding users, databases, etc.) you
are right again in that it was intentionally disabled, but more for the
protection of the system than for our benefit.  The easy way to fix this
is to add a line in pg_hba.conf that reads "local template1 trust" (the
template1 database is created by default for the superuser).  Then you
can connect with:

	psql -U postgres template1

and perform any admin tasks.  However, I would recommend that the first
thing you do is assign the postgres account a password:

	ALTER USER postgres WITH PASSWORD 'password';

Then change the newly added pg_hba.conf line to "local template1 crypt" so
you don't leave the database open to modification by local users.  It is
also possible to change the password assigned on the "admin" account, in
order to make psql access easier from the shell (rather than typing the
16 random chars).  You can either use the ALTER USER command above and put
the same value in .meta.id, or use the utility function provided that will
take care of synchronizing them for you:

	perl -MCobalt::Meta -e 'Cobalt::Meta::setpw("password")'

One final bit of advice if you are looking to access the database from the
network.  After setting up an appropriate "host ..." line in pg_hba.conf
(there are many good examples in the file) you will need to start the
daemon with TCP/IP connections enabled.  To do this edit the initscript
in /etc/rc.d/init.d/postgresql and change the line:

  su postgres -c "nohup /usr/bin/postmaster >> /var/log/postgresql 2>&1 &"

to start postmaster with the -i option:

  su postgres -c "nohup /usr/bin/postmaster -i >> /var/log/postgresql 2>&1 &"

All the documentation for postgresql is quite extensive and is present in
HTML format in /usr/doc/postgresql-7.0.2/.  If you run into any problems
check out the logfile in /var/log/postgresql as it generally contains any
errors.  If you want more debugging to be saved then edit the file
/var/lib/pgsql/data/pg_options and increase the "verbose" and/or "query"
values.


good luck,
-duncan