Securing PostgreSQL

Install PostgreSQL

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

Upon installation, Postgres creates a Linux user called “postgres” which can be used to access the system. We can change to this user by typing:

sudo su - postgres

From here, we can connect to the system by typing:

psql

Notice how we can connect without a password. This is because Postgres has authenticated by username, which it assumes is secured.

Exit out of PostgreSQL and the postgres user by typing the following:

\q
exit

Disable Remote Connections

sudo vi /etc/postgresql/9.1/main/pg_hba.conf

Ensure the contents of the config file look something like the following:

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

The first two security lines specify “local” as the scope that they apply to. This means they are using Unix/Linux domain sockets.
The second two declarations are remote, but the hosts that they apply to (127.0.0.1/32 and ::1/128) are interfaces that specify the local machine.


What If You Need To Access the Databases Remotely?

To access PostgreSQL from a remote location, consider using SSH to connect to the database machine and then using a local connection to the database from there.
It is also possible to tunnel access to PostgreSQL through SSH so that the client machine can connect to the remote database as if it were local. Visit the PostgreSQL official documentation pages.

Use roles to lock down access to individual databases

Log into PostgreSQL:

sudo su - postgres
psql

To create a new role, type the following:

CREATE ROLE role_name WITH optional_permissions;

To see the permissions you can assign, type:

\h CREATE ROLE

You can alter the permissions of any role by typing:

ALTER ROLE role_name WITH optional_permissions;

List the current roles and their attributes by typing:

\du
List of roles
Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
hello     | Create DB                                      | {}
postgres  | Superuser, Create role, Create DB, Replication | {}
testuser  |                                                | {}

Create a new user and assign appropriate permissions for every new application that will be using PostgreSQL.

Installing MySQL gem on OSX 10.6

Hopefully this will be a time saver for anyone else who goes through the following pain.

I have a brand spanking new OSX 10.6 installation. I installed mysql 5.5.9-OSX10.6-X86 (because I thought everything was still i386 based…watch this space!) I installed RVM and installed ruby 1.92 under RVM control. I then do a ‘bundle install’ and it gripes about not having mysql2 gem installed. I do a manual install using the following command.


sudo gem install mysql2

and after a bit of crunching, we get to the following error:


Building native extensions.  This could take a while...
ERROR:  Error installing mysql2:
ERROR: Failed to build gem native extension.
1.9.1/ruby/ruby.h:108: error: size of array ‘ruby_check_sizeof_long’ is negative
/Users/peter/.rvm/rubies/ruby-1.9.2-p180/include/ruby-1.9.1/ruby/ruby.h:112: error: size of array ‘ruby_check_sizeof_voidp’ is negative
In file included from /Users/peter/.rvm/rubies/ruby-1.9.2-p180/include/ruby-1.9.1/ruby/intern.h:29,
             from /Users/peter/.rvm/rubies/ruby-1.9.2-p180/include/ruby-1.9.1/ruby/ruby.h:1327,
             from /Users/peter/.rvm/rubies/ruby-1.9.2-p180/include/ruby-1.9.1/ruby.h:32,
             from ./mysql2_ext.h:4,
             from client.c:1:
/Users/peter/.rvm/rubies/ruby-1.9.2-p180/include/ruby-1.9.1/ruby/st.h:69: error: size of array ‘st_check_for_sizeof_st_index_t’ is negative

The Solution

  • Uninstall your MySQL using the commands below

    sudo rm /usr/local/mysql \
    rm -rf /usr/local/mysql* \
    rm -rf /Library/StartupItems/MySQLCOM \
    rm -rf /Library/PreferencePanes/My* \
    rm -rf /Library/Receipts/mysql* \
    rm -rf /Library/Receipts/MySQL*

  • edit /etc/hostconfig and remove the line MYSQLCOM=-YES

  • Now go and download the 64 bit version of the MySQL package – get the dmg rather than the gzip file.

The rationale behind this is that your new beaut 10.6 is actually referencing 64 bit modules by default. How to prove this I do not know – I’m confused and still searching for the answer. If I do a uname, I get the following.


$ uname – a
Darwin MacBook-Air.local 10.6.0 Darwin Kernel Version 10.6.0: Wed Nov 10 18:13:17 PST 2010; root:xnu-1504.9.26~3/RELEASE_I386 i386

Now, if I’m not mistaken that’s an i386 at the end – go figure. If anyone has any further insights please let me know.

The next step is to download the gem for your ruby/rails use


sudo env ARCHFLAGS="-arch x86_64" gem install mysql2 -- \
--with-mysql-dir=/usr/local/mysql --with-mysql-lib=/usr/local/mysql/lib \
--with-mysql-include=/usr/local/mysql/include
  • The next error encountered is when I try to start the rails server using ‘rails s’

    /Users/peter/.rvm/gems/ruby-1.9.2-p180/gems/mysql2-0.2.6/lib/mysql2.rb:7:in `require’: dlopen(/Users/peter/.rvm/gems/ruby-1.9.2-p180/gems/mysql2-0.2.6/lib/mysql2/mysql2.bundle, 9): Library not loaded: libmysqlclient.16.dylib (LoadError)

  • The answer to this problem is to run the following command

    sudo install_name_tool -change libmysqlclient.16.dylib /usr/local/mysql/lib/libmysqlclient.16.dylib ~/.rvm/gems/ruby-1.9.2-p180/gems/mysql2-0.2.6/lib/mysql2/mysql2.bundle

Bear in mind, I’m running version 1.9.2-p180 fo ruby – you will need to change the command for whatever version you’re running.