So, this is an issue that was caused on RHEL 7.4 with EnterpriseDB 9.6.6.11 and pgPool 3.6. I followed this link to configure wrapper scripts for /sbin/ip and /sbin/arping.
This was added to the /etc/sudoers file to allow the enterprisedb user to execute the wrapper scripts:
enterprisedb compute01=(root) NOPASSWD:EXEC:SETENV: /sbin/ip, /sbin/arping
Just as a check, I was able to sudo to the enterprisedb user and execute the following command and successfully bring up the delegate IP (or VIP):
ip_w addr add 10.10.10.85/24 dev ens192 label ens192:0
Now, as root I started the pgPool service:
systemctl start edb-pgpool-3.6
Checking /var/log/messages, the following was shown:
15:49:12: pid 860: LOG: creating socket for sending heartbeat
15:49:12: pid 860: DETAIL: setsockopt(SO_BINDTODEVICE) requires root privilege
15:49:12: pid 860: LOG: set SO_REUSEPORT option to the socket
15:49:12: pid 860: LOG: creating socket for sending heartbeat
15:49:12: pid 860: DETAIL: set SO_REUSEPORT
15:49:12: pid 858: LOG: failed to create watchdog heartbeat receive socket.
15:49:12: pid 858: DETAIL: setsockopt(SO_BINDTODEVICE) requies root privilege
15:49:12: pid 858: LOG: set SO_REUSEPORT option to the socket
15:49:12: pid 858: LOG: creating watchdog heartbeat receive socket.
15:49:12: pid 858: DETAIL: set SO_REUSEPORT
15:49:14: pid 854: WARNING: watchdog failed to ping host"10.85.10.134"
15:49:14: pid 854: DETAIL: ping process exits with code: 1
15:49:14: pid 854: LOG: waiting for the delegate IP address to become active
15:49:14: pid 854: DETAIL: waiting... count: 1
15:49:17: pid 854: WARNING: watchdog failed to ping host"10.85.10.134"
15:49:17: pid 854: DETAIL: ping process exits with code: 1
15:49:17: pid 854: LOG: waiting for the delegate IP address to become active
15:49:17: pid 854: DETAIL: waiting... count: 2
15:49:20: pid 854: WARNING: watchdog failed to ping host"10.85.10.134"
15:49:20: pid 854: DETAIL: ping process exits with code: 1
15:49:20: pid 854: LOG: waiting for the delegate IP address to become active
15:49:20: pid 854: DETAIL: waiting... count: 3
15:49:20: pid 854: LOG: failed to acquire the delegate IP address
15:49:20: pid 854: DETAIL: 'if_up_cmd' failed
15:49:20: pid 854: WARNING: watchdog escalation failed to acquire delegate IP
15:49:20: pid 850: LOG: watchdog escalation process with pid: 854 exit with SUCCESS.
Notice the "DETAIL: 'if_up_cmd' failed" line. The delegate IP was not brought up.
The following was noted in the /var/log/edb/pgpool3.6/edb-pgpool-3.6.log file:
sudo: sorry, you must have a tty to run sudo
So, what's going on here? Checking the /etc/sudoers file, note this line:
Defaults listpw=all, requiretty, syslog=authpriv, !root_sudo, !umask, env_reset, secure_path = /usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
It looks like a terminal is required by the enterprisedb user to execute the commands to bring up the delegate IP by the fact that "requiretty" is set for all users.
Checking online for the above message, the following link discusses the fix.
Adding the following to /etc/sudoers (use visudo to make changes to this file):
Defaults:enterprisedb !requiretty
So now, enterprisedb can run the relevant commands without a tty.
Since I'm using Puppet to manage the environment, I added the following class to my module that manages the Enterprise DB environment:
class edbas::set_privs {
sudo::default_entry { 'enterprisedb_requiretty':
content => [ '!requiretty', ],
target => ':enterprisedb',
}
sudo::user_specification { 'enterprisedb':
user_list => ['enterprisedb'],
runas => 'root',
cmnd => ['/sbin/ip', '/sbin/arping'],
passwd => false,
}
}
I'm using pupmod-simp-sudo modules in this environment, so this is the actual module that manages the /etc/sudoers file and I call this from my edbas module.
Tuesday, March 20, 2018
Wednesday, February 7, 2018
Migrating a PostgreSQL 9.1 Database with PostGIS to PostgreSQL 9.6.5
This post shows how to migrate a PostgreSQL 9.1 database that has geospatial data types to a PostgreSQL 9.6.5 database.
An in-place upgrade is possible, but this case required was to move from one physical server to another server. The target server already had PostgreSQL 9.6.5 installed.
Note, the following was done on RHEL 7.4 using PostgreSQL 9.6.5.
On source server, dump the source database as follows:
pg_dump -U postgres -Fc -b -v -f "spatial_db.dump" spatial_db
where:
-F specifies output file format
-c specifies custom-format archive file
-b specifies include large objects
-v specifies verbose mode
-f specifies output file
Copy the dump file over to the target server.
On the 9.6.5 instance, create the new database and appropriate extensions:
psql
create database spatial_db;
\c spatial_db
create extension postgis;
create extension btree_gist;
\q
I re-started the 9.6.5 instance at this point. If you get any errors when creating the btree_gist extension, try again and restart the instance. Check what extensions exist in your database as follows:
psql -U postgres -d spatial_db
spatial_db=# \dx
List of installed extensions
<headers removed from dx command>
btree_gist
plpgsql
postgis
You may require other extensions based on your site.
Run the restore as follows:
perl /usr/pgsql-9.6/share/contrib/postgis-2.3/postgis_restore.pl spatial_db.dump | psql -U postgres spatial_db 2>errors.log
You'll need to find the postgis_restore.pl file on your system as it may be located in a different location.
Check the errors.log for any errors.
One thing to note when going from 9.1 to 9.6.5 is that the public.geometry_columns table in 9.1 gets converted to a view in 9.6.5. There might be other objects as well, but this is one that I noticed when I did table counts from 9.1 to 9.6.5.
An in-place upgrade is possible, but this case required was to move from one physical server to another server. The target server already had PostgreSQL 9.6.5 installed.
Note, the following was done on RHEL 7.4 using PostgreSQL 9.6.5.
On source server, dump the source database as follows:
pg_dump -U postgres -Fc -b -v -f "spatial_db.dump" spatial_db
where:
-F specifies output file format
-c specifies custom-format archive file
-b specifies include large objects
-v specifies verbose mode
-f specifies output file
Copy the dump file over to the target server.
On the 9.6.5 instance, create the new database and appropriate extensions:
psql
create database spatial_db;
\c spatial_db
create extension postgis;
create extension btree_gist;
\q
I re-started the 9.6.5 instance at this point. If you get any errors when creating the btree_gist extension, try again and restart the instance. Check what extensions exist in your database as follows:
psql -U postgres -d spatial_db
spatial_db=# \dx
List of installed extensions
<headers removed from dx command>
btree_gist
plpgsql
postgis
You may require other extensions based on your site.
Run the restore as follows:
perl /usr/pgsql-9.6/share/contrib/postgis-2.3/postgis_restore.pl spatial_db.dump | psql -U postgres spatial_db 2>errors.log
You'll need to find the postgis_restore.pl file on your system as it may be located in a different location.
Check the errors.log for any errors.
One thing to note when going from 9.1 to 9.6.5 is that the public.geometry_columns table in 9.1 gets converted to a view in 9.6.5. There might be other objects as well, but this is one that I noticed when I did table counts from 9.1 to 9.6.5.
Thursday, January 11, 2018
PostgreSQL SSL Configuration - Part II
This post describes how to connect to PostgreSQL using Java and client/auth. This assumes that client and server certificates have been installed as described in the previous post.
The PgClientCertDemo.zip can be downloaded here.
Our environment is RHEL 7.4, PostgreSQL 9.6.5 and openjdk version "1.8.0_151".
Java Truststore and Keystore
First, create a Java truststore. Assume that our postgres home is set to /data/pgsql. We need the root and intermediate certificates which can be downloaded from your CA or from the ca-chain.cert.pem file. From this file, copy the intermediate and root certs into separate files e.g. CA-root.crt and CA-intermediate.crt.
Once these are placed in /data/pgsql/.postgresql, import these into the keystore as follows:
$ keytool -import -file CA-root.crt -alias CA-root -keystore cacerts
Enter keystore password:
Re-enter new password:
Owner: CN=...
...
...
...
#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 6C 8A 94 A2 77 B1 80 72 1D 81 7A 16 AA F2 DC CE l...w..r..z.....
0010: 66 EE 45 C0 f.E.
]
]
Trust this certificate? [no]: yes
Certificate was added to keystore
$ keytool -import -file CA-intermediate.crt -alias CA-intermediate -keystore cacerts
Enter keystore password:
Certificate was added to keystore
Next, concatenate the postgresql.crt and postgresql.key file as follows:
$ cat postgresql.key postgresql.crt > postgresql.pem
The postgresql.pem will include the private key, the postgres certificate and the intermediate and root certificates.
Take this PEM file and import into the keystore:
$ openssl pkcs12 -export -in postgresql.pem -out keystore.jks -name postgres -noiter -nomaciter
Enter Export Password:
Verifying - Enter Export Password:
So, we now have a trusted certificate store (cacerts) and key store (keystore.jks).
You can check the contents of the trusted store and key store as follows:
$ keytool -list -v -keystore cacerts
$ keytool -list -v -keystore keystore.jks
You will be prompted for a password.
Or, check the contents specifying an alias:
$ keytool -list -v -keystore keystore.jks -alias postgres
$ keytool -list -v -keystore cacerts -alias CA-root
$ keytool -list -v -keystore cacerts -alias CA-intermediate
Source Code
Unzip the contents of PgClientCertDemo.zip to a temporary location. Once can either use Maven to compile or manually as described in the steps below.
Copy the following files to the root of your temporary location:
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/App.java
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/TestConnection.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLError.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLSocketFactory.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomX509KeyManager.java
So now you should have the following files in your temporary location:
App.java
TestConnection.java
CustomSSLError.java
CustomSSLSocketFactory.java
CustomX509KeyManager.java
Open each of these and comment out the following line:
package au.com.postnewspapers.pgclientcertdemo;
Next, download the following JARs into the same location as where the source code is:
commons-lang3-3.7.jar or commons-lang3-3.7.jar
postgresql-42.1.4.jar
Open the following files:
App.java
CustomSSLSocketFactory.java
and change:
import org.apache.commons.lang.exception.ExceptionUtils;
to
import org.apache.commons.lang3.exception.ExceptionUtils;
If you are using the old version of org.apache.commons.lang package, then the above change is not required.
Compilation Step
Compile each class as follows:
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomX509KeyManager.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLSocketFactory.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLError.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar TestConnection.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar App.java
The PgClientCertDemo.zip can be downloaded here.
Our environment is RHEL 7.4, PostgreSQL 9.6.5 and openjdk version "1.8.0_151".
Java Truststore and Keystore
First, create a Java truststore. Assume that our postgres home is set to /data/pgsql. We need the root and intermediate certificates which can be downloaded from your CA or from the ca-chain.cert.pem file. From this file, copy the intermediate and root certs into separate files e.g. CA-root.crt and CA-intermediate.crt.
Once these are placed in /data/pgsql/.postgresql, import these into the keystore as follows:
$ keytool -import -file CA-root.crt -alias CA-root -keystore cacerts
Enter keystore password:
Re-enter new password:
Owner: CN=...
...
...
...
#3: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: 6C 8A 94 A2 77 B1 80 72 1D 81 7A 16 AA F2 DC CE l...w..r..z.....
0010: 66 EE 45 C0 f.E.
]
]
Trust this certificate? [no]: yes
Certificate was added to keystore
$ keytool -import -file CA-intermediate.crt -alias CA-intermediate -keystore cacerts
Enter keystore password:
Certificate was added to keystore
Next, concatenate the postgresql.crt and postgresql.key file as follows:
$ cat postgresql.key postgresql.crt > postgresql.pem
The postgresql.pem will include the private key, the postgres certificate and the intermediate and root certificates.
Take this PEM file and import into the keystore:
$ openssl pkcs12 -export -in postgresql.pem -out keystore.jks -name postgres -noiter -nomaciter
Enter Export Password:
Verifying - Enter Export Password:
So, we now have a trusted certificate store (cacerts) and key store (keystore.jks).
You can check the contents of the trusted store and key store as follows:
$ keytool -list -v -keystore cacerts
$ keytool -list -v -keystore keystore.jks
You will be prompted for a password.
Or, check the contents specifying an alias:
$ keytool -list -v -keystore keystore.jks -alias postgres
$ keytool -list -v -keystore cacerts -alias CA-root
$ keytool -list -v -keystore cacerts -alias CA-intermediate
Source Code
Unzip the contents of PgClientCertDemo.zip to a temporary location. Once can either use Maven to compile or manually as described in the steps below.
Copy the following files to the root of your temporary location:
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/App.java
./au/com/postnewspapers/pgclientcertdemo/pgclientcertdemo/TestConnection.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLError.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomSSLSocketFactory.java
./au/com/postnewspapers/pgclientcertdemo/ssl/CustomX509KeyManager.java
So now you should have the following files in your temporary location:
App.java
TestConnection.java
CustomSSLError.java
CustomSSLSocketFactory.java
CustomX509KeyManager.java
Open each of these and comment out the following line:
package au.com.postnewspapers.pgclientcertdemo;
Next, download the following JARs into the same location as where the source code is:
commons-lang3-3.7.jar or commons-lang3-3.7.jar
postgresql-42.1.4.jar
Open the following files:
App.java
CustomSSLSocketFactory.java
and change:
import org.apache.commons.lang.exception.ExceptionUtils;
to
import org.apache.commons.lang3.exception.ExceptionUtils;
If you are using the old version of org.apache.commons.lang package, then the above change is not required.
Compilation Step
Compile each class as follows:
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomX509KeyManager.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLSocketFactory.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar CustomSSLError.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar TestConnection.java
$ javac -cp .:commons-lang3-3.7.jar:postgresql-42.1.4.jar App.java
Run Step
Run the test as follows:
$ java -cp .:postgresql-42.1.4.jar:commons-lang3-3.7.jar \
-Djavax.net.ssl.trustStore=/data/pgsql/.postgresql/cacerts App default jks \
/data/pgsql/.postgresql/keystore.jks Password1234 \
'jdbc:postgresql://10.10.10.123/postgres?user=postgres&ssl=true'
Using trusted root store: /data/pgsql/.postgresql/cacerts
trustStore is: /data/pgsql/.postgresql/cacerts
trustStore type is : jks
trustStore provider is :
init truststore
adding as trusted cert:
Subject: CN=...
trustStore is: /data/pgsql/.postgresql/cacerts
trustStore type is : jks
trustStore provider is :
init truststore
adding as trusted cert:
Subject: CN=...
...
...
...
main, WRITE: TLSv1.2 Application Data, length = 147
main, READ: TLSv1.2 Application Data, length = 355
main, WRITE: TLSv1.2 Application Data, length = 87
main, READ: TLSv1.2 Application Data, length = 49
main, WRITE: TLSv1.2 Application Data, length = 108
main, READ: TLSv1.2 Application Data, length = 94
main, WRITE: TLSv1.2 Application Data, length = 84
main, READ: TLSv1.2 Application Data, length = 202
main, WRITE: TLSv1.2 Application Data, length = 29
main, called close()
main, called closeInternal(true)
main, SEND TLSv1.2 ALERT: warning, description = close_notify
main, WRITE: TLSv1.2 Alert, length = 26
main, called closeSocket(true)
main, called close()
main, called closeInternal(true)
main, called close()
main, called closeInternal(true)
Connection and query successful, server version is PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
main, READ: TLSv1.2 Application Data, length = 355
main, WRITE: TLSv1.2 Application Data, length = 87
main, READ: TLSv1.2 Application Data, length = 49
main, WRITE: TLSv1.2 Application Data, length = 108
main, READ: TLSv1.2 Application Data, length = 94
main, WRITE: TLSv1.2 Application Data, length = 84
main, READ: TLSv1.2 Application Data, length = 202
main, WRITE: TLSv1.2 Application Data, length = 29
main, called close()
main, called closeInternal(true)
main, SEND TLSv1.2 ALERT: warning, description = close_notify
main, WRITE: TLSv1.2 Alert, length = 26
main, called closeSocket(true)
main, called close()
main, called closeInternal(true)
main, called close()
main, called closeInternal(true)
Connection and query successful, server version is PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
Conclusion
This completes the exercise to show how to connect to PostgreSQL using client/auth using Java. Note that CRL checking is not done in this exercise. The code would need to be modified to accomplish this. We only supply the keystore.jks password and not the postgres password.
Friday, January 5, 2018
PostgreSQL SSL Configuration - Part I
This post describes how to setup PostgreSQL so that we can connect without providing a user password. Instead, we will use certificates to connect. This is required in some secure environments.
Part II of this post will describe how to connect to PostgreSQL using Java and certificates.
Before we, start we'll build a root CA and an intermediate CA. This way, we can generate certificates when we please.
This site was very clear and concise in detailing how to setup the above structure. We'll use RHEL 7.4 and PostgreSQL 9.6.5.
Note that you may customize your openssl.cnf for your own needs. Again, this is a test scenario. You would most likely be getting your certificates from a certified CA.
Once your root and intermediate CAs' are set up, generate a certificate signing request (CSR) for your PostgreSQL server:
# cd /root/ca
# openssl genrsa -out intermediate/private/server.key 2048
# chmod 400 intermediate/private/server.key
Create the certificate:
# openssl req -config intermediate/openssl.cnf \
-key intermediate/private/server.key \
-new -sha256 -out intermediate/csr/server.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:pgserver.domain.us
Email Address []:
Sign the certificate using the intermediate CA:
# openssl ca -config intermediate/openssl.cnf \
-extensions server_cert -days 375 -notext -md sha256 \
-in intermediate/csr/server.csr \
-out intermediate/certs/server.crt
Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4098 (0x1002)
Validity
...
...
...
X509v3 Key Usage: critical
Digital Signature, Key Encipherment
X509v3 Extended Key Usage:
TLS Web Server Authentication
Certificate is to be certified until Jan 13 20:55:45 2019 GMT (375 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
We can verify the certificate as follows:
# openssl x509 -noout -text -in intermediate/certs/server.crt
Certificate:
Data:
Version: 3 (0x2)
Serial Number: 4098 (0x1002)
Signature Algorithm: sha256WithRSAEncryption
...
...
...
The CA chain file should have been created if you followed the guide. If not, this is how you can do it:
# cd /root/ca/
# cat intermediate/certs/intermediate.cert.pem /root/ca/certs/ca.cert.pem > ca-chain.cert.pem
# chmod 444 intermediate/certs/ca-chain.cert.pem
As root, copy the ca-chain.cert.pem, server.crt and server.key files to the $PGDATA directory:
# export PGDATA=/var/lib/pgsql/9.6/data
# cd /root/ca
# cp intermediate/certs/ca-chain.cert.pem $PGDATA/root.crt
# cp intermediate/certs/server.crt $PGDATA
# cp intermediate/private/server.key $PGDATA
# cd $PGDATA
# chown postgres:postgres root.crt server.crt server.key
As the postgres user, make the following changes to the $PGDATA/postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
Restart the PostgreSQL server:
$ pg_ctl restart
Now create a certificate for the postges user:
# cd /root/ca
# openssl genrsa -out intermediate/private/postgresql.key 2048
# chmod 400 intermediate/private/postgresql.key
Create the certificate:
# cd /root/ca
# openssl req -config intermediate/openssl.cnf \
-key intermediate/private/postgresql.key \
-new -sha256 -out intermediate/csr/postgresql.csr
Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:postgres.domain.us
Email Address []:
As before, sign the certificate request using the intermediate CA:
# cd /root/ca
# openssl ca -config intermediate/openssl.cnf \
-extensions server_cert -days 375 -notext -md sha256 \
-in intermediate/csr/postgresql.csr \
-out intermediate/certs/postgresql.crt
Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4099 (0x1003)
Validity
Not Before: Jan 5 16:52:54 2018 GMT
Not After : Jan 15 16:52:54 2019 GMT
...
...
...
TLS Web Server Authentication
Certificate is to be certified until Jan 15 16:52:54 2019 GMT (375 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
Make usre /var/lib/pgsql/.postgresql exists and is owned by postgres.
Next, copy the following to /var/lib/pgsql/.postgresql:
# cd /root/ca
# cp intermediate/certs/postgresql.crt /var/lib/pgsql/.postgresql
# cp intermediate/private/postgresql.key /var/lib/pgsql/.postgresql
# cp intermediate/certs/ca-chain.cert.pem /var/lib/pgsql/.postgresql/root.crt
# chown postgres:postgres /var/lib/pgsql/.postgresql/*
Our host IP is 10.10.10.123. This is reflected in the pg_hba.conf file.
As the postgres user, make the following changes to $PGDATA/pg_hba.conf and $PGDATA/pg_ident.conf:
pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ssl-test postgres.domain.us postgres
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 10.10.10.123/32 cert clientcert=1 map=ssl-test
Now, we can test our SSL connection to the database:
$ cd /var/lib/pgsql/.postgresql
$ psql 'host=10.10.10.123 port=5432 dbname=postgres user=postgres sslmode=require sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=root.crt'
psql (9.6.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
Logs should show something like the following:
Jan 5 12:14:20 pgs01 postgres[3817]: [6-1] 2018-01-05 12:14:20 EST [3817]: [1] user=postgres,db=postgres 2018-01-05 12:14:20.975 EST 00000 5a4fb26c.ee9 pgs01(37242) [unknown] 2018-01-05 12:14:20 EST LOG: connection authorized: user=postgres database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
Part II of this post will describe how to connect to PostgreSQL using Java and certificates.
Before we, start we'll build a root CA and an intermediate CA. This way, we can generate certificates when we please.
This site was very clear and concise in detailing how to setup the above structure. We'll use RHEL 7.4 and PostgreSQL 9.6.5.
Note that you may customize your openssl.cnf for your own needs. Again, this is a test scenario. You would most likely be getting your certificates from a certified CA.
Once your root and intermediate CAs' are set up, generate a certificate signing request (CSR) for your PostgreSQL server:
# cd /root/ca
# openssl genrsa -out intermediate/private/server.key 2048
# chmod 400 intermediate/private/server.key
Create the certificate:
# openssl req -config intermediate/openssl.cnf \
-key intermediate/private/server.key \
-new -sha256 -out intermediate/csr/server.csr
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:pgserver.domain.us
Email Address []:
Sign the certificate using the intermediate CA:
# openssl ca -config intermediate/openssl.cnf \
-extensions server_cert -days 375 -notext -md sha256 \
-in intermediate/csr/server.csr \
-out intermediate/certs/server.crt
Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4098 (0x1002)
Validity
...
...
...
X509v3 Key Usage: critical
Digital Signature, Key Encipherment
X509v3 Extended Key Usage:
TLS Web Server Authentication
Certificate is to be certified until Jan 13 20:55:45 2019 GMT (375 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
We can verify the certificate as follows:
# openssl x509 -noout -text -in intermediate/certs/server.crt
Certificate:
Data:
Version: 3 (0x2)
Serial Number: 4098 (0x1002)
Signature Algorithm: sha256WithRSAEncryption
...
...
...
The CA chain file should have been created if you followed the guide. If not, this is how you can do it:
# cd /root/ca/
# cat intermediate/certs/intermediate.cert.pem /root/ca/certs/ca.cert.pem > ca-chain.cert.pem
# chmod 444 intermediate/certs/ca-chain.cert.pem
As root, copy the ca-chain.cert.pem, server.crt and server.key files to the $PGDATA directory:
# export PGDATA=/var/lib/pgsql/9.6/data
# cd /root/ca
# cp intermediate/certs/ca-chain.cert.pem $PGDATA/root.crt
# cp intermediate/certs/server.crt $PGDATA
# cp intermediate/private/server.key $PGDATA
# cd $PGDATA
# chown postgres:postgres root.crt server.crt server.key
As the postgres user, make the following changes to the $PGDATA/postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
Restart the PostgreSQL server:
$ pg_ctl restart
Now create a certificate for the postges user:
# cd /root/ca
# openssl genrsa -out intermediate/private/postgresql.key 2048
# chmod 400 intermediate/private/postgresql.key
Create the certificate:
# cd /root/ca
# openssl req -config intermediate/openssl.cnf \
-key intermediate/private/postgresql.key \
-new -sha256 -out intermediate/csr/postgresql.csr
Country Name (2 letter code) [US]:
State or Province Name [CA]:
Locality Name [San Jose]:
Organization Name [Alice Ltd]:
Organizational Unit Name []:Alice Ltd Web Services
Common Name []:postgres.domain.us
Email Address []:
As before, sign the certificate request using the intermediate CA:
# cd /root/ca
# openssl ca -config intermediate/openssl.cnf \
-extensions server_cert -days 375 -notext -md sha256 \
-in intermediate/csr/postgresql.csr \
-out intermediate/certs/postgresql.crt
Using configuration from intermediate/openssl.cnf
Enter pass phrase for /root/ca/intermediate/private/intermediate.key.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
Serial Number: 4099 (0x1003)
Validity
Not Before: Jan 5 16:52:54 2018 GMT
Not After : Jan 15 16:52:54 2019 GMT
...
...
...
TLS Web Server Authentication
Certificate is to be certified until Jan 15 16:52:54 2019 GMT (375 days)
Sign the certificate? [y/n]:y
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
Make usre /var/lib/pgsql/.postgresql exists and is owned by postgres.
Next, copy the following to /var/lib/pgsql/.postgresql:
# cd /root/ca
# cp intermediate/certs/postgresql.crt /var/lib/pgsql/.postgresql
# cp intermediate/private/postgresql.key /var/lib/pgsql/.postgresql
# cp intermediate/certs/ca-chain.cert.pem /var/lib/pgsql/.postgresql/root.crt
# chown postgres:postgres /var/lib/pgsql/.postgresql/*
Our host IP is 10.10.10.123. This is reflected in the pg_hba.conf file.
As the postgres user, make the following changes to $PGDATA/pg_hba.conf and $PGDATA/pg_ident.conf:
pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ssl-test postgres.domain.us postgres
pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 10.10.10.123/32 cert clientcert=1 map=ssl-test
Now, we can test our SSL connection to the database:
$ cd /var/lib/pgsql/.postgresql
$ psql 'host=10.10.10.123 port=5432 dbname=postgres user=postgres sslmode=require sslcert=postgresql.crt sslkey=postgresql.key sslrootcert=root.crt'
psql (9.6.5)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
postgres=#
Logs should show something like the following:
Jan 5 12:14:20 pgs01 postgres[3817]: [6-1] 2018-01-05 12:14:20 EST [3817]: [1] user=postgres,db=postgres 2018-01-05 12:14:20.975 EST 00000 5a4fb26c.ee9 pgs01(37242) [unknown] 2018-01-05 12:14:20 EST LOG: connection authorized: user=postgres database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
Tuesday, January 2, 2018
PostgreSQL Backup Script
This script backups a PostgreSQL cluster.
It is assumed that this cluster is STIG'd using DISA STIG guidelines. SSL is turned on and configured correctly.
The following parameters are set in postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /data/pgsql/9.6/wals/%f'
For this case:
PGDATA=/data/pgsql/9.6/data
BKUPDIR=/var/lib/pgsql/9.6/backup
For security reason, SSL is turned on. The following is set in postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
It is assumed that valid certs have been installed for PostgreSQL.
The pg_hba.conf file looks like this:
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 10.10.10.123/32 cert clientcert=1 map=ssl-test
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv4 network connections
hostnossl all all 0.0.0.0/0 reject
The pg_ident.hba file looks like this:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ssl-test postgres.dmn.org postgres
The backup script is shown below:
#!/bin/sh
# pg_basebackup won't work with hostssl and cert clientcert=1
# so, we do the backup using the API
LABEL="Base Backup on $(/bin/hostname) on $(/bin/date +%Y%m%d%S)"
DT=`/bin/date +%Y%m%d%S`
BKUPDIR=/var/lib/pgsql/9.6/backup
HOST=pgs01.dmn.mil
STR="hostssl replication postgres 10.10.10.123/32 trust"
echo $STR
/bin/echo $STR >> /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload
/usr/pgsql-9.6/bin/pg_basebackup -D ${BKUPDIR}/${DT} --xlog-method=fetch --format=tar --write-recovery-conf --progress --verbose --username=postgres --no-password --gzip --host=${HOST} --label="${LABEL}"
/bin/sed -i '/hostssl replication postgres 10.10.10.123\/32 trust/d' /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload
Before the backup begins, the script adds the hostssl replication line defined by STR above and does a pg_ctl reload so that the changes in pg_hba.conf are picked by the postgres server. This allows a trusted connection to be made from the local server using SSL (or you could simply change this to 127.0.0.1).
The backup is executed using pg_basebackup.
After the backup is completed, the hostssl replication line is removed and the pg_hba.conf file is reloaded. This is done for STIG compliance.
The backup is run from root's cron once a week:
# PostgreSQL Weekly Full Backups on Sundays
0 0 * * 0 /bin/su -l postgres -c "/bin/sh /data/pgsql/bkup.sh"
It is assumed that this cluster is STIG'd using DISA STIG guidelines. SSL is turned on and configured correctly.
The following parameters are set in postgresql.conf
wal_level = replica
archive_mode = on
archive_command = 'cp %p /data/pgsql/9.6/wals/%f'
For this case:
PGDATA=/data/pgsql/9.6/data
BKUPDIR=/var/lib/pgsql/9.6/backup
For security reason, SSL is turned on. The following is set in postgresql.conf:
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'
It is assumed that valid certs have been installed for PostgreSQL.
The pg_hba.conf file looks like this:
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 10.10.10.123/32 cert clientcert=1 map=ssl-test
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv4 network connections
hostnossl all all 0.0.0.0/0 reject
The pg_ident.hba file looks like this:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
ssl-test postgres.dmn.org postgres
The backup script is shown below:
#!/bin/sh
# pg_basebackup won't work with hostssl and cert clientcert=1
# so, we do the backup using the API
LABEL="Base Backup on $(/bin/hostname) on $(/bin/date +%Y%m%d%S)"
DT=`/bin/date +%Y%m%d%S`
BKUPDIR=/var/lib/pgsql/9.6/backup
HOST=pgs01.dmn.mil
STR="hostssl replication postgres 10.10.10.123/32 trust"
echo $STR
/bin/echo $STR >> /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload
/usr/pgsql-9.6/bin/pg_basebackup -D ${BKUPDIR}/${DT} --xlog-method=fetch --format=tar --write-recovery-conf --progress --verbose --username=postgres --no-password --gzip --host=${HOST} --label="${LABEL}"
/bin/sed -i '/hostssl replication postgres 10.10.10.123\/32 trust/d' /data/pgsql/9.6/data/pg_hba.conf
/usr/pgsql-9.6/bin/pg_ctl reload
Before the backup begins, the script adds the hostssl replication line defined by STR above and does a pg_ctl reload so that the changes in pg_hba.conf are picked by the postgres server. This allows a trusted connection to be made from the local server using SSL (or you could simply change this to 127.0.0.1).
The backup is executed using pg_basebackup.
After the backup is completed, the hostssl replication line is removed and the pg_hba.conf file is reloaded. This is done for STIG compliance.
The backup is run from root's cron once a week:
# PostgreSQL Weekly Full Backups on Sundays
0 0 * * 0 /bin/su -l postgres -c "/bin/sh /data/pgsql/bkup.sh"
Friday, December 22, 2017
PostgreSQL 9.6.5 STIG Fix Script
The PostgreSQL 9.6.5 has about 111 checks. Most of these checks are repetitive and deal with settings in the postgresql.conf file.
The script below assumes that this is a fresh PostgreSQL install. It fixes 52 of the 111 checks by modifying the postgresql.conf file. The script can be improved upon and other checks can be added. But as a start, if you are standing up many PostgreSQL clusters, and need them to be STIG'd, this script can set about half of your open findings.
It assumes that the pgaudit RPMs have not been installed and that these RPMs are available in the same location that the script is run from. The check_rpm_files functions does this. The check_rpms functions looks to see if the RPMs are installed. If not, it will install the RPMs. If you already have pgaudit installed, comment out these calls in the script.
pg_9.6.5_stig_fixes.sh
#!/bin/bash
check_rpms() {
# Check if we have pgaudit RPMs installed
PGAUDIT=`echo $PGAUDITRPM | awk -F ".rpm" '{print $1}'`
CHECK=`rpm -qa|grep $PGAUDIT`
if [ $? -ne 1 ]
then
echo "$PGAUDITRPM is installed...skipping install"
else
RPMLIST="$RPMLIST $PGAUDITRPM"
echo $RPMLIST
INSTALLRPM=true
fi
PGAUDITDEBUG=`echo $PGAUDITDEBUGRPM | awk -F ".rpm" '{print $1}'`
CHECK=`rpm -qa|grep $PGAUDITDEBUG`
if [ $? -ne 1 ]
then
echo "$PGAUDITDEBUGRPM is installed...skipping install"
else
RPMLIST="$RPMLIST $PGAUDITDEBUGRPM"
echo "RPMS to install $RPMLIST"
INSTALLRPM=true
fi
if [ "$INSTALLRPM" = true ]
then
echo "This is our list.....$RPMLIST"
yum install $RPMLIST -y
fi
}
check_pgdata() {
# Assume that PGDATA=/var/lib/pgsql/9.6/data
if [ "$1" != "" ]; then
PGDATA=$1
else
PGDATA=/var/lib/pgsql/9.6/data
fi
# Check if PGDATA exists
if [ ! -d $PGDATA ]
then
echo "$PGDATA does not exist!"
exit 1
fi
}
check_rpm_files() {
# Our pgaudit RPMs should be in the same directory that the script runs from
for RPMS in $PGAUDITRPM $PGAUDITDEBUGRPM; do
if [ ! -f $RPMS ]
then
echo "RPM file $RPMS does not exist!"
exit 1
fi
done
}
logger() {
printf "\n$1\n\n" >> /tmp/$LOGFILE
#echo $1 >> /tmp/$LOGFILE
}
#############
### FIXES ###
#############
fix_V-72843() {
CHK=`grep pgaudit.log_catalog $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_catalog = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i 's/\(#pgaudit\|pgaudit\)\.log_catalog.*/pgaudit.log_catalog = on/g' $PGDATA/postgresql.conf
fi
CHK=`grep pgaudit.log_level $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_level = log" >> $PGDATA/postgresql.conf
else
# String found
sed -i 's/\(#pgaudit\|pgaudit\)\.log_level.*/pgaudit.log_level = log/g' $PGDATA/postgresql.conf
fi
CHK=`grep pgaudit.log_parameter $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_parameter = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i 's/\(#pgaudit\|pgaudit\)\.log_parameter.*/pgaudit.log_parameter = on/g' $PGDATA/postgresql.conf
fi
CHK=`grep pgaudit.log_statement_once $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_statement_once = off" >> $PGDATA/postgresql.conf
else
# String found
sed -i 's/\(#pgaudit\|pgaudit\)\.log_statement_once.*/pgaudit.log_statement_once = off/g' $PGDATA/postgresql.conf
fi
CHK=`grep 'pgaudit.log=\|pgaudit.log =' $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log = 'all, -misc, ddl, write, role, read, function'" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#pgaudit\|pgaudit\)\.log\(=\| =\).*/pgaudit.log = 'all, -misc, ddl, write, role, read, function'/g" $PGDATA/postgresql.conf
fi
CHK=`grep log_line_prefix $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_line_prefix = '%t [%p]: [%1-1] user=%u,db=%d %m %e %c %r %a %s '" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_line_prefix\|log_line_prefix\).*/log_line_prefix = '%t [%p]: [%1-1] user=%u,db=%d %m %e %c %r %a %s '/g" $PGDATA/postgresql.conf
fi
CHK=`grep log_error_verbosity $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_error_verbosity = default " >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_error_verbosity\|log_error_verbosity\).*/log_error_verbosity = default/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-72843:CAT II:PostgreSQL must produce audit records containing sufficient information to establish the outcome (success or failure) of the events."
}
fix_V-72847() {
# Will fix later
#logger "FIXED:V-72847:CAT II:The audit information produced by PostgreSQL must be protected from unauthorized modification."
echo "Will fix later..."
}
fix_V-72851() {
CHK=`grep client_min_messages $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "client_min_messages = error '" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#client_min_messages\|client_min_messages\).*/client_min_messages = error/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-72851:CAT II:PostgreSQL must provide non-privileged users with error messages that provide information necessary for corrective actions without revealing information that could be exploited by adversaries."
}
fix_V-72853() {
chown postgres ${PGDATA}/postgresql.conf
chmod 0600 ${PGDATA}/postgresql.conf
chown root:root /usr/pgsql-9.*/lib/*.so
chmod 0755 /usr/pgsql-9.*/lib/*.so
logger "FIXED:V-72853:CAT II:Privileges to change PostgreSQL software modules must be limited."
}
fix_V-72863() {
CHK=`grep max_connections $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "max_connections = 500 '" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#max_connections\|max_connections\).*/max_connections = 500/g" $PGDATA/postgresql.conf
fi
runuser -l postgres -c psql < $CWD/V-72863.sql > $CWD/fix_V-72863.sql
# clean up fix file
sed -i '/\?column\?/d' $CWD/fix_V-72863.sql
sed -i '/^---*./d' $CWD/fix_V-72863.sql
sed -i '/([0-9].*/d' $CWD/fix_V-72863.sql
# run fix file
runuser -l postgres -c psql < $CWD/fix_V-72863.sql
logger "FIXED:V-72863:CAT II:PostgreSQL must limit the number of concurrent sessions to an organization-defined number per user for all accounts and/or account types."
}
fix_V-72885() {
CHK=`grep log_file_mode $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_file_mode = 0600" >> $PGDATA/postgresql.conf
else
# Grab the comment after the command
STRING=`perl -ne '/(\s+# creation mode for log files,.*)/ && print($1)."" && exit' $PGDATA/postgresql.conf`
# String found
sed -i "s/\(#log_file_mode\|log_file_mode\).*/log_file_mode = 0600${STRING}/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-72885:CAT II:The audit information produced by PostgreSQL must be protected from unauthorized deletion."
}
fix_V-72891() {
chown postgres:postgres ${PGDATA?}/postgresql.conf
chmod 600 ${PGDATA?}/postgresql.conf
logger "REVIEW:V-72891:CAT II:PostgreSQL must allow only the ISSM (or individuals or roles appointed by the ISSM) to select which auditable events are to be audited."
}
fix_V-72919() {
logger "fix_V-72919 was fixed by fix_V-72843"
logger "FIXED:V-72919:CAT II:PostgreSQL must generate audit records when categorized information (e.g., classification levels/security levels) is accessed."
}
fix_V-72921() {
logger "fix_V-72921 was fixed by fix_V-72843"
logger "FIXED:V-72921:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to access security objects occur."
}
fix_V-72923() {
CHK=`grep log_connections $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_connections = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_connections\|log_connections\).*/log_connections = on/g" $PGDATA/postgresql.conf
fi
logger "log_line_prefix was fixed by fix_V-72843"
logger "FIXED:V-72923:CAT II:PostgreSQL must generate audit records when unsuccessful logons or connection attempts occur."
}
fix_V-72925() {
CHK=`grep log_connections $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_disconnections = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_disconnections\|log_disconnections\).*/log_disconnections = on/g" $PGDATA/postgresql.conf
fi
logger "log_line_prefix was fixed by fix_V-72843"
logger "log_connections was fixed by fix_V-72923"
logger "FIXED:V-72925:CAT II:PostgreSQL must generate audit records showing starting and ending time for user access to the database(s)."
}
fix_V-72927(){
logger "fix_V-72927 was fixed by fix_V-72843"
logger "FIXED:V-72927:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to modify security objects occur."
}
fix_V-72929() {
logger "fix_V-72929 was fixed by fix_V-72843"
logger "FIXED:V-72929:CAT II:PostgreSQL must generate audit records when privileges/permissions are added."
}
fix_V-72931() {
CHK=`grep log_file_mode $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "shared_preload_libraries = 'pgaudit'" >> $PGDATA/postgresql.conf
else
# Grab the comment after the command
STRING=`perl -ne '/(\s+\# \(change requires restart\).*)/ && print($1)."" && exit' $PGDATA/postgresql.conf`
# String found
sed -i "s/\(#shared_preload_libraries\|shared_preload_libraries\).*/shared_preload_libraries = 'pgaudit'${STRING}/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-72931:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to delete categorized information (e.g., classification levels/security levels) occur."
}
fix_V-72933() {
logger "fix_V-72933 was fixed by fix_V-72843 and fix_V-72923"
logger "FIXED:V-72933:CAT II:PostgreSQL must generate audit records when successful logons or connections occur."
}
fix_V-72939() {
logger "fix_V-72939 was fixed by fix_V-72843"
logger "FIXED:V-72939:CAT II:PostgreSQL must generate audit records when security objects are deleted."
}
fix_V-72941() {
logger "fix_V-72941 was fixed by fix_V-72843"
logger "FIXED:V-72941:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to retrieve privileges/permissions occur."
}
fix_V-72945() {
logger "fix_V-72945 was fixed by fix_V-72843"
logger "FIXED:V-72945:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to delete privileges/permissions occur."
}
fix_V-72947() {
logger "fix_V-72947 was fixed by fix_V-72843 and fix_V-72931"
logger "FIXED:V-72947:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to delete privileges/permissions occur."
}
fix_V-72949() {
logger "fix_V-72949 was fixed by fix_V-72843 and fix_V-72931"
logger "FIXED:V-72949:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to modify categorized information (e.g., classification levels/security levels) occur."
}
fix_V-72951() {
logger "fix_V-72951 was fixed by fix_V-72843"
logger "FIXED:V-72951:CAT II:PostgreSQL must generate audit records when unsuccessful accesses to objects occur."
}
fix_V-72953() {
logger "fix_V-72931 was fixed by fix_V-72843"
logger "FIXED:V-72953:CAT II:PostgreSQL must generate audit records for all privileged activities or other system-level access."
}
fix_V-72955() {
logger "fix_V-72955 was fixed by fix_V-72843"
logger "FIXED:V-72955:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to access categorized information (e.g., classification levels/security levels) occur."
}
fix_V-72957() {
logger "fix_V-72957 was fixed by fix_V-72843 and by fix_V-72931"
logger "FIXED:V-72957:CAT II:PostgreSQL must be able to generate audit records when security objects are accessed."
}
fix_V-72959() {
logger "fix_V-72959 was fixed by fix_V-72843"
logger "FIXED:V-72959:CAT II:PostgreSQL must generate audit records when privileges/permissions are deleted."
}
fix_V-72961() {
logger "fix_V-72961 was fixed by fix_V-72923, fix_V-72925 and fix_V-72843"
logger "FIXED:V-72961:CAT II:PostgreSQL must generate audit records when concurrent logons/connections by the same user from different workstations occur."
}
fix_V-72963() {
logger "fix_V-72963 was fixed by fix_V-72931 and fix_V-72843"
logger "FIXED:V-72963:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to delete security objects occur."
}
fix_V-72965() {
logger "fix_V-72965 was fixed by fix_V-72931 and fix_V-72843"
logger "FIXED:V-72965:CAT II:PostgreSQL must generate audit records when privileges/permissions are modified."
}
fix_V-72969() {
logger "fix_V-72969 was fixed by fix_V-72843"
logger "FIXED:V-72969:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to execute privileged activities or other system-level access occur."
}
fix_V-72971() {
logger "fix_V-72971 was fixed by fix_V-72931 and fix_V-72843"
logger "FIXED:V-72971:CAT II:PostgreSQL must generate audit records when security objects are modified."
}
fix_V-72973() {
logger "fix_V-72973 was fixed by fix_V-72931 and fix_V-72843"
logger "FIXED:V-72973:CAT II:PostgreSQL must generate audit records when categorized information (e.g., classification levels/security levels) is modified."
}
fix_V-72975() {
logger "fix_V-72975 was fixed by fix_V-72843"
logger "FIXED:V-72975:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to modify privileges/permissions occur."
}
fix_V-72977() {
logger "fix_V-72977 was fixed by fix_V-72843"
logger "FIXED:V-72977:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to add privileges/permissions occur."
}
fix_V-72985() {
logger "fix_V-72985 was fixed by fix_V-72843"
logger "FIXED:V-72985:CAT II:PostgreSQL must generate time stamps, for audit records and application data, with a minimum granularity of one second."
}
fix_V-72987() {
logger "fix_V-72987 was fixed by fix_V-72843"
logger "FIXED:V-72987:CAT II:PostgreSQL must produce audit records containing sufficient information to establish the identity of any user/subject or process associated with the event."
}
fix_V-73001() {
logger "fix_V-73001 was fixed by fix_V-72931"
logger "FIXED:V-73001:CAT II:PostgreSQL must initiate session auditing upon startup."
}
fix_V-73005() {
CHK=`grep log_hostname $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_hostname = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_hostname\|log_hostname\).*/log_hostname = on/g" $PGDATA/postgresql.conf
fi
logger "fix_V-73001 log_line_prefix was fixed by fix_V-72843"
logger "FIXED:V-73005:CAT II:PostgreSQL must produce audit records containing sufficient information to establish the sources (origins) of the events."
}
fix_V-73015() {
CHK=`grep password_encryption $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "password_encryption = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#password_encryption\|password_encryption\).*/password_encryption = on/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-73015:CAT II:If passwords are used for authentication, PostgreSQL must store only hashed, salted representations of passwords."
}
fix_V-73019() {
CHK=`grep pgaudit.log_relation $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_relation = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#pgaudit.log_relation\|pgaudit.log_relation\).*/pgaudit.log_relation = on/g" $PGDATA/postgresql.conf
fi
logger "fix_V-73019 was fixed by fix_V-72923 and fix_V-72931"
logger "FIXED:V-73019:CAT II:PostgreSQL must protect against a user falsely repudiating having performed organization-defined actions."
}
fix_V-73021(){
logger "fix_V-73021 was fixed by fix_V-72843, fix_V-72925 and fix_V-72843"
logger "FIXED:V-73019:CAT II:PostgreSQL must protect against a user falsely repudiating having performed organization-defined actions."
}
fix_V-73025() {
CHK=`grep pgaudit.role $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.role = 'auditor'" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#pgaudit.role\|pgaudit.role\).*/pgaudit.role = 'auditor'/g" $PGDATA/postgresql.conf
fi
logger "fix_V-73025 shared_preload_libraries was fixed by fix_V-72931"
logger "FIXED:V-73025:CAT II:PostgreSQL must provide the means for individuals in authorized roles to change the auditing to be performed on all application components, based on all selectable event criteria within organization-defined time thresholds."
}
fix_V-73033() {
logger "fix_V-73033 was fixed by fix_V-72923, fix_V-72925 and fix_V-72843"
logger "FIXED:V-73033:CAT II:PostgreSQL must produce audit records containing sufficient information to establish what type of events occurred."
}
fix_V-73037() {
CHK=`grep statement_timeout $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "statement_timeout = 10000" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#statement_timeout\|statement_timeout\).*/statement_timeout = 10000/g" $PGDATA/postgresql.conf
fi
CHK=`grep net.ipv4.tcp_keepalive_time /etc/sysctl.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "net.ipv4.tcp_keepalive_time = 1000" >> /etc/sysctl.conf
else
# String found
sed -i "s/\(#net.ipv4.tcp_keepalive_time\|net.ipv4.tcp_keepalive_time\).*/net.ipv4.tcp_keepalive_time = 1000/g" /etc/sysctl.conf
fi
CHK=`grep net.ipv4.tcp_keepalive_intvl /etc/sysctl.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "net.ipv4.tcp_keepalive_intvl = 1000" >> /etc/sysctl.conf
else
# String found
sed -i "s/\(#net.ipv4.tcp_keepalive_intvl\|net.ipv4.tcp_keepalive_intvl\).*/net.ipv4.tcp_keepalive_intvl = 1000/g" /etc/sysctl.conf
fi
CHK=`grep net.ipv4.tcp_keepalive_probes /etc/sysctl.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "net.ipv4.tcp_keepalive_probes = 10" >> /etc/sysctl.conf
else
# String found
sed -i "s/\(#net.ipv4.tcp_keepalive_probes\|net.ipv4.tcp_keepalive_probes\).*/net.ipv4.tcp_keepalive_probes = 10/g" /etc/sysctl.conf
fi
/sbin/sysctl -p
logger "FIXED:V-73037:CAT II:PostgreSQL must invalidate session identifiers upon user logout or other session termination."
}
fix_V-73039() {
chown -R postgres:postgres ${PGDATA?}
chown -R root:root /usr/pgsql-9.*/share/contrib/pgaudit 2>/dev/null
logger "NOTE: RUN SQL TO CHECK - FIX THIS TO BE AUTOMATED "
logger "FIXED:V-73039:CAT II:PostgreSQL must protect its audit features from unauthorized access."
}
fix_V-73041() {
logger "fix_V-73041 was fixed by fix_V-72843"
logger "FIXED:V-73041:CAT II:PostgreSQL must produce audit records containing time stamps to establish when the events occurred."
}
fix_V-73043() {
chown -R root:root /usr/pgsql-9*/bin
chown -R root:root /usr/pgsql-9*/share
chown -R root:root /usr/pgsql-9*/include
logger "fix_V-73043 was fixed by fix_V-73039"
logger "FIXED:V-73043:CAT II:PostgreSQL must protect its audit features from unauthorized removal."
}
fix_V-73045() {
CHK=`grep log_destination $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_destination = 'syslog'" >> $PGDATA/postgresql.conf
else
# Grab the comment after the command
STRING=`perl -ne '/(\s+\# Valid values are combinations of.*)/ && print($1)."" && exit' $PGDATA/postgresql.conf`
# String found
sed -i "s/\(#log_destination\|log_destination\).*/log_destination = 'syslog'${STRING}/g" $PGDATA/postgresql.conf
fi
CHK=`grep syslog_facility $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "syslog_facility = 'LOCAL0'" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#syslog_facility\|syslog_facility\).*/syslog_facility = 'LOCAL0'/g" $PGDATA/postgresql.conf
fi
CHK=`grep syslog_ident $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "syslog_ident = 'postgres'" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#syslog_ident\|syslog_ident\).*/syslog_ident = 'postgres'/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-73045:CAT II:PostgreSQL must off-load audit data to a separate log management facility; this must be continuous and in near real time for systems with a network connection to the storage facility and weekly or more often for stand-alone systems."
}
fix_V-73061() {
chown postgres:postgres ${PGDATA?}/*.conf
chmod 0600 ${PGDATA?}/*.conf
logger "fix_V-73061 was fixed by fix_V-72885"
logger "FIXED:V-73061:CAT II:PostgreSQL must protect its audit configuration from unauthorized modification."
}
fix_V-73065() {
logger "fix_V-73065 was fixed by fix_V-72843 and fix_V-72931"
logger "FIXED:V-73065:CAT II:Audit records must be generated when categorized information (e.g., classification levels/security levels) is deleted."
}
fix_V-73067() {
logger "fix_V-73067 was fixed by fix_V-72843 and fix_V-72931"
logger "FIXED:V-73067:CAT II:PostgreSQL must generate audit records when successful accesses to objects occur."
}
fix_V-73069() {
logger "fix_V-73069 was fixed by fix_V-72843, fix_V-72923 and fix_V-72925"
logger "FIXED:V-73069:CAT II:PostgreSQL must generate audit records for all direct access to the database(s)."
}
fix_V-73123() {
logger "fix_V-73123 was fixed by fix_V-72843"
logger "FIXED:V-73123:CAT II:PostgreSQL must produce audit records containing sufficient information to establish where the events occurred."
}
######################
####### main #########
######################
# Run as root. Assumes that pgaudit11_96 RPMS are accessible and will be installed.
# Set up our globals
PGAUDITRPM=pgaudit11_96-1.1.1-1.rhel7.x86_64.rpm
PGAUDITDEBUGRPM=pgaudit11_96-debuginfo-1.1.1-1.rhel7.x86_64.rpm
SCRIPT=`realpath $0`
CWD=`dirname $SCRIPT`
if [[ $EUID -ne 0 ]]; then
echo "This script must be run as root"
exit 1
fi
# Log file
LOGFILE=stig_fix_`date +%Y%m%d-%H%M%S`.log
check_pgdata $1
# Backup the postgresql.conf file
cp -rfp $PGDATA/postgresql.conf $PGDATA/postgresql.conf.`date +%Y%m%d-%H%M%S`
check_rpm_files
check_rpms
# 52 fixes
fix_V-72843
fix_V-72847
fix_V-72851
fix_V-72853
fix_V-72863
fix_V-72885
fix_V-72891
fix_V-72919
fix_V-72921
fix_V-72923
fix_V-72925
fix_V-72927
fix_V-72929
fix_V-72931
fix_V-72933
fix_V-72939
fix_V-72941
fix_V-72945
fix_V-72947
fix_V-72949
fix_V-72951
fix_V-72953
fix_V-72955
fix_V-72957
fix_V-72959
fix_V-72961
fix_V-72963
fix_V-72965
fix_V-72969
fix_V-72971
fix_V-72973
fix_V-72975
fix_V-72977
fix_V-72985
fix_V-72987
fix_V-73001
fix_V-73005
fix_V-73015
fix_V-73019
fix_V-73021
fix_V-73025
fix_V-73033
fix_V-73037
fix_V-73039
fix_V-73041
fix_V-73043
fix_V-73045
#fix_V-73047 - Easy fix with ssl=on in postgresql.conf
fix_V-73065
fix_V-73061
fix_V-73067
fix_V-73069
fix_V-73123
Place the V-72863.sql file in the same directory as the pg_9.6.5_stig_fixes.sh file:
-- V-72863.sql SQL file
SELECT 'ALTER ROLE '||rolname||' CONNECTION LIMIT 100;'
FROM pg_roles
WHERE rolconnlimit = -1
AND rolname <> 'pg_signal_backend';
The script below assumes that this is a fresh PostgreSQL install. It fixes 52 of the 111 checks by modifying the postgresql.conf file. The script can be improved upon and other checks can be added. But as a start, if you are standing up many PostgreSQL clusters, and need them to be STIG'd, this script can set about half of your open findings.
It assumes that the pgaudit RPMs have not been installed and that these RPMs are available in the same location that the script is run from. The check_rpm_files functions does this. The check_rpms functions looks to see if the RPMs are installed. If not, it will install the RPMs. If you already have pgaudit installed, comment out these calls in the script.
pg_9.6.5_stig_fixes.sh
#!/bin/bash
check_rpms() {
# Check if we have pgaudit RPMs installed
PGAUDIT=`echo $PGAUDITRPM | awk -F ".rpm" '{print $1}'`
CHECK=`rpm -qa|grep $PGAUDIT`
if [ $? -ne 1 ]
then
echo "$PGAUDITRPM is installed...skipping install"
else
RPMLIST="$RPMLIST $PGAUDITRPM"
echo $RPMLIST
INSTALLRPM=true
fi
PGAUDITDEBUG=`echo $PGAUDITDEBUGRPM | awk -F ".rpm" '{print $1}'`
CHECK=`rpm -qa|grep $PGAUDITDEBUG`
if [ $? -ne 1 ]
then
echo "$PGAUDITDEBUGRPM is installed...skipping install"
else
RPMLIST="$RPMLIST $PGAUDITDEBUGRPM"
echo "RPMS to install $RPMLIST"
INSTALLRPM=true
fi
if [ "$INSTALLRPM" = true ]
then
echo "This is our list.....$RPMLIST"
yum install $RPMLIST -y
fi
}
check_pgdata() {
# Assume that PGDATA=/var/lib/pgsql/9.6/data
if [ "$1" != "" ]; then
PGDATA=$1
else
PGDATA=/var/lib/pgsql/9.6/data
fi
# Check if PGDATA exists
if [ ! -d $PGDATA ]
then
echo "$PGDATA does not exist!"
exit 1
fi
}
check_rpm_files() {
# Our pgaudit RPMs should be in the same directory that the script runs from
for RPMS in $PGAUDITRPM $PGAUDITDEBUGRPM; do
if [ ! -f $RPMS ]
then
echo "RPM file $RPMS does not exist!"
exit 1
fi
done
}
logger() {
printf "\n$1\n\n" >> /tmp/$LOGFILE
#echo $1 >> /tmp/$LOGFILE
}
#############
### FIXES ###
#############
fix_V-72843() {
CHK=`grep pgaudit.log_catalog $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_catalog = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i 's/\(#pgaudit\|pgaudit\)\.log_catalog.*/pgaudit.log_catalog = on/g' $PGDATA/postgresql.conf
fi
CHK=`grep pgaudit.log_level $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_level = log" >> $PGDATA/postgresql.conf
else
# String found
sed -i 's/\(#pgaudit\|pgaudit\)\.log_level.*/pgaudit.log_level = log/g' $PGDATA/postgresql.conf
fi
CHK=`grep pgaudit.log_parameter $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_parameter = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i 's/\(#pgaudit\|pgaudit\)\.log_parameter.*/pgaudit.log_parameter = on/g' $PGDATA/postgresql.conf
fi
CHK=`grep pgaudit.log_statement_once $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_statement_once = off" >> $PGDATA/postgresql.conf
else
# String found
sed -i 's/\(#pgaudit\|pgaudit\)\.log_statement_once.*/pgaudit.log_statement_once = off/g' $PGDATA/postgresql.conf
fi
CHK=`grep 'pgaudit.log=\|pgaudit.log =' $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log = 'all, -misc, ddl, write, role, read, function'" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#pgaudit\|pgaudit\)\.log\(=\| =\).*/pgaudit.log = 'all, -misc, ddl, write, role, read, function'/g" $PGDATA/postgresql.conf
fi
CHK=`grep log_line_prefix $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_line_prefix = '%t [%p]: [%1-1] user=%u,db=%d %m %e %c %r %a %s '" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_line_prefix\|log_line_prefix\).*/log_line_prefix = '%t [%p]: [%1-1] user=%u,db=%d %m %e %c %r %a %s '/g" $PGDATA/postgresql.conf
fi
CHK=`grep log_error_verbosity $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_error_verbosity = default " >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_error_verbosity\|log_error_verbosity\).*/log_error_verbosity = default/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-72843:CAT II:PostgreSQL must produce audit records containing sufficient information to establish the outcome (success or failure) of the events."
}
fix_V-72847() {
# Will fix later
#logger "FIXED:V-72847:CAT II:The audit information produced by PostgreSQL must be protected from unauthorized modification."
echo "Will fix later..."
}
fix_V-72851() {
CHK=`grep client_min_messages $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "client_min_messages = error '" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#client_min_messages\|client_min_messages\).*/client_min_messages = error/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-72851:CAT II:PostgreSQL must provide non-privileged users with error messages that provide information necessary for corrective actions without revealing information that could be exploited by adversaries."
}
fix_V-72853() {
chown postgres ${PGDATA}/postgresql.conf
chmod 0600 ${PGDATA}/postgresql.conf
chown root:root /usr/pgsql-9.*/lib/*.so
chmod 0755 /usr/pgsql-9.*/lib/*.so
logger "FIXED:V-72853:CAT II:Privileges to change PostgreSQL software modules must be limited."
}
fix_V-72863() {
CHK=`grep max_connections $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "max_connections = 500 '" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#max_connections\|max_connections\).*/max_connections = 500/g" $PGDATA/postgresql.conf
fi
runuser -l postgres -c psql < $CWD/V-72863.sql > $CWD/fix_V-72863.sql
# clean up fix file
sed -i '/\?column\?/d' $CWD/fix_V-72863.sql
sed -i '/^---*./d' $CWD/fix_V-72863.sql
sed -i '/([0-9].*/d' $CWD/fix_V-72863.sql
# run fix file
runuser -l postgres -c psql < $CWD/fix_V-72863.sql
logger "FIXED:V-72863:CAT II:PostgreSQL must limit the number of concurrent sessions to an organization-defined number per user for all accounts and/or account types."
}
fix_V-72885() {
CHK=`grep log_file_mode $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_file_mode = 0600" >> $PGDATA/postgresql.conf
else
# Grab the comment after the command
STRING=`perl -ne '/(\s+# creation mode for log files,.*)/ && print($1)."" && exit' $PGDATA/postgresql.conf`
# String found
sed -i "s/\(#log_file_mode\|log_file_mode\).*/log_file_mode = 0600${STRING}/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-72885:CAT II:The audit information produced by PostgreSQL must be protected from unauthorized deletion."
}
fix_V-72891() {
chown postgres:postgres ${PGDATA?}/postgresql.conf
chmod 600 ${PGDATA?}/postgresql.conf
logger "REVIEW:V-72891:CAT II:PostgreSQL must allow only the ISSM (or individuals or roles appointed by the ISSM) to select which auditable events are to be audited."
}
fix_V-72919() {
logger "fix_V-72919 was fixed by fix_V-72843"
logger "FIXED:V-72919:CAT II:PostgreSQL must generate audit records when categorized information (e.g., classification levels/security levels) is accessed."
}
fix_V-72921() {
logger "fix_V-72921 was fixed by fix_V-72843"
logger "FIXED:V-72921:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to access security objects occur."
}
fix_V-72923() {
CHK=`grep log_connections $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_connections = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_connections\|log_connections\).*/log_connections = on/g" $PGDATA/postgresql.conf
fi
logger "log_line_prefix was fixed by fix_V-72843"
logger "FIXED:V-72923:CAT II:PostgreSQL must generate audit records when unsuccessful logons or connection attempts occur."
}
fix_V-72925() {
CHK=`grep log_connections $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_disconnections = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_disconnections\|log_disconnections\).*/log_disconnections = on/g" $PGDATA/postgresql.conf
fi
logger "log_line_prefix was fixed by fix_V-72843"
logger "log_connections was fixed by fix_V-72923"
logger "FIXED:V-72925:CAT II:PostgreSQL must generate audit records showing starting and ending time for user access to the database(s)."
}
fix_V-72927(){
logger "fix_V-72927 was fixed by fix_V-72843"
logger "FIXED:V-72927:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to modify security objects occur."
}
fix_V-72929() {
logger "fix_V-72929 was fixed by fix_V-72843"
logger "FIXED:V-72929:CAT II:PostgreSQL must generate audit records when privileges/permissions are added."
}
fix_V-72931() {
CHK=`grep log_file_mode $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "shared_preload_libraries = 'pgaudit'" >> $PGDATA/postgresql.conf
else
# Grab the comment after the command
STRING=`perl -ne '/(\s+\# \(change requires restart\).*)/ && print($1)."" && exit' $PGDATA/postgresql.conf`
# String found
sed -i "s/\(#shared_preload_libraries\|shared_preload_libraries\).*/shared_preload_libraries = 'pgaudit'${STRING}/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-72931:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to delete categorized information (e.g., classification levels/security levels) occur."
}
fix_V-72933() {
logger "fix_V-72933 was fixed by fix_V-72843 and fix_V-72923"
logger "FIXED:V-72933:CAT II:PostgreSQL must generate audit records when successful logons or connections occur."
}
fix_V-72939() {
logger "fix_V-72939 was fixed by fix_V-72843"
logger "FIXED:V-72939:CAT II:PostgreSQL must generate audit records when security objects are deleted."
}
fix_V-72941() {
logger "fix_V-72941 was fixed by fix_V-72843"
logger "FIXED:V-72941:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to retrieve privileges/permissions occur."
}
fix_V-72945() {
logger "fix_V-72945 was fixed by fix_V-72843"
logger "FIXED:V-72945:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to delete privileges/permissions occur."
}
fix_V-72947() {
logger "fix_V-72947 was fixed by fix_V-72843 and fix_V-72931"
logger "FIXED:V-72947:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to delete privileges/permissions occur."
}
fix_V-72949() {
logger "fix_V-72949 was fixed by fix_V-72843 and fix_V-72931"
logger "FIXED:V-72949:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to modify categorized information (e.g., classification levels/security levels) occur."
}
fix_V-72951() {
logger "fix_V-72951 was fixed by fix_V-72843"
logger "FIXED:V-72951:CAT II:PostgreSQL must generate audit records when unsuccessful accesses to objects occur."
}
fix_V-72953() {
logger "fix_V-72931 was fixed by fix_V-72843"
logger "FIXED:V-72953:CAT II:PostgreSQL must generate audit records for all privileged activities or other system-level access."
}
fix_V-72955() {
logger "fix_V-72955 was fixed by fix_V-72843"
logger "FIXED:V-72955:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to access categorized information (e.g., classification levels/security levels) occur."
}
fix_V-72957() {
logger "fix_V-72957 was fixed by fix_V-72843 and by fix_V-72931"
logger "FIXED:V-72957:CAT II:PostgreSQL must be able to generate audit records when security objects are accessed."
}
fix_V-72959() {
logger "fix_V-72959 was fixed by fix_V-72843"
logger "FIXED:V-72959:CAT II:PostgreSQL must generate audit records when privileges/permissions are deleted."
}
fix_V-72961() {
logger "fix_V-72961 was fixed by fix_V-72923, fix_V-72925 and fix_V-72843"
logger "FIXED:V-72961:CAT II:PostgreSQL must generate audit records when concurrent logons/connections by the same user from different workstations occur."
}
fix_V-72963() {
logger "fix_V-72963 was fixed by fix_V-72931 and fix_V-72843"
logger "FIXED:V-72963:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to delete security objects occur."
}
fix_V-72965() {
logger "fix_V-72965 was fixed by fix_V-72931 and fix_V-72843"
logger "FIXED:V-72965:CAT II:PostgreSQL must generate audit records when privileges/permissions are modified."
}
fix_V-72969() {
logger "fix_V-72969 was fixed by fix_V-72843"
logger "FIXED:V-72969:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to execute privileged activities or other system-level access occur."
}
fix_V-72971() {
logger "fix_V-72971 was fixed by fix_V-72931 and fix_V-72843"
logger "FIXED:V-72971:CAT II:PostgreSQL must generate audit records when security objects are modified."
}
fix_V-72973() {
logger "fix_V-72973 was fixed by fix_V-72931 and fix_V-72843"
logger "FIXED:V-72973:CAT II:PostgreSQL must generate audit records when categorized information (e.g., classification levels/security levels) is modified."
}
fix_V-72975() {
logger "fix_V-72975 was fixed by fix_V-72843"
logger "FIXED:V-72975:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to modify privileges/permissions occur."
}
fix_V-72977() {
logger "fix_V-72977 was fixed by fix_V-72843"
logger "FIXED:V-72977:CAT II:PostgreSQL must generate audit records when unsuccessful attempts to add privileges/permissions occur."
}
fix_V-72985() {
logger "fix_V-72985 was fixed by fix_V-72843"
logger "FIXED:V-72985:CAT II:PostgreSQL must generate time stamps, for audit records and application data, with a minimum granularity of one second."
}
fix_V-72987() {
logger "fix_V-72987 was fixed by fix_V-72843"
logger "FIXED:V-72987:CAT II:PostgreSQL must produce audit records containing sufficient information to establish the identity of any user/subject or process associated with the event."
}
fix_V-73001() {
logger "fix_V-73001 was fixed by fix_V-72931"
logger "FIXED:V-73001:CAT II:PostgreSQL must initiate session auditing upon startup."
}
fix_V-73005() {
CHK=`grep log_hostname $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_hostname = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#log_hostname\|log_hostname\).*/log_hostname = on/g" $PGDATA/postgresql.conf
fi
logger "fix_V-73001 log_line_prefix was fixed by fix_V-72843"
logger "FIXED:V-73005:CAT II:PostgreSQL must produce audit records containing sufficient information to establish the sources (origins) of the events."
}
fix_V-73015() {
CHK=`grep password_encryption $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "password_encryption = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#password_encryption\|password_encryption\).*/password_encryption = on/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-73015:CAT II:If passwords are used for authentication, PostgreSQL must store only hashed, salted representations of passwords."
}
fix_V-73019() {
CHK=`grep pgaudit.log_relation $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.log_relation = on" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#pgaudit.log_relation\|pgaudit.log_relation\).*/pgaudit.log_relation = on/g" $PGDATA/postgresql.conf
fi
logger "fix_V-73019 was fixed by fix_V-72923 and fix_V-72931"
logger "FIXED:V-73019:CAT II:PostgreSQL must protect against a user falsely repudiating having performed organization-defined actions."
}
fix_V-73021(){
logger "fix_V-73021 was fixed by fix_V-72843, fix_V-72925 and fix_V-72843"
logger "FIXED:V-73019:CAT II:PostgreSQL must protect against a user falsely repudiating having performed organization-defined actions."
}
fix_V-73025() {
CHK=`grep pgaudit.role $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "pgaudit.role = 'auditor'" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#pgaudit.role\|pgaudit.role\).*/pgaudit.role = 'auditor'/g" $PGDATA/postgresql.conf
fi
logger "fix_V-73025 shared_preload_libraries was fixed by fix_V-72931"
logger "FIXED:V-73025:CAT II:PostgreSQL must provide the means for individuals in authorized roles to change the auditing to be performed on all application components, based on all selectable event criteria within organization-defined time thresholds."
}
fix_V-73033() {
logger "fix_V-73033 was fixed by fix_V-72923, fix_V-72925 and fix_V-72843"
logger "FIXED:V-73033:CAT II:PostgreSQL must produce audit records containing sufficient information to establish what type of events occurred."
}
fix_V-73037() {
CHK=`grep statement_timeout $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "statement_timeout = 10000" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#statement_timeout\|statement_timeout\).*/statement_timeout = 10000/g" $PGDATA/postgresql.conf
fi
CHK=`grep net.ipv4.tcp_keepalive_time /etc/sysctl.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "net.ipv4.tcp_keepalive_time = 1000" >> /etc/sysctl.conf
else
# String found
sed -i "s/\(#net.ipv4.tcp_keepalive_time\|net.ipv4.tcp_keepalive_time\).*/net.ipv4.tcp_keepalive_time = 1000/g" /etc/sysctl.conf
fi
CHK=`grep net.ipv4.tcp_keepalive_intvl /etc/sysctl.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "net.ipv4.tcp_keepalive_intvl = 1000" >> /etc/sysctl.conf
else
# String found
sed -i "s/\(#net.ipv4.tcp_keepalive_intvl\|net.ipv4.tcp_keepalive_intvl\).*/net.ipv4.tcp_keepalive_intvl = 1000/g" /etc/sysctl.conf
fi
CHK=`grep net.ipv4.tcp_keepalive_probes /etc/sysctl.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "net.ipv4.tcp_keepalive_probes = 10" >> /etc/sysctl.conf
else
# String found
sed -i "s/\(#net.ipv4.tcp_keepalive_probes\|net.ipv4.tcp_keepalive_probes\).*/net.ipv4.tcp_keepalive_probes = 10/g" /etc/sysctl.conf
fi
/sbin/sysctl -p
logger "FIXED:V-73037:CAT II:PostgreSQL must invalidate session identifiers upon user logout or other session termination."
}
fix_V-73039() {
chown -R postgres:postgres ${PGDATA?}
chown -R root:root /usr/pgsql-9.*/share/contrib/pgaudit 2>/dev/null
logger "NOTE: RUN SQL TO CHECK - FIX THIS TO BE AUTOMATED "
logger "FIXED:V-73039:CAT II:PostgreSQL must protect its audit features from unauthorized access."
}
fix_V-73041() {
logger "fix_V-73041 was fixed by fix_V-72843"
logger "FIXED:V-73041:CAT II:PostgreSQL must produce audit records containing time stamps to establish when the events occurred."
}
fix_V-73043() {
chown -R root:root /usr/pgsql-9*/bin
chown -R root:root /usr/pgsql-9*/share
chown -R root:root /usr/pgsql-9*/include
logger "fix_V-73043 was fixed by fix_V-73039"
logger "FIXED:V-73043:CAT II:PostgreSQL must protect its audit features from unauthorized removal."
}
fix_V-73045() {
CHK=`grep log_destination $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "log_destination = 'syslog'" >> $PGDATA/postgresql.conf
else
# Grab the comment after the command
STRING=`perl -ne '/(\s+\# Valid values are combinations of.*)/ && print($1)."" && exit' $PGDATA/postgresql.conf`
# String found
sed -i "s/\(#log_destination\|log_destination\).*/log_destination = 'syslog'${STRING}/g" $PGDATA/postgresql.conf
fi
CHK=`grep syslog_facility $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "syslog_facility = 'LOCAL0'" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#syslog_facility\|syslog_facility\).*/syslog_facility = 'LOCAL0'/g" $PGDATA/postgresql.conf
fi
CHK=`grep syslog_ident $PGDATA/postgresql.conf`
if [ $? -eq 1 ]
then
# String not found, add it
echo "syslog_ident = 'postgres'" >> $PGDATA/postgresql.conf
else
# String found
sed -i "s/\(#syslog_ident\|syslog_ident\).*/syslog_ident = 'postgres'/g" $PGDATA/postgresql.conf
fi
logger "FIXED:V-73045:CAT II:PostgreSQL must off-load audit data to a separate log management facility; this must be continuous and in near real time for systems with a network connection to the storage facility and weekly or more often for stand-alone systems."
}
fix_V-73061() {
chown postgres:postgres ${PGDATA?}/*.conf
chmod 0600 ${PGDATA?}/*.conf
logger "fix_V-73061 was fixed by fix_V-72885"
logger "FIXED:V-73061:CAT II:PostgreSQL must protect its audit configuration from unauthorized modification."
}
fix_V-73065() {
logger "fix_V-73065 was fixed by fix_V-72843 and fix_V-72931"
logger "FIXED:V-73065:CAT II:Audit records must be generated when categorized information (e.g., classification levels/security levels) is deleted."
}
fix_V-73067() {
logger "fix_V-73067 was fixed by fix_V-72843 and fix_V-72931"
logger "FIXED:V-73067:CAT II:PostgreSQL must generate audit records when successful accesses to objects occur."
}
fix_V-73069() {
logger "fix_V-73069 was fixed by fix_V-72843, fix_V-72923 and fix_V-72925"
logger "FIXED:V-73069:CAT II:PostgreSQL must generate audit records for all direct access to the database(s)."
}
fix_V-73123() {
logger "fix_V-73123 was fixed by fix_V-72843"
logger "FIXED:V-73123:CAT II:PostgreSQL must produce audit records containing sufficient information to establish where the events occurred."
}
######################
####### main #########
######################
# Run as root. Assumes that pgaudit11_96 RPMS are accessible and will be installed.
# Set up our globals
PGAUDITRPM=pgaudit11_96-1.1.1-1.rhel7.x86_64.rpm
PGAUDITDEBUGRPM=pgaudit11_96-debuginfo-1.1.1-1.rhel7.x86_64.rpm
SCRIPT=`realpath $0`
CWD=`dirname $SCRIPT`
if [[ $EUID -ne 0 ]]; then
echo "This script must be run as root"
exit 1
fi
# Log file
LOGFILE=stig_fix_`date +%Y%m%d-%H%M%S`.log
check_pgdata $1
# Backup the postgresql.conf file
cp -rfp $PGDATA/postgresql.conf $PGDATA/postgresql.conf.`date +%Y%m%d-%H%M%S`
check_rpm_files
check_rpms
# 52 fixes
fix_V-72843
fix_V-72847
fix_V-72851
fix_V-72853
fix_V-72863
fix_V-72885
fix_V-72891
fix_V-72919
fix_V-72921
fix_V-72923
fix_V-72925
fix_V-72927
fix_V-72929
fix_V-72931
fix_V-72933
fix_V-72939
fix_V-72941
fix_V-72945
fix_V-72947
fix_V-72949
fix_V-72951
fix_V-72953
fix_V-72955
fix_V-72957
fix_V-72959
fix_V-72961
fix_V-72963
fix_V-72965
fix_V-72969
fix_V-72971
fix_V-72973
fix_V-72975
fix_V-72977
fix_V-72985
fix_V-72987
fix_V-73001
fix_V-73005
fix_V-73015
fix_V-73019
fix_V-73021
fix_V-73025
fix_V-73033
fix_V-73037
fix_V-73039
fix_V-73041
fix_V-73043
fix_V-73045
#fix_V-73047 - Easy fix with ssl=on in postgresql.conf
fix_V-73065
fix_V-73061
fix_V-73067
fix_V-73069
fix_V-73123
Place the V-72863.sql file in the same directory as the pg_9.6.5_stig_fixes.sh file:
-- V-72863.sql SQL file
SELECT 'ALTER ROLE '||rolname||' CONNECTION LIMIT 100;'
FROM pg_roles
WHERE rolconnlimit = -1
AND rolname <> 'pg_signal_backend';
Thursday, April 21, 2016
Creating and Registering a Plugin for OIM Using JDeveloper
This post is inspired by:
http://deepakdubeyontech.blogspot.com/2014/05/oim-11g-r2-ps2-notify-user-id-to-user.html
In this post, step-by-step instructions are given on how to create a custom component so that it can be registered to OIM 11gR2 PS2 or PS3.
Start JDeveloper
Select Java EE Edition and click OK
Click New Application
Click click the green '+' icon and Create in Project and then Java Class
Name your class e.g. NotifyUserIdToUser and click OK
Your screen should look like this:
Copy and paste this code into your new class:
package com.test.event.oim.user;
import static oracle.iam.identity.usermgmt.api.UserManagerConstants.AttributeName.MANAGER_KEY;
import static oracle.iam.identity.usermgmt.api.UserManagerConstants.AttributeName.USER_LOGIN;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import oracle.iam.identity.exception.NoSuchUserException;
import oracle.iam.identity.exception.UserLookupException;
import oracle.iam.identity.usermgmt.api.UserManager;
import oracle.iam.identity.usermgmt.vo.User;
import oracle.iam.notification.api.NotificationService;
import oracle.iam.notification.vo.NotificationEvent;
import oracle.iam.platform.Platform;
import oracle.iam.platform.authz.exception.AccessDeniedException;
import oracle.iam.platform.kernel.spi.PostProcessHandler;
import oracle.iam.platform.kernel.vo.AbstractGenericOrchestration;
import oracle.iam.platform.kernel.vo.BulkEventResult;
import oracle.iam.platform.kernel.vo.BulkOrchestration;
import oracle.iam.platform.kernel.vo.EventResult;
import oracle.iam.platform.kernel.vo.Orchestration;
import java.util.logging.Logger;
public class NotifyUserIdToUser implements PostProcessHandler {
private static final Logger log = Logger.getLogger( NotifyUserIdToUser.class.getName() );
public EventResult execute(long processId, long eventId,
Orchestration orchestration) {
return new EventResult();
}
private NotificationEvent createNotificationEvent(String poTemplateName,
String userKey) {
NotificationEvent event = null;
try {
event = new NotificationEvent();
String[] receiverUserIds = getRecipientUserIds(userKey);
event.setUserIds(receiverUserIds);
event.setTemplateName(poTemplateName);
event.setSender(null);
HashMap<String, Object> templateParams = new HashMap<String, Object>();
templateParams.put("usr_key", userKey);
event.setParams(templateParams);
log.info("Template Name " + poTemplateName);
} catch (Exception e) {
e.printStackTrace();
System.out.println("e-------->" + e.getMessage());
}
return event;
}
@Override
public void initialize(HashMap<String, String> arg0) {
Notice the red bars on the right which indicate missing jar files. Try and compile the program selecting Build -> Make Client2.jpr
The compiler log pane will display errors as shown below:
To fix these, we need to modify the class path so that the relevant libraries and jar files can be included. To do this, in the Application Navigator, right click on Client and from the pop-up menu, select Project Properties:
From the Project Properties menu, select Libraries and Classpath:
Click on the Add JAR/Directory:
Most of the required jars will be in your $IDM_HOME/server which in this case is $IDM_HOME=/app/fmw/Oracle_IDM1/server. In the Add Archive or Directory pop up, enter /app/fmw/Oracle_IDM1/server/client/oimclient.jar and click Select. See below:
The following will be displayed:
Repeat the above for the following jar files:
$IDM_HOME/server/platform/iam-platform-kernel.jar
$IDM_HOME/server/platform/iam-platform-context.jar
Your Project Properties -> Libraries and Classpath should look like this:
Click Build -> Rebuild Client2.jpr (or whatever your project is called). You should get a clean build with no errors:
Source and class files will be located here:
/home/oracle/Application2/Client2/classes
/home/oracle/Application2/Client2/src
Next, we are ready to create the jar file and use Ant to register the plugin to OIM.
Set your ant and OIM environment variable and place your ant variable in the path:
# e.g. IDM_HOME=/app/fmw/Oracle_IDM1
OIM_ORACLE_HOME=$IDM_HOME
ANT_HOME=$MW_HOME/modules/org.apache.ant_17.1
export PATH=$PATH:$ANT_HOME/bin
export ORACLE_OIM_HOME
Create a plugins directory, say /home/oracle/oim_plugins
cd /home/oracle/oim_plugins
mkdir -p stage/custom/lib
Copy class files from /home/oracle/Application2/Client2/classes to /home/oracle/oim_plugins/stage/lib as follows:
cd /home/oracle/oim_plugins/stage/custom/lib
cp -rfp /home/oracle/Application2/Client2/classes .
Create a plugin.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<oimplugins>
<plugins pluginpoint="oracle.iam.
<plugin pluginclass=
"com.test.event.oim.user.
version="1.0"
name="NotifyUserIDToUser">
</plugin>
</plugins>
</oimplugins>
Next, create a zip archive:
zip -r NotifyUser.zip lib plugin.xml
You are now ready for registering your custom component into OIM.
cd $IDM_HOME/server/plugin_utility
Register the plugin:
ant -f pluginregistration.xml register
Buildfile: pluginregistration.xml
register:
[echo]
[echo] ******************************
[echo] REGISTRATION TOOL TO REGISTER
[echo] ******************************
[echo] This tool can be used to register or unregister plugins to OIM.
[echo]
...
...
...
[input] Enter the oim user id:
xelsysadm
[input]Enter the oim user password:
[input] Enter the server url [WLS : t3://<host>:<port> WAS : corbaloc:iiop:<host>:<port> )]:
t3://localhost:14000
[input] Enter name (complete file name with path) of the plugin file:
/home/oracle/oim_plugins_ins/
-register-to-was-server:
-register-to-wls-server:
...
...
...
BUILD SUCCESSFUL
Total time: 1 minute 37 seconds
Check for errors in the output. If there are no error, your plugin should now be registered and ready for use.
http://deepakdubeyontech.blogspot.com/2014/05/oim-11g-r2-ps2-notify-user-id-to-user.html
In this post, step-by-step instructions are given on how to create a custom component so that it can be registered to OIM 11gR2 PS2 or PS3.
Start JDeveloper
Select Java EE Edition and click OK
Click New Application
Take the defaults and click Next
Give it a name, say Client2 and click Next
Give your default package a name such as com.test.event.oim.user.NotifyUserIdToUser and click Finish
Name your class e.g. NotifyUserIdToUser and click OK
Your screen should look like this:
Copy and paste this code into your new class:
package com.test.event.oim.user;
import static oracle.iam.identity.usermgmt.api.UserManagerConstants.AttributeName.MANAGER_KEY;
import static oracle.iam.identity.usermgmt.api.UserManagerConstants.AttributeName.USER_LOGIN;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import oracle.iam.identity.exception.NoSuchUserException;
import oracle.iam.identity.exception.UserLookupException;
import oracle.iam.identity.usermgmt.api.UserManager;
import oracle.iam.identity.usermgmt.vo.User;
import oracle.iam.notification.api.NotificationService;
import oracle.iam.notification.vo.NotificationEvent;
import oracle.iam.platform.Platform;
import oracle.iam.platform.authz.exception.AccessDeniedException;
import oracle.iam.platform.kernel.spi.PostProcessHandler;
import oracle.iam.platform.kernel.vo.AbstractGenericOrchestration;
import oracle.iam.platform.kernel.vo.BulkEventResult;
import oracle.iam.platform.kernel.vo.BulkOrchestration;
import oracle.iam.platform.kernel.vo.EventResult;
import oracle.iam.platform.kernel.vo.Orchestration;
import java.util.logging.Logger;
public class NotifyUserIdToUser implements PostProcessHandler {
private static final Logger log = Logger.getLogger( NotifyUserIdToUser.class.getName() );
public EventResult execute(long processId, long eventId,
Orchestration orchestration) {
return new EventResult();
}
private NotificationEvent createNotificationEvent(String poTemplateName,
String userKey) {
NotificationEvent event = null;
try {
event = new NotificationEvent();
String[] receiverUserIds = getRecipientUserIds(userKey);
event.setUserIds(receiverUserIds);
event.setTemplateName(poTemplateName);
event.setSender(null);
HashMap<String, Object> templateParams = new HashMap<String, Object>();
templateParams.put("usr_key", userKey);
event.setParams(templateParams);
log.info("Template Name " + poTemplateName);
} catch (Exception e) {
e.printStackTrace();
System.out.println("e-------->" + e.getMessage());
}
return event;
}
@Override
public void initialize(HashMap<String, String> arg0) {
}
@Override
public boolean cancel(long arg0, long arg1,
AbstractGenericOrchestration arg2) {
return false;
}
@Override
public void compensate(long arg0, long arg1,
AbstractGenericOrchestration arg2) {
}
@Override
public BulkEventResult execute(long l, long l1, BulkOrchestration bulkOrch) {
try {
System.out.println("Entering BulkEventResult of NotifyUserIDToUser");
System.out.println("l ->" + l);
System.out.println("l1 ->" + l1);
String oprType = bulkOrch.getOperation();
System.out.println("oprType ->" + oprType);
HashMap<String, Serializable>[] bulkParams = bulkOrch
.getBulkParameters();
for (HashMap<String, Serializable> bulkParam : bulkParams) {
System.out.println("bulkParam ->" + bulkParam);
Set<String> bulkKeySet = bulkParam.keySet();
System.out.println("bulkKeySet ->" + bulkKeySet);
String usrLogin = null;
String usrKey = null;
for (String key : bulkKeySet) {
System.out.println("key ->" + key);
Serializable serializable = bulkParam.get(key);
System.out.println("serializable ->" + serializable);
if (key.equalsIgnoreCase("User Login")) {
usrLogin = serializable.toString();
System.out.println("usrLogin ->" + usrLogin);
UserManager usrMgr = Platform
.getService(UserManager.class);
User user = usrMgr.getDetails(usrLogin, null, true);
usrKey = user.getEntityId(); // getAttribute("usr_key").toString();
String uid = user.getId();
System.out.println("uid--->" + uid);
System.out.println("usrKey ->" + usrKey);
String templateName = "Notify UserId to User";
NotificationService notService = Platform
.getService(NotificationService.class);
NotificationEvent eventToSend = this
.createNotificationEvent(templateName, usrKey);
notService.notify(eventToSend);
}
}
}
} catch (Exception e) {
System.out.println("exception e in BulkExecuteEvent ->"
+ e.getMessage());
e.printStackTrace();
}
System.out.println("Exiting BulkEventResult of NotifyUserIDToUser");
return new BulkEventResult();
}
private String[] getRecipientUserIds(String userKey)
throws NoSuchUserException, UserLookupException,
AccessDeniedException {
UserManager usrMgr = Platform.getService(UserManager.class);
User user = null;
String userId = null;
Set<String> userRetAttrs = new HashSet<String>();
userRetAttrs.add(MANAGER_KEY.getId());
userRetAttrs.add(USER_LOGIN.getId());
User manager = null;
String managerId = null;
String managerKey = null;
Set<String> managerRetAttrs = new HashSet<String>();
managerRetAttrs.add(USER_LOGIN.getId());
user = usrMgr.getDetails(userKey, userRetAttrs, false);
userId = user.getAttribute(USER_LOGIN.getId()).toString();
List<String> userIds = new ArrayList<String>();
userIds.add(userId);
if (user.getAttribute(MANAGER_KEY.getId()) != null) {
managerKey = user.getAttribute(MANAGER_KEY.getId()).toString();
manager = usrMgr.getDetails(managerKey, managerRetAttrs, false);
managerId = manager.getAttribute(USER_LOGIN.getId()).toString();
userIds.add(managerId);
}
String[] recipientIDs = userIds.toArray(new String[0]);
return recipientIDs;
}
}
Your JDeveloper screen should look like this:
The compiler log pane will display errors as shown below:
To fix these, we need to modify the class path so that the relevant libraries and jar files can be included. To do this, in the Application Navigator, right click on Client and from the pop-up menu, select Project Properties:
From the Project Properties menu, select Libraries and Classpath:
Click on the Add JAR/Directory:
Most of the required jars will be in your $IDM_HOME/server which in this case is $IDM_HOME=/app/fmw/Oracle_IDM1/server. In the Add Archive or Directory pop up, enter /app/fmw/Oracle_IDM1/server/client/oimclient.jar and click Select. See below:
The following will be displayed:
Repeat the above for the following jar files:
$IDM_HOME/server/platform/iam-platform-kernel.jar
$IDM_HOME/server/platform/iam-platform-context.jar
Your Project Properties -> Libraries and Classpath should look like this:
Click Build -> Rebuild Client2.jpr (or whatever your project is called). You should get a clean build with no errors:
Source and class files will be located here:
/home/oracle/Application2/Client2/classes
/home/oracle/Application2/Client2/src
Next, we are ready to create the jar file and use Ant to register the plugin to OIM.
Set your ant and OIM environment variable and place your ant variable in the path:
# e.g. IDM_HOME=/app/fmw/Oracle_IDM1
OIM_ORACLE_HOME=$IDM_HOME
ANT_HOME=$MW_HOME/modules/org.apache.ant_17.1
export PATH=$PATH:$ANT_HOME/bin
export ORACLE_OIM_HOME
Create a plugins directory, say /home/oracle/oim_plugins
cd /home/oracle/oim_plugins
mkdir -p stage/custom/lib
Copy class files from /home/oracle/Application2/Client2/classes to /home/oracle/oim_plugins/stage/lib as follows:
cd /home/oracle/oim_plugins/stage/custom/lib
cp -rfp /home/oracle/Application2/Client2/classes .
Create a plugin.xml file:
<?xml version="1.0" encoding="UTF-8"?>
<oimplugins>
<plugins pluginpoint="oracle.iam.
<plugin pluginclass=
"com.test.event.oim.user.
version="1.0"
name="NotifyUserIDToUser">
</plugin>
</plugins>
</oimplugins>
Next, create a zip archive:
zip -r NotifyUser.zip lib plugin.xml
You are now ready for registering your custom component into OIM.
cd $IDM_HOME/server/plugin_utility
Register the plugin:
ant -f pluginregistration.xml register
Buildfile: pluginregistration.xml
register:
[echo]
[echo] ******************************
[echo] REGISTRATION TOOL TO REGISTER
[echo] ******************************
[echo] This tool can be used to register or unregister plugins to OIM.
[echo]
...
...
...
[input] Enter the oim user id:
xelsysadm
[input]Enter the oim user password:
[input] Enter the server url [WLS : t3://<host>:<port> WAS : corbaloc:iiop:<host>:<port> )]:
t3://localhost:14000
[input] Enter name (complete file name with path) of the plugin file:
/home/oracle/oim_plugins_ins/
-register-to-was-server:
-register-to-wls-server:
...
...
...
BUILD SUCCESSFUL
Total time: 1 minute 37 seconds
Check for errors in the output. If there are no error, your plugin should now be registered and ready for use.
Subscribe to:
Posts (Atom)