| View previous topic :: View next topic |
| Author |
Message |
jlpoole
Guru
Joined: 01 Nov 2005
Posts: 491
Location: Salem, OR
|
 Posted: Thu Oct 07, 2010 4:28 am Post subject: [SOLVED] PostgreSQL: pgcrypto ? |
 |
|
I'm trying to get Apache to authenticate against a PostgreSQL database using the example in http://httpd.apache.org/docs/current/mod/mod_authn_dbd.html. It looks like the SQL used in the example:
| Code: | # mod_authn_dbd SQL query to authenticate a user
AuthDBDUserPWQuery \
"SELECT password FROM authn WHERE user = %s"
|
is not so simple. According to http://httpd.apache.org/docs/2.2/misc/password_encryptions.html, a variety of cryptographic functions is needed. When I tried using:
| Code: | AuthDBDUserPWQuery \
"SELECT '{SHA}'||encode(digest(cast('password' as String),cast('sha1' as String)),'base64') FROM authn WHERE dbuser = %s" |
in a regular SQL session, postgres complained:
| Code: | ERROR: function digest(text, text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 24 |
My installation of PostgreSQL is:
| Code: | Installed versions: 8.4.4-r1(8.4)!t(12:14:13 AM 10/06/2010)(kernel_linux nls perl python -doc -linguas_af -linguas_cs -linguas_de -linguas_es -linguas_fa -linguas_fr -linguas_hr -linguas_hu -linguas_it -linguas_ko -linguas_nb -linguas_pl -linguas_pt_BR -linguas_ro -linguas_ru -linguas_sk -linguas_sl -linguas_sv -linguas_tr -linguas_zh_CN -linguas_zh_TW -pg_legacytimestamp -selinux -tcl -uuid -xml)
Homepage: http://www.postgresql.org/
Description: PostgreSQL server
|
I'm not seeing anything that suggests the pgcrypto module is included; moreover the error message "ERROR: function digest(text, text) does not exist" when compared with the "F.23. pgcrypto" documentation referenced above:
| Code: | F.23.1. General hashing functions
F.23.1.1. digest()
digest(data text, type text) returns bytea
digest(data bytea, type text) returns bytea
|
suggests that it is not.
If my conclusions above are correct, then it looks like PostgreSQL is not Apache friendly, e.g. the mod_authn_dbd will not work with the current install of Postgres because the requisite cryptographic module, pgcrypto, is not present.
Suggestions?
Should I file a bug, or is this too esoteric? Seems to me Apache friendliness for PostgreSQL should be a default configuration.
--- UPDATE --
Here's what I did to activate the pgcrypto:
| Code: | plug postgresql-8.4 # pwd
/usr/share/postgresql-8.4
plug postgresql-8.4 #psql -d apacheauth -U [username w/super privileges] -f ./contrib/pgcrypto.sql
SET
CREATE FUNCTION
...
CREATE FUNCTION
plug postgresql-8.4 #
|
Now the function md5() works.
Last edited by jlpoole on Thu Oct 07, 2010 11:34 am; edited 1 time in total |
|
| Back to top |
|
 |
jlpoole
Guru
Joined: 01 Nov 2005
Posts: 491
Location: Salem, OR
|
| Posted: Thu Oct 07, 2010 5:20 am Post subject: |
|
|
I ended up using www-apache/mod_auth_pgsql to get security up and running. I returned to further explore at http://www.postgresql.org/docs/8.4/interactive/contrib.html.
The "Appendix F. Additional Supplied Modules" introduction provides the answer to my question above:
| Quote: | This appendix contains information regarding the modules that can be found in the contrib directory of the PostgreSQL distribution. These include porting tools, analysis utilities, and plug-in features that are not part of the core PostgreSQL system, mainly because they address a limited audience or are too experimental to be part of the main source tree. This does not preclude their usefulness.
When building from the source distribution, these modules are not built automatically. You can build and install all of them by running:
gmake
gmake install
in the contrib directory of a configured source tree; or to build and install just one selected module, do the same in that module's subdirectory. |
|
|
| Back to top |
|
|
jlpoole
Guru
Joined: 01 Nov 2005
Posts: 491
Location: Salem, OR
|
| Posted: Thu Oct 07, 2010 12:41 pm Post subject: |
|
|
Adding to the knowledge tree here.
The instructions are scattered and not consolidated, so I thought I would share what it took for me to get mod_authn_dbd working in an Apache/PostgreSQL environment with virtual hosts
in /etc/apache2/vhosts.d/SomeVirtualHost.include file add:
| Code: |
# mod_dbd configuration
DBDriver pgsql
DBDParams "host=localhost dbname=apacheauth user=apache password=test"
DBDMin 4
DBDKeep 8
DBDMax 20
DBDExptime 300
<Directory "/var/www/SomeVirtualHost/public/test_mod_authn_dbd">
# core authentication and mod_auth_basic configuration
# for mod_authn_dbd
AuthType Basic
AuthName "Test of mod_authn_dbd"
AuthBasicProvider dbd
AuthDBDUserPWQuery \
"SELECT password FROM authn WHERE dbuser = %s"
require valid-user
</Directory>
|
Then on the database side:
| Code: |
CREATE DATABASE apacheauth
WITH OWNER = postgres
ENCODING = 'UTF8'
TABLESPACE = pg_default
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
CONNECTION LIMIT = -1;
COMMENT ON DATABASE apacheauth IS 'Apache authentication test';
CREATE TABLE authn
(
id integer NOT NULL,
dbuser text,
"password" text,
CONSTRAINT authn_pkey PRIMARY KEY (id),
CONSTRAINT authn_dbuser_key UNIQUE (dbuser)
)
WITH (
OIDS=FALSE
);
ALTER TABLE authn OWNER TO apache;
|
Then the trick here is to use the digest and encode functions around the password and prefix it with "{SHA}" to meet Apache's format of encrypting password within the mod_authn_dbd in an insert statement for user "tburton" password "test":
| Code: |
insert into authn
(id, dbuser, password)
VALUES
(999,'tburton','{SHA}'||encode(digest('test','sha1'),'base64'));
|
Note: if you use the AuthDBDUserRealmQuery, the construction of the password wrappers is different and the recipe is set forth at http://httpd.apache.org/docs/2.2/misc/password_encryptions.html
Hopefully this will save someone hours of trials and research as it cost me. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
Portage & Programming
