Generating Addresses in PostgreSQL

During my initial dirty-hack of the coindroids system for defcon/defcoin, we took a lazy step and just pre-populated an address table with a boatload of addresses generated outside the system. While this was great for preparing for a three day conference, it obviously isn't ideal for a real production system.

The last thing you want to do is lose users because you didn't have an address ready for them when they needed it.

So, there are a number of ways we can generate addresses on the fly but I have an obsession with PostgreSQL, so that is where we are doing it. You could theoretically use the same python code anywhere within your application layer and get the same results.

Getting started, we need:

  • PostgreSQL 9.3+
  • PL/Python
  • pycoin

For simplicity sake, I'm going to assume you have PostgreSQL installed and setup already. If you don't, it's one of the best documented projects on the Internet so I have faith that you can get that done and come back.

You may not have PL/Python installed though. You can try and add the language to the database with this SQL command:

$ psql
abstract=# CREATE LANGUAGE plpython2u;  

If you receive an error then you need to install the module. If you compiled Postgres yourself, you will need to recompile with --with-python. Alternatively, if you have have the luxury of a package manager then something along the lines of sudo apt-get install postgresql-plpython-9.3 will be much easier. Once that is complete, try the create statement above again.

Now our Postgres install supports functions written in python! We can write up a quick test and try it out.

CREATE OR REPLACE FUNCTION add_with_python(c integer)  
RETURNS character varying  
AS $$  
    a = 1
    b = 2
    return a+b+c
$$ LANGUAGE plpython2u;

SELECT add_with_python(3);  

So why go to this trouble? There is a wonderful library called pycoin that has all the features we could ever want specifically HD Wallets and support for many cryptocurrency networks.

Let's get that installed next.

$ git clone
$ cd pycoin
$ sudo make

We can test out the install by calling the ku (Key Utility) command that comes along with the library. This utility is basically a proof of concept for the library but also is super useful.

$ ku create
input                        : create  
network                      : Bitcoin mainnet  
netcode                      : BTC  
wallet key                   : xprv9s21ZUoithesenumbersaresecretBz2XRNLzYH29WBfPqUGy37h  
public version               : xpub661MyMwAqRbcGithesenumbersaresecrettooG49rZJTvXhLD  
tree depth                   : 0  
fingerprint                  : a9963a0c  
Bitcoin address              : 1GThAMhCSk87AebUKuERFoDxNez9TswMaP  
Bitcoin address uncompressed : 156ogLZ6KqSN4rm9VrXKUHNTUyDWNNdxRr  

If the result of the command looks anything like the above then we have successfully installed pycoin and also generated some details we can use in the next step.

The details we will need later include the wallet key and the public version. For a production system, you would want to be a bit more careful when creating these values, such as performing this step on an offline machine. I'm not going to go too deeply on security in this post though so let's just all understand that what you just did was horribly insecure and this is just for fun.

Now, on to the fun. Let's create a function within Postgres that uses the pycoin library to generate a new address based on the id of our user.

CREATE OR REPLACE FUNCTION generate_address(id integer)  
RETURNS character varying  
AS $$  
    from pycoin.key.BIP32Node import BIP32Node

    # Repalce this with your Public Version of the Wallet Key
    s = 'xpub661MyMwAqRbcF3Cz7...WewxtqXMxCfHjsFBtp3mnmHFXR1kwUUp9'

    # Load up the Public Version as a BIP32 keychain
    key = BIP32Node.from_text(s)

    # We are going to be simple with the path and just use a single int for now
    path = str(id)

    # Combine the path with the keychain to generate the key at that path
    for k in key.subkeys(path):
        address = k.address(0)

    return address
$$ LANGUAGE plpython2u;

And to test:

abstract=# SELECT generate_address(1);  

If all goes to plan, an address should appear as a result.

Another benefit of ku is that we can run tests with it to validate our Postgres function.

Run the command below from bash, replacing my xpub with yours. The result should be the exact same address created above. The -s 1 flag is the same as the 1 parameter used when calling the SQL function. If you used a different value, you need to change this command to reflect the same.

$ ku -a xpub661MyMwAqRbcF3Cz7...WewxtqXMxp3mnmHFXR1kwUUp9 -s 1 

We can further test this even more by using the Wallet Key. A wallet key is able to generate the address like above, but also the private key needed to spend the funds. This is how we can normally keep these separate, only using the public half on live servers.

$ ku -a prv9s21ZcebQLYj...cPELMLFBhEY3Xzw1PS -s 1 
$ ku -a xprv9s21ZcebQLYj...cPELMLFBhEY3Xzw1PS -s 1 -W

All checks out for me. Hopefully your examples are no different! We can and will get a lot more complicated but I am going to stop here for now. Next time I will go into more detail on setting up support for multiple currencies and getting deeper into BIP32 paths.