Contents

• pg_datatype_password
  • Abstract
  • Description
  • Synopsis
  • Contribution Needed !!!
  • What is included?
  • Install
  • Dependencies
  • Copyright and License

pg_datatype_password

Abstract

PostgreSQL data type for storing blowfish encrypted and salted passwords which can be queried against clear text.

Description

This is a PostgreSQL extension which adds custom base data type called password and related operators. This module requires pgcrypto module.

User can transparently query encrypted passwords against clear password coming from web application etc. Every password is encryped using different salt, therefore even same passwords are represented with different encryped text. See [https://www.postgresql.org/docs/current/static/pgcrypto.html](pgcrypto documentation)

Synopsis

On CLI:

$ make install

On SQL:

```SQL CREATE EXTENSION pgcrypto; CREATE EXTENSION pg_datatype_password;

CREATE TABLE app_user ( id SERIAL, password password, CONSTRAINT pkey PRIMARY KEY(id) );

CREATE TRIGGER encrypt_password BEFORE INSERT OR UPDATE OF password ON app_user FOR EACH ROW EXECUTE PROCEDURE t_encrypt_password();

-- Password is inserted as encrypted because of trigger. INSERT INTO app_user (password) VALUES ('MySecret'); INSERT INTO app_user (password) VALUES ('MySecret'); ```

SQL SELECT * FROM app_user;

id | password ------|--------------- 1 | $2a$08$uTmBdGeSABWFM.scyi3DB.d0G.9Lmof6j06dc.PTdkiS4AeyoCjGu 2 | $2a$08$oTyyV8VlUMXWmWPsUbuzNOHZImN3FB79dNZkgjxaDH8/gWCU8/Jli

```SQL -- Test with clear password such as coming from web application. -- See two passwords encrypted differently even they were same as clear text, because every password is created with different salt. SELECT * FROM app_user WHERE id = 1 AND password = 'MySecret';

```

id | password ------|--------------- 1 | $2a$08$uTmBdGeSABWFM.scyi3DB.d0G.9Lmof6j06dc.PTdkiS4AeyoCjGu 2 | $2a$08$oTyyV8VlUMXWmWPsUbuzNOHZImN3FB79dNZkgjxaDH8/gWCU8/Jli

SQL -- Cannot be tested with encrypted password. SELECT * FROM app_user WHERE id = 1 AND password = '$2a$08$uTmBdGeSABWFM.scyi3DB.d0G.9Lmof6j06dc.PTdkiS4AeyoCjGu';

id | password ------|--------------- |

Contribution Needed !!!

It would be much better and elegant solution, if input function of password base type would directly encrypt given input and return encrypted result. Then there would be no need the extra trigger. Module becomes much more transparent than it's current state.

Because of PostgreSQL's ctype input limitation for procedural languages, this function have to be written in C. Please see [https://www.postgresql.org/docs/current/static/sql-createtype.html](PostgreSQL documentation)

PR for the input function is very welcome.

What is included?

• password data type
• = and <> comparison operators to compare clear text with encrypted password.
• t_encrypt_password trigger

Install

To build it, just do this:

make
make installcheck
make install

If you encounter an error such as:

"Makefile", line 8: Need an operator

You need to use GNU make, which may well be installed on your system as gmake:

gmake
gmake install
gmake installcheck

If you encounter an error such as:

make: pg_config: Command not found

Be sure that you have pg_config installed and in your path. If you used a package management system such as RPM to install PostgreSQL, be sure that the -devel package is also installed. If necessary tell the build process where to find it:

env PG_CONFIG=/path/to/pg_config make && make installcheck && make install

And finally, if all that fails (and if you're on PostgreSQL 8.1 or lower, it likely will), copy the entire distribution directory to the contrib/ subdirectory of the PostgreSQL source tree and try it there without pg_config:

env NO_PGXS=1 make && make installcheck && make install

If you encounter an error such as:

ERROR:  must be owner of database regression

You need to run the test suite using a super user, such as the default "postgres" super user:

make installcheck PGUSER=postgres

Once pg_datatype_password is installed, you can add it to a database. If you're running PostgreSQL 9.1.0 or greater, it's a simple as connecting to a database as a super user and running:

CREATE EXTENSION pg_datatype_password;

If you've upgraded your cluster to PostgreSQL 9.1 and already had pg_datatype_password installed, you can upgrade it to a properly packaged extension with:

CREATE EXTENSION pg_datatype_password FROM unpackaged;

For versions of PostgreSQL less than 9.1.0, you'll need to run the installation script:

psql -d mydb -f /path/to/pgsql/share/contrib/pg_datatype_password.sql

If you want to install pg_datatype_password and all of its supporting objects into a specific schema, use the PGOPTIONS environment variable to specify the schema, like so:

PGOPTIONS=--search_path=extensions psql -d mydb -f pg_datatype_password.sql

Dependencies

The pg_datatype_password requires pgcrypto module.

Copyright and License

See LICENSE file. Copyright (c) 2016 Özüm Eldoğan.