[wplug] postgres and mediawiki

[Bill Moran]

In response to "Michael H. Semcheski" <mhsemcheski at gmail.com>:

> Hello,
> 
> I'm trying to setup MediaWiki with a postgres backend, and there
> hasn't been much progress.
> 
> I'm fairly confused by the security system in Postgres, for one.  I've
> never used it, and have not scoured the documentation as thoroughly as
> I probably should.
> 
> There are two things I want to accomplish:
> 
> First, set up things so that I can connect as the superuser (and thus
> do anything I want or need to on any database) from my unix account
> using pgadmin3.  I'm really not sure what has to go in pg_hba.conf and
> pg_ident.conf for this.

Keep in mind that pg_hba.conf controls host-based access.  This is more
like firewall rules than filesystem permissions.

Basically, PostgreSQL will compare you connection parameters to the settings
in pg_hba, and use that to determine how to authenticate you.  For example,
a line such as:
hostssl wiki joe 192.168.5.0/24 password
Will cause anyone connecting via SSL to database wiki as user joe from
the specified subnet to be challenged for a password.  If there's no line
in pg_hba matching the connection attempt, it will be rejected.

> Second, allow the php configuration part of mediawiki to login as the
> user wikidb_user, and modify the database wikidb.  The script will
> create the necessary tables, etc.
> 
> For both of these cases, the user that will be accessing them will be
> local.  (Although I think pgadmin3 seems to connect via TCP).  Also,
> the mediawiki config asks for a database name and (under postgres
> specific options) a schema.  What schema?
> 
> I'm able to login with psql, and I created the database wikidb and the
> user wikidb_user (who owns wikidb).

Local connections are either through a Unix socket (usually /tmp/psql.5432)
or via the loopback interface on port 5432.

The unix domain socket is preferable, since it doesn't incur the overhead
of the TCP stack, but many programs are too stupid to understand the
connection syntax (Drupal, for example).  Try using hostname=/tmp in the
wikipedia config, if it doesn't work, then use hostname=127.0.0.1.

If you're not doing _any_ remote connections, I recommend you tweak
postgresql.conf and change to listen_address='127.0.0.1'.  This will
cause PG to not even open a listening socket on the outside.

Then, in your pg_hba, two lines should suffice:
local   all         all                               password
host    all         all         127.0.0.1/32          password

If you forget a password, you can always switch "password" to "trust" to
make the password unnecessary, but I don't recommend you run like that,
even with the thing bound only to localhost.

When creating accounts, two of the biggest problems I have are forgetting
to grant login permissions and forgetting to set a password:
ALTER ROLE wikidb_user WITH LOGIN;
ALTER ROLE wikidb_user WITH PASSWORD 'somepassword';

Neither is the default when an account is first created.

Once you've got that, you'll be dealing with fairly standard SQL ACLs.  Have
a look at the docs for GRANT and REVOKE.  Also, ALTER DATABASE wikidb OWNED
BY wikidb_user; may be necessary to get things rolling.

If you're still having trouble, have a look at the logging section of
postgresql.conf.  You can enable extra-verbose logging regarding the login
process, which is usually pretty helpful.  Don't forget to restart the
server after making config changes.

Hope this gets you rolling.  If you get stuck, post again.

-- 
Bill Moran
http://www.potentialtech.com