This should work on NON-RDS postgres databases also
Add pycrypto extension
CREATE EXTENSION pgcrypto;
To check if pycrypto is added:
SHOW rds.extensions;
pgsql offers two method of column encryption, Below is example where we need encryption and decrytion as well.
perform the steps to create a public and private before.
create a table:
CREATE TABLE secured_table(property SERIAL PRIMARY KEY, property varchar(100), value bytea);
test for rows :
qa=> select * from secured_table;
id | property | value
----+----------+-------
(0 rows)
insert encrypted data:
INSERT INTO secured_table(property, value)
SELECT robotccs.property, pgp_pub_encrypt(robotccs.value, keys.pubkey) As value
FROM (VALUES ('awskey', 'xfadgghlrgsadff'),
('secretkey', 'aCFDGsfsabasfafdh') ) As robotccs(property, value)
CROSS JOIN (SELECT dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
-----END PGP PUBLIC KEY BLOCK-----') As pubkey) As keys;
check rows after insert , it shows only encrypted values:
qa=> select * from secured_table;
id | property | value
----+-----------+----------------------------------------------
1 | awskey | \xc1c04c038068b7aeb7cbdfd5010800b10b11ef0fa2
2 | secretkey | \xc1c04c038068b7aeb7cbdfd50107ff5cc130e2e216
(2 rows)
to find decrypted original values:
SELECT property, pgp_pub_decrypt(value, keys.privkey) As ccdecrypt
FROM secured_table
CROSS JOIN
(SELECT dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
-----END PGP PRIVATE KEY BLOCK-----') As privkey) As keys;
property | ccdecrypt
-----------+-------------------
awskey | xfadgghlrgsadff
secretkey | aCFDGsfsabasfafdh
(2 rows)
STEPS TO GENERATE THE KEY:
[user@server ~]$ gpg --gen-key
gpg (GnuPG) 2.0.14; Copyright (C) 2009 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Please select what kind of key you want:
(1) RSA and RSA (default)
(2) DSA and Elgamal
(3) DSA (sign only)
(4) RSA (sign only)
Your selection?
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048)
Requested keysize is 2048 bits
Please specify how long the key should be valid.
0 = key does not expire
<n> = key expires in n days
<n>w = key expires in n weeks
<n>m = key expires in n months
<n>y = key expires in n years
Key is valid for? (0)
Key does not expire at all
Is this correct? (y/N) y
GnuPG needs to construct a user ID to identify your key.
Real name: admin1
Email address: admin1@company.com
Comment:
You selected this USER-ID:
"admin1 <admin1@company.com>"
Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.
can't connect to `/home/user/.gnupg/S.gpg-agent': No such file or directory
You don't want a passphrase - this is probably a *bad* idea!
I will do it anyway. You can change your passphrase at any time,
using this program with the option "--edit-key".
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
asgdhgjasdhad
asghadfhj
gpg: /home/user/.gnupg/trustdb.gpg: trustdb created
gpg: key 966B48BB marked as ultimately trusted
public and secret key created and signed.
gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0 valid: 1 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 1u
pub 2048R/966B48BB 2016-09-27
Key fingerprint = 7243 4195 E241 8B7E A994 6817 8F12 326A 966B 48BB
uid admin1 <admin1@company.com>
sub 2048R/B7CBDFD5 2016-09-27
[[user@server ~]$ gpg --list-secret-keys
/home/user/.gnupg/secring.gpg
---------------------------------
sec 2048R/966B48BB 2016-09-27
uid admin1 <admin1@company.com>
ssb 2048R/B7CBDFD5 2016-09-27
[user@server ~]$ gpg -a --export B7CBDFD5
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
mQENBFfqtg0BCADixmjg0xac4ylRMBdQhlu+K1iDNeYJWYh1IqpiTwZEfO2mSpm+
uQVk2jHNs9n3oDTng9ggtkq5gQDvDmsIk4X2LqXEgzKaMIQRZAVTL5CbRtUManbn
ANPOrlPtLQJ301T5faZ7EnosSvEtVyNqapnwkkBG7KSMnUfSGLo2RrdlPMPPvWjg
c35wN3Yqr/U02cxkYyDcmPIpzJD3HdwlDCb2d5PAo89gcu9oTT5k54cBBMx17Izc
ge1rdMyvAKdaoJyHrgjItu9SE81i3ydDZJ8FkStt2coxGJXmN9V6rZCM9YxEjnwr
u0yEne7bNep8XnN8OU1QD62IsJfgX0MFykaLC2ndp0kaEbPfztjTp9keGFiTPsLY
MvNf28LwEoXc0uVhVGnoHOkCbVEVHg75jQxKAqnqTMh78TcqxyxrVf2gKPNhN/T4
YhA7t5x5u0YDy0K2FaRdKiZQv1+mcrOcpvY=
=6cNj
-----END PGP PUBLIC KEY BLOCK-----
[user@server ~]$ gpg -a --export-secret-keys 966B48BB
-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
lQOYBFfqtg0BCADixmjg0xac4ylRMBdQhlu+K1iDNeYJWYh1IqpiTwZEfO2mSpm+
uQVk2jHNs9n3oDTng9ggtkq5gQDvDmsIk4X2LqXEgzKaMIQRZAVTL5CbRtUManbn
ANPOrlPtLQJ301T5faZ7EnosSvEtVyNqapnwkkBG7KSMnUfSGLo2RrdlPMPPvWjg
c35wN3Yqr/U02cxkYyDcmPIpzJD3HdwlDCb2d5PAo89gcu9oTT5k54cBBMx17Izc
ge1rdMyvAKdaoJyHrgjItu9SE81i3ydDZJ8FkStt2coxGJXmN9V6rZCM9YxEjnwr
WUrCdJ6Vx78b9YfoxkTjYK9xMhG+xj7bZanbABEBAAEAB/0cXIdwrsBiPgAZ2POM
LPGRDBIVft/WhYMNG0PHPcOL5zqVlI474x9Ak2VX057MBcD4FMN/+9cb8igFaH6M
Y7Qvq3qB5Df86VprDmD4HA0azmCwx9PiZMvDSobRNSgyMj+rYEKPm9T5xc758QRL
N9UpgtuqbcglPzpklUEcrqVwcfe3D6fqfrpUPdGqzfdH2LEOF5cydFnRzbuswsAu
ySXnWbuaDTgMxO2w5f/7VodPzK5wu2tsPwvVos1GKqiKPHlfLWhOn3qNpk6ODRIM
/ioZ+hXVu0yEne7bNep8XnN8OU1QD62IsJfgX0MFykaLC2ndp0kaEbPfztjTp9ke
GFiTPsLYMvNf28LwEoXc0uVhVGnoHOkCbVEVHg75jQxKAqnqTMh78TcqxyxrVf2g
KPNhN/T4YhA7t5x5u0YDy0K2FaRdKiZQv1+mcrOcpvY=
=MRtJ
-----END PGP PRIVATE KEY BLOCK-----
[user@server ~]$
Add pycrypto extension
CREATE EXTENSION pgcrypto;
To check if pycrypto is added:
SHOW rds.extensions;
pgsql offers two method of column encryption, Below is example where we need encryption and decrytion as well.
perform the steps to create a public and private before.
create a table:
CREATE TABLE secured_table(property SERIAL PRIMARY KEY, property varchar(100), value bytea);
test for rows :
qa=> select * from secured_table;
id | property | value
----+----------+-------
(0 rows)
insert encrypted data:
INSERT INTO secured_table(property, value)
SELECT robotccs.property, pgp_pub_encrypt(robotccs.value, keys.pubkey) As value
FROM (VALUES ('awskey', 'xfadgghlrgsadff'),
('secretkey', 'aCFDGsfsabasfafdh') ) As robotccs(property, value)
CROSS JOIN (SELECT dearmor('-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
-----END PGP PUBLIC KEY BLOCK-----') As pubkey) As keys;
check rows after insert , it shows only encrypted values:
qa=> select * from secured_table;
id | property | value
----+-----------+----------------------------------------------
1 | awskey | \xc1c04c038068b7aeb7cbdfd5010800b10b11ef0fa2
2 | secretkey | \xc1c04c038068b7aeb7cbdfd50107ff5cc130e2e216
(2 rows)
to find decrypted original values:
SELECT property, pgp_pub_decrypt(value, keys.privkey) As ccdecrypt
FROM secured_table
CROSS JOIN
(SELECT dearmor('-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
-----END PGP PRIVATE KEY BLOCK-----') As privkey) As keys;
property | ccdecrypt
-----------+-------------------
awskey | xfadgghlrgsadff
secretkey | aCFDGsfsabasfafdh
(2 rows)
STEPS TO GENERATE THE KEY:
[user@server ~]$ gpg --gen-key
gpg (GnuPG) 2.0.14; Copyright (C) 2009 Free Software Foundation, Inc.
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.
Please select what kind of key you want:
(1) RSA and RSA (default)
(2) DSA and Elgamal
(3) DSA (sign only)
(4) RSA (sign only)
Your selection?
RSA keys may be between 1024 and 4096 bits long.
What keysize do you want? (2048)
Requested keysize is 2048 bits
Please specify how long the key should be valid.
0 = key does not expire
<n> = key expires in n days
<n>w = key expires in n weeks
<n>m = key expires in n months
<n>y = key expires in n years
Key is valid for? (0)
Key does not expire at all
Is this correct? (y/N) y
GnuPG needs to construct a user ID to identify your key.
Real name: admin1
Email address: admin1@company.com
Comment:
You selected this USER-ID:
"admin1 <admin1@company.com>"
Change (N)ame, (C)omment, (E)mail or (O)kay/(Q)uit? O
You need a Passphrase to protect your secret key.
can't connect to `/home/user/.gnupg/S.gpg-agent': No such file or directory
You don't want a passphrase - this is probably a *bad* idea!
I will do it anyway. You can change your passphrase at any time,
using this program with the option "--edit-key".
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
We need to generate a lot of random bytes. It is a good idea to perform
some other action (type on the keyboard, move the mouse, utilize the
disks) during the prime generation; this gives the random number
generator a better chance to gain enough entropy.
asgdhgjasdhad
asghadfhj
gpg: /home/user/.gnupg/trustdb.gpg: trustdb created
gpg: key 966B48BB marked as ultimately trusted
public and secret key created and signed.
gpg: checking the trustdb
gpg: 3 marginal(s) needed, 1 complete(s) needed, PGP trust model
gpg: depth: 0 valid: 1 signed: 0 trust: 0-, 0q, 0n, 0m, 0f, 1u
pub 2048R/966B48BB 2016-09-27
Key fingerprint = 7243 4195 E241 8B7E A994 6817 8F12 326A 966B 48BB
uid admin1 <admin1@company.com>
sub 2048R/B7CBDFD5 2016-09-27
[[user@server ~]$ gpg --list-secret-keys
/home/user/.gnupg/secring.gpg
---------------------------------
sec 2048R/966B48BB 2016-09-27
uid admin1 <admin1@company.com>
ssb 2048R/B7CBDFD5 2016-09-27
[user@server ~]$ gpg -a --export B7CBDFD5
-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
mQENBFfqtg0BCADixmjg0xac4ylRMBdQhlu+K1iDNeYJWYh1IqpiTwZEfO2mSpm+
uQVk2jHNs9n3oDTng9ggtkq5gQDvDmsIk4X2LqXEgzKaMIQRZAVTL5CbRtUManbn
ANPOrlPtLQJ301T5faZ7EnosSvEtVyNqapnwkkBG7KSMnUfSGLo2RrdlPMPPvWjg
c35wN3Yqr/U02cxkYyDcmPIpzJD3HdwlDCb2d5PAo89gcu9oTT5k54cBBMx17Izc
ge1rdMyvAKdaoJyHrgjItu9SE81i3ydDZJ8FkStt2coxGJXmN9V6rZCM9YxEjnwr
u0yEne7bNep8XnN8OU1QD62IsJfgX0MFykaLC2ndp0kaEbPfztjTp9keGFiTPsLY
MvNf28LwEoXc0uVhVGnoHOkCbVEVHg75jQxKAqnqTMh78TcqxyxrVf2gKPNhN/T4
YhA7t5x5u0YDy0K2FaRdKiZQv1+mcrOcpvY=
=6cNj
-----END PGP PUBLIC KEY BLOCK-----
[user@server ~]$ gpg -a --export-secret-keys 966B48BB
-----BEGIN PGP PRIVATE KEY BLOCK-----
Version: GnuPG v2.0.14 (GNU/Linux)
lQOYBFfqtg0BCADixmjg0xac4ylRMBdQhlu+K1iDNeYJWYh1IqpiTwZEfO2mSpm+
uQVk2jHNs9n3oDTng9ggtkq5gQDvDmsIk4X2LqXEgzKaMIQRZAVTL5CbRtUManbn
ANPOrlPtLQJ301T5faZ7EnosSvEtVyNqapnwkkBG7KSMnUfSGLo2RrdlPMPPvWjg
c35wN3Yqr/U02cxkYyDcmPIpzJD3HdwlDCb2d5PAo89gcu9oTT5k54cBBMx17Izc
ge1rdMyvAKdaoJyHrgjItu9SE81i3ydDZJ8FkStt2coxGJXmN9V6rZCM9YxEjnwr
WUrCdJ6Vx78b9YfoxkTjYK9xMhG+xj7bZanbABEBAAEAB/0cXIdwrsBiPgAZ2POM
LPGRDBIVft/WhYMNG0PHPcOL5zqVlI474x9Ak2VX057MBcD4FMN/+9cb8igFaH6M
Y7Qvq3qB5Df86VprDmD4HA0azmCwx9PiZMvDSobRNSgyMj+rYEKPm9T5xc758QRL
N9UpgtuqbcglPzpklUEcrqVwcfe3D6fqfrpUPdGqzfdH2LEOF5cydFnRzbuswsAu
ySXnWbuaDTgMxO2w5f/7VodPzK5wu2tsPwvVos1GKqiKPHlfLWhOn3qNpk6ODRIM
/ioZ+hXVu0yEne7bNep8XnN8OU1QD62IsJfgX0MFykaLC2ndp0kaEbPfztjTp9ke
GFiTPsLYMvNf28LwEoXc0uVhVGnoHOkCbVEVHg75jQxKAqnqTMh78TcqxyxrVf2g
KPNhN/T4YhA7t5x5u0YDy0K2FaRdKiZQv1+mcrOcpvY=
=MRtJ
-----END PGP PRIVATE KEY BLOCK-----
[user@server ~]$
