Postgres system queries

check active connections

SELECT 
    pid
    ,datname as database
    ,usename as username
    ,application_name
    ,client_addr as client_address 
    ,client_hostname
    ,client_port
    ,backend_start
    ,query_start
    ,query
    ,state
FROM pg_stat_activity
WHERE state = 'active'
order by datname;

close connections

SELECT
pg_terminate_backend(pid)
FROM
pg_stat_activity
WHERE
-- don't kill my own connection!
pid <> pg_backend_pid()
-- don't kill the connections to other databases
AND datname = 'medical_center_sm'
;

 

SELECT a.datname,
l.relation::regclass,
l.transactionid,
l.mode,
l.GRANTED,
a.usename,
a.query,
a.query_start,
age(now(), a.query_start) AS “age”,
a.pid
FROM pg_stat_activity a
JOIN pg_locks l ON l.pid = a.pid
ORDER BY a.query_start;
— SELECT pg_terminate_backend(14832 );

[SOLVED] Denied DNS cache queries originating from 127.0.0.1

from here

i had thi situation:

[SOLVED] Denied DNS cache queries originating from 127.0.0.1

Hi,

recently Virtualmin complained that I needed to add 127.0.0.1 to the BIND Zones.
Since then I’ve started seeing the following entries in my /var/named/data/named.run:

13-Jan-2016 14:20:12.442 client 127.0.0.1#24778: query (cache) 'w3.org/NS/IN' denied
13-Jan-2016 14:20:12.442 client 127.0.0.1#24778: query (cache) 'comcast.net/NS/IN' denied
13-Jan-2016 14:20:12.443 client 127.0.0.1#24778: query (cache) 'akamai.com/NS/IN' denied
13-Jan-2016 14:21:03.839 client 127.0.0.1#33857: query (cache) 'cingular.com/NS/IN' denied
13-Jan-2016 14:21:03.839 client 127.0.0.1#33857: query (cache) 'kernel.org/NS/IN' denied
13-Jan-2016 14:21:03.840 client 127.0.0.1#33857: query (cache) 'gmx.net/NS/IN' denied
13-Jan-2016 19:55:38.304 client 127.0.0.1#30860: query (cache) 'sun.com/NS/IN' denied
13-Jan-2016 19:55:38.305 client 127.0.0.1#30860: query (cache) 'w3.org/NS/IN' denied
13-Jan-2016 19:55:38.307 client 127.0.0.1#30860: query (cache) 'mit.edu/NS/IN' denied

I already have the named-refused-udp and named-refused-tcp fail2ban jails activated, so I’m not worried about any such entries which originate from an external ip address.

What concerns me is those originating from 127.0.0.1.
Have I been hacked?

Thanks

i patch like this

I’ll answer my own question to help anyone else looking. To stop these entries add the following to /etc/named.conf

allow-query-cache { none; };
recursion no;
additional-from-auth no;
additional-from-cache no;
minimal-responses yes;

in this way:

options {

                listen-on port 53 { any; };

                listen-on-v6 port 53 { any; };

                directory       “/var/named”;

                dump-file       “/var/named/data/cache_dump.db”;

                statistics-file “/var/named/data/named_stats.txt”;

                memstatistics-file “/var/named/data/named_mem_stats.txt”;

                allow-query     { any; };

                allow-recursion {“none”;};

                recursion no;

                version “Not disclosed”;

                allow-query-cache { none; };

                additional-from-auth no;

                additional-from-cache no;

                minimal-responses yes;

…..

6 commands to check and list active SSH connections in Linux (connections in general)

1. Using ss command

ss is used to dump socket statistics. It allows showing information similar to netstat. It can display more TCP and state information than other tools. We will use grep function to only get the list of active SSH sessions on our local host

[root@node3 ~]# ss | grep -i ssh
tcp    ESTAB      0      0      10.0.2.32:ssh                  10.0.2.31:37802
tcp    ESTAB      0      64     10.0.2.32:ssh                  10.0.2.2:49966
tcp    ESTAB      0      0      10.0.2.32:ssh                  10.0.2.30:56088

From the above example we know that there are three hosts which are currently connected to our node3. We have active SSH connections from 10.0.2.31, 10.0.2.30 and 10.0.2.2

 

2. Using last command

last searches back through the file /var/log/wtmp (or the file designated by the -f flag) and displays a list of all users logged in (and out) since that file was created. Names of users and tty’s can be given, in which case last will show only those entries matching the arguments.

Using this command you can also get the information about the user using which the SSH connection was created between server and client. So below we know the connection from 10.0.2.31 is done using ‘deepak‘ user, while for other two hosts, ‘root‘ user was used for connecting to node3.

[root@node3 ~]# last -a | grep -i still
deepak   pts/1        Fri May 31 16:58   still logged in    10.0.2.31
root     pts/2        Fri May 31 16:50   still logged in    10.0.2.30
root     pts/0        Fri May 31 09:17   still logged in    10.0.2.2

Here I am grepping for a string “still” to get all the patterns with “still logged in“. So now we know we have three active SSH connections from 10.0.2.31, 10.0.2.30 and 10.0.2.2

 

3. Using who command

who is used to show who is logged on on your Linux host. This tool can also give this information

[root@node3 ~]# who
root     pts/0        2019-05-31 09:17 (10.0.2.2)
root     pts/1        2019-05-31 16:47 (10.0.2.31)
root     pts/2        2019-05-31 16:50 (10.0.2.30)

Using this command we also get similar information as from last command. Now you get the user details used for connecting to node3 from source host, also we have terminal information on which the session is still active.

We generally hear terminal as tty but here we see terminal is referenced as pts, but now:
What is the difference between tty and pts?
How to disable or enable individual tty terminal console in Linux?

 

4. Using w command

w displays information about the users currently on the machine, and their processes. This gives more information than who and last command and also serves our purpose to get the list of active SSH connections. Additionally it also gives us the information of the running process on those sessions.

Using w command you will also get the idle time details, i.e. for how long the session is idle. If the SSH session is idle for long period then it is a security breach and it is recommended that such idle SSH session must be killed, you can configure your Linux host to automatically kill such idle SSH session.

[root@node3 ~]# w
 17:01:41 up  7:44,  3 users,  load average: 0.00, 0.01, 0.05
USER     TTY      FROM             LOGIN@   IDLE   JCPU   PCPU WHAT
root     pts/0    10.0.2.2         09:17    9:41   0.31s  0.00s less -s
deepak   pts/1    10.0.2.31        16:58    3:06   0.03s  0.03s -bash
root     pts/2    10.0.2.30        16:50    5.00s  0.07s  0.02s w

 

5. Using netstat command

Similar to ss we have netstat command to show active ssh sessions. Actually we can also say that ss is the new version of netstat. Here we can see all the ESTABLISHED SSH sessions from remote hosts to our localhost node3. it is also possible that one or some of these active ssh connections are in hung state so you can configure your host to automatically disconnect or kill these hung or unresponsive ssh sessions in Linux.

[root@node3 ~]# netstat -tnpa | grep 'ESTABLISHED.*sshd'
tcp        0      0 10.0.2.32:22            10.0.2.31:37806         ESTABLISHED 10295/sshd: deepak
tcp        0      0 10.0.2.32:22            10.0.2.2:49966          ESTABLISHED 4329/sshd: root@pts
tcp        0      0 10.0.2.32:22            10.0.2.30:56088         ESTABLISHED 10125/sshd: root@pt

 

6. Using ps command

Now to show active ssh sessions, ps command may not give you accurate results like other commands we discussed in this article but it can give you some more additional information i.e. PID of the SSHD process which are currently active and connected.

# ps auxwww | grep sshd: | grep -v grep
root      4329  0.0  0.1 154648  5512 ?        Ss   09:17   0:00 sshd: root@pts/0
root     10125  0.0  0.1 154648  5532 ?        Ss   16:50   0:00 sshd: root@pts/2
root     10295  0.0  0.1 154648  5480 ?        Ss   16:58   0:00 sshd: deepak [priv]
deepak   10301  0.0  0.0 156732  2964 ?        S    16:58   0:00 sshd: deepak@pts/1

 

Check ssh connection history

To get the ssh connection history you can always check your SSHD logs for more information on connected or disconnected SSH session. Now the sshd log file may vary from distribution to distribution. On my RHEL 7.4 my sshd logs are stored inside /var/log/sshd

Lastly I hope the steps from the article to check active SSH connections and ssh connection history in Linux was helpful. So, let me know your suggestions and feedback using the comment section.

How to enable HTTPS certificate in a Spring Boot Java application

Setting up HTTPS for Spring Boot requires two steps:

  1. Getting an SSL certificate;
  2. Configuring SSL in Spring Boot.

We can generate an SSL certificate ourselves (self-signed certificate). Its use is intended just for development and testing purposes. In production, we should use a certificate issued by a trusted Certificate Authority (CA).

In either case, we’re going to see how to enable HTTPS in a Spring Boot application. Examples will be shown both for Spring Boot 1 and Spring Boot 2.

Introduction

In this tutorial, we’re going to:

  1. Get an SSL certificate
    • Generate a self-signed SSL certificate
    • Use an existing SSL certificate
  2. Enable HTTPS in Spring Boot
  3. Redirect HTTP requests to HTTPS
  4. Distribute the SSL certificate to clients.

If you don’t already have a certificate, follow the step 1a. If you have already got an SSL certificate, you can follow the step 1b.

Throughout this tutorial, I’ll use the following technologies and tools:

  • Java JDK 8
  • Spring Boot 2.2.2 and Spring Boot 1.5.22
  • keytool

Keytool is a certificate management utility provided together with the JDK, so if you have the JDK installed, you should already have keytool available. To check it, try running the command keytool --help from your Terminal prompt. Note that if you are on Windows, you might need to launch it from the \bin folder. For more information about this utility, you can read the official documentation.

On GitHub, you can find the source code for the application we are building in this tutorial.

1a. Generate a self-signed SSL certificate

First of all, we need to generate a pair of cryptographic keys, use them to produce an SSL certificate and store it in a keystore. The keytool documentation defines a keystore as a database of “cryptographic keys, X.509 certificate chains, and trusted certificates”.

To enable HTTPS, we’ll provide a Spring Boot application with this keystore containing the SSL certificate.

The two most common formats used for keystores are JKS, a proprietary format specific for Java, and PKCS12, an industry-standard format. JKS used to be the default choice, but now Oracle recommends to adopt the PKCS12 format. We’re going to see how to use both.

Generate an SSL certificate in a keystore

Let’s open our Terminal prompt and write the following command to create a JKS keystore:

keytool -genkeypair -alias tomcat -keyalg RSA -keysize 2048 -keystore keystore.jks -validity 3650 -storepass password

To create a PKCS12 keystore, and we should, the command is the following:

keytool -genkeypair -alias tomcat -keyalg RSA -keysize 2048 -storetype PKCS12 -keystore keystore.p12 -validity 3650 -storepass password

Let’s have a closer look at the command we just run:

  • genkeypair: generates a key pair;
  • alias: the alias name for the item we are generating;
  • keyalg: the cryptographic algorithm to generate the key pair;
  • keysize: the size of the key. We have used 2048 bits, but 4096 would be a better choice for production;
  • storetype: the type of keystore;
  • keystore: the name of the keystore;
  • validity: validity number of days;
  • storepass: a password for the keystore.

When running the previous command, we will be asked to input some information, but we are free to skip all of it (just press Return to skip an option). When asked if the information is correct, we should type yes. Finally, we hit return to use the keystore password as key password as well.

What is your first and last name? 
    [Unknown]: 
What is the name of your organizational unit? 
    [Unknown]: 
What is the name of your organization? 
    [Unknown]: 
What is the name of your City or Locality? 
    [Unknown]: 
What is the name of your State or Province? 
    [Unknown]: 
What is the two-letter country code for this unit? 
    [Unknown]: 
Is CN=localhost, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown correct? 
    [no]: yes 

Enter key password for <tomcat> 
    (RETURN if same as keystore password):

At the end of this operation, we’ll get a keystore containing a brand new SSL certificate.

Verify the keystore content

To check the content of the keystore following the JKS format, we can use keytool again:

keytool -list -v -keystore keystore.jks

To test the content of a keystore following the PKCS12 format:

keytool -list -v -storetype pkcs12 -keystore keystore.p12

Convert a JKS keystore into PKCS12

Should we have already a JKS keystore, we have the option to migrate it to PKCS12; keytool has a convenient command for that:

keytool -importkeystore -srckeystore keystore.jks -destkeystore keystore.p12 -deststoretype pkcs12

1b. Use an existing SSL certificate

In case we have already got an SSL certificate, for example, one issued by Let’s Encrypt, we can import it into a keystore and use it to enable HTTPS in a Spring Boot application.

We can use keytool to import our certificate in a new keystore.

keytool -import -alias tomcat -file myCertificate.crt -keystore keystore.p12 -storepass password

To get more information about the keystore and its format, please refer to the previous section.

2. Enable HTTPS in Spring Boot

Whether our keystore contains a self-signed certificate or one issued by a trusted Certificate Authority, we can now set up Spring Boot to accept requests over HTTPS instead of HTTP by using that certificate.

The first thing to do is placing the keystore file inside the Spring Boot project. We want to put it in the resources folder or the root folder.

Then, we configure the server to use our brand new keystore and enable https. Let’s go through the steps both for Spring Boot 1 and Spring Boot 2.

Enable HTTPS in Spring Boot 1

Let’s open our application.properties file (or application.yml) and define the following properties:

server.port=8443

server.ssl.key-store-type=PKCS12
server.ssl.key-store=classpath:keystore.p12
server.ssl.key-store-password=password
server.ssl.key-alias=tomcat

security.require-ssl=true
application.properties (Spring Boot 1)

Enable HTTPS in Spring Boot 2

To enable HTTPS for our Spring Boot 2 application, let’s open our application.yml file (or application.properties) and define the following properties:

server:
  ssl:
    key-store: classpath:keystore.p12
    key-store-password: password
    key-store-type: pkcs12
    key-alias: tomcat
    key-password: password
  port: 8443
application.yml (Spring Boot 2)

Configuring SSL in Spring Boot

Let’s have a closer look at the SSL configuration we have just defined in our Spring Boot application properties.

  • server.port: the port on which the server is listening. We have used 8443 rather than the default 8080 port.
  • server.ssl.key-store: the path to the key store that contains the SSL certificate. In our example, we want Spring Boot to look for it in the classpath.
  • server.ssl.key-store-password: the password used to access the key store.
  • server.ssl.key-store-type: the type of the key store (JKS or PKCS12).
  • server.ssl.key-alias: the alias that identifies the key in the key store.
  • server.ssl.key-password: the password used to access the key in the key store.

Configure Spring Security to require HTTPS requests

When using Spring Security, we can configure it to require automatically block any request coming from a non-secure HTTP channel.

In a Spring Boot 1 application, we can achieve that by setting the security.require-ssl property to true, without explicitly touching our Spring Security configuration class.

To achieve the same result in a Spring Boot 2 application, we need to extend the WebSecurityConfigurerAdapter class, since the security.require-ssl property has been deprecated.

@EnableWebSecurity
public class SecurityConfig extends WebSecurityConfigurerAdapter {

    @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
            .requiresChannel()
            .anyRequest()
            .requiresSecure();
    }
}
SecurityConfig.java (Spring Boot 2)

For more information about how to configure SSL in Spring Boot, you can have a look at the Reference Guide. If you want to find out which properties are available to configure SSL, you can refer to the definition in the code-base.

Congratulations! You have successfully enabled HTTPS in your Spring Boot application! Give it a try: run the application, open your browser and check if everything works as it should.

3. Redirect HTTP requests to HTTPS

Now that we have enabled HTTPS in our Spring Boot application and blocked any HTTP request, we want to redirect all traffic to HTTPS.

Spring allows defining just one network connector in application.properties (or application.yml). Since we have used it for HTTPS, we have to set the HTTP connector programmatically for our Tomcat web server.

The implementations for Spring Boot 1 and Spring Boot 2 are almost the same. The only difference is that some classes for server configuration have been renamed in Spring Boot 2.

Configuring Tomcat for Spring Boot 1

@Configuration
public class ServerConfig {
  
  @Bean
  public EmbeddedServletContainerFactory servletContainer() {
    TomcatEmbeddedServletContainerFactory tomcat = new TomcatEmbeddedServletContainerFactory() {
      @Override
      protected void postProcessContext(Context context) {
        SecurityConstraint securityConstraint = new SecurityConstraint();
        securityConstraint.setUserConstraint("CONFIDENTIAL");
        SecurityCollection collection = new SecurityCollection();
        collection.addPattern("/*");
        securityConstraint.addCollection(collection);
        context.addConstraint(securityConstraint);
      }
    };
    tomcat.addAdditionalTomcatConnectors(getHttpConnector());
    return tomcat;
  }
  
  private Connector getHttpConnector() {
    Connector connector = new Connector("org.apache.coyote.http11.Http11NioProtocol");
    connector.setScheme("http");
    connector.setPort(8080);
    connector.setSecure(false);
    connector.setRedirectPort(8443);
    return connector;
  }
}
ServerConfig.java (Spring Boot 1)

Configuring Tomcat for Spring Boot 2

@Configuration
public class ServerConfig {

    @Bean
    public ServletWebServerFactory servletContainer() {
        TomcatServletWebServerFactory tomcat = new TomcatServletWebServerFactory() {
            @Override
            protected void postProcessContext(Context context) {
                SecurityConstraint securityConstraint = new SecurityConstraint();
                securityConstraint.setUserConstraint("CONFIDENTIAL");
                SecurityCollection collection = new SecurityCollection();
                collection.addPattern("/*");
                securityConstraint.addCollection(collection);
                context.addConstraint(securityConstraint);
            }
        };
        tomcat.addAdditionalTomcatConnectors(getHttpConnector());
        return tomcat;
    }

    private Connector getHttpConnector() {
        Connector connector = new Connector(TomcatServletWebServerFactory.DEFAULT_PROTOCOL);
        connector.setScheme("http");
        connector.setPort(8080);
        connector.setSecure(false);
        connector.setRedirectPort(8443);
        return connector;
    }
}
ServerConfig.java (Spring Boot 2)

4. Distribute the SSL certificate to clients

When using a self-signed SSL certificate, our browser won’t trust our application and will warn the user that it’s not secure. And that’ll be the same with any other client.

It’s possible to make a client trust our application by providing it with our certificate.

Extract an SSL certificate from a keystore

We have stored our certificate inside a keystore, so we need to extract it. Again, keytool supports us very well:

keytool -export -keystore keystore.jks -alias tomcat -file myCertificate.crt

The keystore can be in JKS or PKCS12 format. During the execution of this command, keytool will ask us for the keystore password that we set at the beginning of this tutorial (the extremely secure password).

Now we can import our certificate into our client. Later, we’ll see how to import the certificate into the JRE in case we need it to trust our application.

Make a browser trust an SSL certificate

When using a keystore in the industry-standard PKCS12 format, we should be able to use it directly without extracting the certificate.

I suggest you check the official guide on how to import a PKCS12 file into your specific client. On macOS, for example, we can directly import a certificate into the Keychain Access (which browsers like Safari, Chrome and Opera rely on to manage certificates).

If deploying the application on localhost, we may need to do a further step from our browser: enabling insecure connections with localhost.

In Firefox, we are shown an alert message. To access the application, we need to explicitly define an exception for it and make Firefox trust the certificate.

In Chrome, we can write the following URL in the search bar: chrome://flags/#allow-insecure-localhost and activate the relative option.

Import an SSL certificate inside the JRE keystore

To make the JRE trust our certificate, we need to import it inside cacerts: the JRE trust store in charge of holding all certificates that can be trusted.

First, we need to know the path to our JDK home. A quick way to find it, if we are using Eclipse or STS as our IDE, is by going to Preferences > Java > Installed JREs. If using IntelliJ IDEA, we can access this information by going to Project Structure > SDKs and look at the value of the JDK home path field.

On macOS, it could be something like /Library/Java/JavaVirtualMachines/adoptopenjdk-8.jdk/Contents/Home. In the following, we’ll refer to this location by using the placeholder $JDK_HOME.

Then, from our Terminal prompt, let’s insert the following command (we might need to run it with administrator privileges by prefixing it with sudo):

keytool -importcert -file myCertificate.crt -alias tomcat -keystore $JDK_HOME/jre/lib/security/cacerts

We’ll be asked to input the JRE keystore password. If you have never changed it, it should be the default one: changeit or changeme, depending on the operating system. Finally, keytool will ask if you want to trust this certificate: let’s say yes.

If everything went right, we’d see the message Certificate was added to keystore. Great!

Conclusion

In this tutorial, we have seen how to generate a self-signed SSL certificate, how to import an existing certificate into a keystore, how to use it to enable HTTPS inside a Spring Boot application, how to redirect HTTP to HTTPS and how to extract and distribute the certificate to clients.

On GitHub, you can find the source code for the application we have built in this tutorial.

If you want to protect the access to some resources of your application, consider using Keycloak for the authentication and authorization of the users visiting your Spring Boot or Spring Security application.