I am considering updating a PGSQL 6.x extension...

[Bear Giles]

Hi,

I'm considering updating an unpublished PostgreSQL 6.x extension and have a quick question. (well, several, but I want to RTFM first.)  It's a crypto extension - different from pg_crypto - but that raises the question of export restrictions. Do you know if extensions fall under the same umbrella as PostgreSQL or if they'll need to be managed separately? The restrictions are identical to those on libssl since that's the only crypto it uses.

BTW the original extension added native datatypes for digital certificates, private keys, and keystores, plus a metric ton of associated functions. The motivation is that most people treat digital certs and keys as blobs, cache some values pulled from them, and have blind trust that nobody has corrupted the data. With this extension it's easy to add constraints that make that impossible:

create table certs (
   cert x509v3 not null,
   subject varchar[200] constraint subject = x509subject(cert),
   authority varchar[200] constraint authority = x509authority(cert),
   ...
)

or something like that. Maybe I enforced this with triggers. In any case it was impossible for someone to edit the entry so the cached values no longer matched the certificate. I know I also used triggers to ensure that every certificate in a table was either self-signed or signed by another certificate in the table - nobody could sneak in rogue certificates.

I know pg_crypto covers some of the same ground but I'm revisiting my extension because a common practice emerging is to use H2 for development testing and PostgreSQL, Oracle, etc. when deployed. You can easily add user-defined functions to H2 by including them on the classpath. But this only works if you have the same types, functions and behaviors on the 'real' database as well.

Thanks,

Bear

[David E. Wheeler]

On Oct 15, 2014, at 8:54 AM, Bear Giles <bgi...@coyotesong.com> wrote:

> I'm considering updating an unpublished PostgreSQL 6.x extension and have a quick question. (well, several, but I want to RTFM first.) It's a crypto extension - different from pg_crypto - but that raises the question of export restrictions. Do you know if extensions fall under the same umbrella as PostgreSQL or if they'll need to be managed separately? The restrictions are identical to those on libssl since that's the only crypto it uses.

I have no idea. I think you would be best off consulting a lawyer. PGXN makes no assumptions and enforces no constraints on exports. If it makes any difference, the root PGXN server is currently located in Germany.

> BTW the original extension added native datatypes for digital certificates, private keys, and keystores, plus a metric ton of associated functions. The motivation is that most people treat digital certs and keys as blobs, cache some values pulled from them, and have blind trust that nobody has corrupted the data. With this extension it's easy to add constraints that make that impossible:
>
> create table certs (
> cert x509v3 not null,
> subject varchar[200] constraint subject = x509subject(cert),
> authority varchar[200] constraint authority = x509authority(cert),
> ...
> )
>
> or something like that. Maybe I enforced this with triggers. In any case it was impossible for someone to edit the entry so the cached values no longer matched the certificate. I know I also used triggers to ensure that every certificate in a table was either self-signed or signed by another certificate in the table - nobody could sneak in rogue certificates.

That looks really cool!

> I know pg_crypto covers some of the same ground but I'm revisiting my extension because a common practice emerging is to use H2 for development testing and PostgreSQL, Oracle, etc. when deployed. You can easily add user-defined functions to H2 by including them on the classpath. But this only works if you have the same types, functions and behaviors on the 'real' database as well.

Crypto data types sounds really useful, thank you!

Best,

David

[Bear Giles]

Okay, thanks. Off to RTFM so see how much has changed in... eek, 10 years.

Bear

[Bear Giles]

I've finally found some time to work on this again. (Fun fun fun with a cybersecurity specialization and cloud computing.) I've forgotten my password and can't find the page that sends me a reminder.

The account name is 'thebearinboulder'.

I'll be uploading a 'complex number' extension this weekend. It's mostly educational and will be documented in a concurrent blog post. I know anyone can look at the source for existing extensions but where would they start? It's also hard to find documentation on how to read and write composite types in the C-language functions.

On the crypto-side I have some things but quickly chased my tail into a rabbit hole and need to break it into smaller pieces. That's one reason for pg-complex - it allows me to learn the basics before jumping into the complexities of the openssl library. The current plan is to work on a few extensions as time permits. I should probably document this somewhere. :-)

pg-bignum

This wraps the OpenSSL BIGNUM type and won't do much more than allow casts between it and a bigint.

pg-dn

This wraps the OpenSSL X509_NAME type - it is the 'distinguished name' from LDAP and digital certificates. I'm still researching the best way to handle this but the high-level view is that LDAP and PKI are both defined under X500 and both have the concept of a 'distinguished name'. It is an ordered pair of values. It's conventionally written something like 'DN=something I forgot,CN=Bear Giles,C=US,S=Colorado,L=Boulder'. There are a few dozen keys, some can be repeated. (E.g., 'DC' is 'domain component', e.g., 'DC=google,DC=com') and you can also have non-standard but common extensions. There are a few different ways to present the information and of course different services use different formats, e.g., ordering from high-to-low or vice versa.

I have not decided whether to store this in its native format or as an extension of a hstore or json type. It should support casting between them.

I also need to verify that there isn't already a standard implemention. If so I'll just interoperate with it.

pg-x509 (?)

This is the actual digital certs. Some key components are bignums (serial numbers) and dn (subject and issuer). There are additional components that should arguably be their own types but that can be handled in future versions. That's why I want to do the other extensions first - I knew I should create additional types and they could be useful outside of the crypto realm.

There is a related concept of certificate chains. This is superficially an array of x509 but there are nuances.

pg-keystore

A deeper rabbit hole. The issue is that you should never store a naked key. This is my biggest annoyance with existing implementations. I don't even want to pass a naked key - pass an encrypted container and let the caller decrypt it.

There's a solution to this - keystores. On windows they're usually .p12 extensions. You can store both digital certificates and the associated keys, or just the digital certificate. (The latter are sometimes referred to as certstores.) You can have a password on both the keystore as a whole and individual keys.

The complication is that you can have more than one certificate and key. That means the UDF needs to handle keyids instead of simply assuming that there's one cert and one optional key and writing simple functions.

(There's also .p8 format that takes a single key. It also needs to play well with the .p12 types)

pg-crypto

A very deep rabbit hole. I know there's pgcrypto but 1) I'll have a better way to store keys and 2) I really hate passing around naked keys. REALLY hate it. I can write functions that take keystores and passwords instead of naked keys but if you're passing that information around you can still get the key.

The obvious solution is to have a table containing keystores, pass around the keyid and password, and then use SPI to retrieve the keystore. This can be secured by having the keystores owned by a dedicated user and only allowing access through vetted stored procedures. The app can call stored procedures to access the encrypted data instead of accessing it directly or passing in a naked key.

.. but that's fuzzy thinking. I probably need to write some sample code to figure out what works best but I can't do that until I have the keystore. The keystore design needs to make the crypto design cleanest. Rabbit hole.

The deepest rabbit hole is that there's good reasons to not store this keystore in the database at all. Store it in the server's filesystem. I know that means it's an untrusted exception but I still need to determine where to put the file, etc.

pg-ca

This is fairly straightforward once you have the keystore. You take a digital certificate request, sign it, and return a digital certificate. It requires a few tables but it can be a lot like postgis in terms of defining standard tables. This code is pretty brain-dead, all of the hard work is in policies controlling who can call what.

Bear

[David E. Wheeler]

On Jul 17, 2015, at 9:57 AM, Bear Giles <bgi...@coyotesong.com> wrote:

> I've finally found some time to work on this again. (Fun fun fun with a cybersecurity specialization and cloud computing.) I've forgotten my password and can't find the page that sends me a reminder.
>
> The account name is 'thebearinboulder'.

Looks like your account is active. You can reset your password here:

http://manager.pgxn.org/account/forgotten

> I'll be uploading a 'complex number' extension this weekend. It's mostly educational and will be documented in a concurrent blog post. I know anyone can look at the source for existing extensions but where would they start? It's also hard to find documentation on how to read and write composite types in the C-language functions.

I tend to look at the in-core contrib extensions to learn how stuff works. You can browse the source here:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;f=config;hb=HEAD

> pg-bignum
>
> This wraps the OpenSSL BIGNUM type and won't do much more than allow casts between it and a bigint.

Is the OpenSSL BIGNUM different from BIGINT? BTW, if you release it on PGXN, I recommend omitting the “pg-” part, since its redudnant. Maybe call it openssl-bignum or something.

> pg-dn
>
> This wraps the OpenSSL X509_NAME type - it is the 'distinguished name' from LDAP and digital certificates. I'm still researching the best way to handle this but the high-level view is that LDAP and PKI are both defined under X500 and both have the concept of a 'distinguished name'. It is an ordered pair of values. It's conventionally written something like 'DN=something I forgot,CN=Bear Giles,C=US,S=Colorado,L=Boulder'. There are a few dozen keys, some can be repeated. (E.g., 'DC' is 'domain component', e.g., 'DC=google,DC=com') and you can also have non-standard but common extensions. There are a few different ways to present the information and of course different services use different formats, e.g., ordering from high-to-low or vice versa.

Sounds useful. Maybe call it x509-name or ldap-dn.

> I have not decided whether to store this in its native format or as an extension of a hstore or json type. It should support casting between them.

JSON and JSONB are core, so it’d definitely be useful to support casting for them. It’d be cool to expose functions that convert between distinguished names and JSON. Might not even need a type, then.

>
> I also need to verify that there isn't already a standard implemention. If so I'll just interoperate with it.
>
> pg-x509 (?)
>
> This is the actual digital certs. Some key components are bignums (serial numbers) and dn (subject and issuer). There are additional components that should arguably be their own types but that can be handled in future versions. That's why I want to do the other extensions first - I knew I should create additional types and they could be useful outside of the crypto realm.

Might find some interesting pointers in this SO question:

http://dba.stackexchange.com/q/36416/32927

> .. but that's fuzzy thinking. I probably need to write some sample code to figure out what works best but I can't do that until I have the keystore. The keystore design needs to make the crypto design cleanest. Rabbit hole.

Deep one, I suspect. Would be cool, though. Maybe you could also leverage column-level privileges?

http://www.postgresql.org/docs/current/static/sql-grant.html

HTH,

David

[Bear Giles]

I can't get to the 'forgotten password' page. It requires me to enter my password.

Bear

--
You received this message because you are subscribed to the Google Groups "PGXN Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgxn-users+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tomas Vondra]

The link http://manager.pgxn.org/account/forgotten works fine for me
(i.e. no password requested).

regards
Tomas

[Bear Giles]

Basic authentication is evil. It looks like the problem is that I tried to log in, had the wrong password, and now there's no way to clear it from my browser. The browser checks that information (which fails) which fails before allowing me to the page. I know how to handle this with java webapps but not basic sites.

I was able to get via a different browser.