Loading
Notes
Study Reminders
Support
Text Version

PostgreSQL Databases

Set your study reminders

We will email you at these times to remind you to study.
  • Monday

    -

    7am

    +

    Tuesday

    -

    7am

    +

    Wednesday

    -

    7am

    +

    Thursday

    -

    7am

    +

    Friday

    -

    7am

    +

    Saturday

    -

    7am

    +

    Sunday

    -

    7am

    +

In this section we're going to focus on postgresql we're going to talk about postgresql architecture as well as how to compromise postgresql and how to secure the platform so first let's dive into the architecture so the architecture of postgresql includes the client a component called the postmaster which is kind of like the listener was when we talked about the oracle database and then you have the backend we have configuration files and then the actual data files that make up the database itself as well as the log files that are associated with it now the default port for postgresql ql is 5432 now that doesn't mean that every postgresql database will be listening on that port but it is the default port so it's the one to check first so i did a quick telnet you can see that we are indeed listening on that port i do have an installation of postgresql already in place as a matter of fact it's in place because it's serving as the database for my openvas installation openvas just as a side note is a open source vulnerability scanning tool and it happens to use postgresql all right so we're going to navigate through
some of the directory structures here and you look through the base folder these are actually database logs database files themselves and we look through other structures we can see the actual configuration files now the configuration files are actually stored in the etsy directory while the database files etc at least on ubuntu and the ubuntu installation are in var lib postgresql and then the version number so let's navigate over to the etsy folder and let's take a look at the configuration files so i remembered you know earlier we said okay hey here are the components that make up the database essentially that's the configuration files the database files and the listeners and the client so the configuration files are here under etsy postgresql slash version slash main you can see we've got postgres control postgres hba ident postgresql.com and we're going to walk through all of these in detail as we go throughout this section but we went ahead and i'm going to open up postgresql.conf and you can see that there are references to other files and configuration files and one of them is hba the postgres underscore hba which is the host-based authentication file and that's where we'l configure authentication settings but you notice we just passed through some of the configuration and ssl settings and so to configure an ssl enable postgresql part of that configuration will take place here in the postgresql.conf file now in addition to that for ssl we'll need to configure certificates so here's another look at the conf files there's also an ident.conf and then we have startup configuration file but let's look in the host based authentication file so this is where a lot of our security setup takes place and this is where we set up security for the listener or in postgresql terminology the postmaster so you can see that there's several different types of authentication you have md5 we have peer-based authentication host-based authentication and notice that you can set up various parameters in terms of network addresses individual ips and subnets okay so when it comes to
authentication methods there are eight authentication methods that are supported by postgresql and the first one is trust and essentially that means there's no security we trust the connection a reject would be to reject the connection outright ident and what that means is that we're mapping authentication to either an os user or ldap we'll talk about that password based authentication which is clear text by default by the way and crypt password with assault md5 is salt and username combination you can use kerberos as well to authenticate to postgresql so what i'm doing here is i'm going to go in and we're going to take a look at the base installation now i've opened up the postgres hba the host-based authentication file and you can see the options up top that i just scrolled through and what i'd like to do is as we go through the lesson modify the authentication mechanism and change it to md5 so any clients that try to authenticate remotely or even locally for that matter
should be required to authenticate with a password and in addition to that that password will be
hashed right because if we just chose the password option yes we would be prompted for a password
yes there would be a username and password configured for a listener connection but that would be in clear text so now that we've enabled the password for the post master slash listener let's go ahead and try to authenticate so psql is the command that we'll use to authenticate at the command line to obtain our postgresql interface all right so you can see that we authenticated and we were prompted for a password and we are at a postgres prompt so when you install postgresql by default there is isn't really a database other than some of the system catalogs that exist so the create db command will allow us to create a database and you need to make sure of course that you have the correct permissions to do so okay so i've created a database called security test that doesn't necessarily mean that it's populated with any tables yet so i've run a script to go ahead and populate that we need some sample tables to test with all right so if we do a backslash d plus that shows us that we have some schema components we have a list of relations a list of tables we have a city table country table we can look at the structure of an individual table with backslash d the name of the table all right so we run a select you can see the contents of some of the tables so we've got our basic lab environment set up and we've set up some basic security on the listener but we really haven't set
up any security yet for the database itself and we'll get into that momentarily you can see the actual databases that exist there are some database templates there's postgres itself and then the security test database that we created using the create db command so as a side note postgresql does not use operating system users to authenticate it uses user names and passwords to authenticate which are stored in the pg underscore shadow system catalog table and only the super user for postgresql has access to that table so obviously you want to protect the super user for postgresql so next i'm going to issue a query that shows us username and password information for the msf and postgresql users and really the point here is just to show you that they are md5 hashed so postgresql in
addition to user accounts also supports groups and roles so we're going to issue a query here to
get a list of groups next i want to create a new user and i just want to show you that it is possible to create a user account with an unencrypted password but you kind of have to go out of your way to do it it's not a default configuration and i will say that postgresql is an open source relational database that is designed with security in mind and a lot of the default security settings are in place right out of the gate so we created the user and you see we can see the password in clear text which of course is a bad idea so as we've mentioned with other databases postgresql also supports stored procedures and stored procedures often are an attack vector that attackers and malware can utilize especially default system stored procedures to potentially compromise a database so here are the languages that are supported for stored procedures internal we have c sql and pl sql that are supported so now that we've covered some of the architecture of pl sql we've set up our lab environment we have some sample data next we're going to move on to attacking the postgresql database so when we talk about attacking postgresql installations there are really six steps to the process or six considerations in terms of securing and or attacking the environment one is if i'm an attacker i need to find the front door and that of course is the listener we can issue network-based attacks and spoofing and several others which I'll walk through so here I've opened up nmap i'm going to run an nmap scan on this network segment so you can see i've run a scan on this class c network and we're not finding any postgresql instances which makes sense because there's only one postgresql instance in my lab environment and it happens to be the machine that we're running on right now so what i'll need to do is run a port scan on my local machine now sometimes you'll see a message coming back that there is a filtered protocol that doesn't necessarily mean that there's a listener for that protocol in place it just means that there's a firewall of some sort that's filtering it so now let's run the scan on our local machine okay so as you know we have postgresql running on this machine so it does make sense that it found the open port and this is one of the first things you want to look for i'll tell that as well
and verify yes it is indeed listening so as i mentioned in postgresql terms the postmaster is much like the tns listener for the oracle database it's a traffic cop and a security gateway to some extent in terms of managing the establishment of connections from clients now the other type of attack aside from just trying to compromise the listener itself would be a straight up network based attack and this can
be at other levels of the tcp model such as level two level three and this assumes that you have access to the local lan segment so as an attacker if you've compromised a machine you can pivot from that machine and attack other systems and these other systems can use let's say they compromised a server they can use that server as a launch point for a network-based attack against a postgresql server so if you had indeed let's say compromised a server that's on the same lan as a postgresql server then you could implement a tool such as wireshark on that local segment and start capturing data and all postgresql data by default is in cleartext it's not encrypted with tls ssl by default there are certain steps you need to go through which we'll cover subsequently but just keep in mind that that information is in clear text now if you have configured host-based authentication within the postgresql hba conf file then the ip address that has been set or the ips that have been set to allow well if you happen to be on one of those machines and you've compromised one of those machines well you have access to the database by virtue of the fact that you're coming from that i p address but keep in mind that i p address i p addresses can be spooped spoofed and mac addresses can be spoofed as well so i'm going to navigate back into the pghba.com file so as i mentioned if you have a host-based authentication setup it would be by ip address there are other mechanisms and parameters that we can configure to enhance security of the connections so arp spoof is a freely available tool that will allow you to spoof your mac address so if you had compromise a machine that's on the lan positioned in the subnet adjacent to or the same subnet as the postgresql database then we can spoof our layer two addresses and subsequently potentially get access to the machine
so some of the other attack vectors outside of spoofing or network-based attacks or enumeration
are to find the bugs and vulnerabilities within the database and cve.mitre.org is a good spot to look for
reported vulnerabilities so there's the cve the common vulnerability and exposures database and you can search there specific to the database type and the version of the database and we can potentially
find versions that have vulnerabilities associated with them and you can look this up also by platform so in in our case we're running postgresql 9.3 on ubuntu 14.04 so just as i mentioned when we talked about this covering oracle databases the same holds true here which is we're looking for vulnerabilities in this version of the database or later now in addition to finding the vulnerabilities let's say we find a vulnerability in our version well as an attacker we would want to find an exploit associated with that vulnerability so it's one thing to know that a machine is potentially vulnerable the next step would be finding an actual exploit that can be implemented perhaps in metasploit payload so we can search the exploit db database by cve and we can also search a little more generically by looking at the the type of database and the version of the database and sometimes you'll find that when you do a search on a particular cve that there isn't an exploit for it in the exploit database that doesn't mean that an exploit doesn't exist all it means is that that exploit doesn't exist in this database so digging around on the dark web might turn up different results so if we do a search for postgresql and we go through this captcha process real quickly here then we can see if there are any published vulnerabilities excuse me published exploits for postgresql and there are indeed some but notably there aren't really a whole lot and i think that's a testament to the security of this platform so as an attacker another thing that we would look for are postgresql installations or databases that have insecure configurations and one of the most important things you can do other than patching the database which you know we harp on that consistently and it is important the other important thing that we can do is to ensure that the configuration has been hardened and so part of that involves the installation of the database you may want to make sure that you do not install the database as root or more specifically that the database does not run as root because if the database is running as the root user the database is compromised the host is compromised and vice versa so if you're running as root someone gets root access to the box they'll automatically have access to the
database and that works in the other direction as well so now that we've talked about setting up the lab for postgresql we've talked about some of the architecture we've also talked through the attack process slash audit process next we want to walk through the security and hardening process for postgresql and you know one of the simplest things you can do aside from securing the listener is to encrypt the traffic to the postgresql server and so we would do this through the postgresql.conf file and you would also need to generate a certificate to be used to facilitate the encryption.