eXpand yOur cReativity by Bhushan G Ahire

15Feb/101

Installing PostgreSQL on Snow Leopard 10.6

Installing PostgreSQL 8.3

First, you’ll need to install Xcode if you haven’t already. This is available on the Snow Leopard DVD in the Optional Installs directory.

Second, if you aren’t already using it, download Mac Ports for Snow Leopard and install it. Mac Ports has come a long way in the last few years and will make your life much easier.

Once those are installed, run the following command:
sudo port install postgresql83 postgresql83-server

Setup Your First Database

At the very end of the install it tells you how to setup your first database:

sudo mkdir -p /opt/local/var/db/postgresql83/defaultdb
sudo chown postgres:postgres /opt/local/var/db/postgresql83/defaultdb
sudo su postgres -c '/opt/local/lib/postgresql83/bin/initdb -D /opt/local/var/db/postgresql83/defaultdb'

You’ll also want to setup Postgres to auto-run as a server on start up.

sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql83-server.plist

If you want to start it right now, you can either reboot or do the following:

sudo su postgres -c '/opt/local/lib/postgresql83/bin/postgres -D /opt/local/var/db/postgresql83/defaultdb'

Make psql Available from the Command Line

The executable files for PostgreSQL get shoved into a non-standard place (just like MySQL), so you’ll need to edit the default profile.

sudo vi /etc/profile

You can also do this using sudo mate /etc/profile if you aren’t comfortable in VI.

The PATH= line needs to be changed to include the PostgreSQL bin directory.

Mine was PATH="/opt/local/bin:$PATH" and is now:

PATH="/opt/local/bin:/opt/local/sbin:/opt/local/lib/postgresql83/bin:$PATH"

If you open a new terminal window you can now type psql and it will find it.

Create a New User and Database

By default, PostgreSQL creates a postgres user for you. However, it’s not good practice to use the default and it’s a pain in the ass. Let’s just create a new database user to make it easier.

createuser --superuser macusername -U postgres

You need to change macusername to your mac username. This will make your life ALOT easier. Trust me here.

Next, create your database:

createdb my_database

Installing the PostgreSQL Ruby Gem

Unlike the MySQL driver, we don’t need to pass the ARCHFLAGS variable as 64 bit. PostgreSQL comes with both 32 and 64-bit versions. Yeah!

sudo gem install postgres-pr

Per Tom’s comment below, we should be using the native driver for better performance.

sudo env ARCHFLAGS="-arch x86_64" gem install pg

Configuring your Rails Application

Inside your Ruby on Rails application, open up config/database.yml and change your development adapter to be similar to the following:

development:
adapter: postgresql
database: defaultdb
username: defaultdb

You can change defaultdb to the name you need for your application.

Tagged as: , , 1 Comment
17Jun/090

Grant privileges to all tables in a database for postgresql

Grant privileges to all tables in a database (select, update, insert, delete)

Eg:( Creating a read-only user in postgres)

–Function to grant access(select,insert,update,delete) to users

CREATE FUNCTION pg_grant(TEXT, TEXT, TEXT, TEXT)
RETURNS integer AS '
DECLARE obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind in (''r'',''v'',''S'') AND
nspname = $4 AND
relname LIKE $3
LOOP
EXECUTE ''GRANT '' || $2 || '' ON '' || obj.relname || '' TO '' || $1;
num := num + 1;
END LOOP;
RETURN num;
END;
' LANGUAGE plpgsql SECURITY DEFINER;

–Function to revoke access(select,insert,update,delete) from users

CREATE FUNCTION pg_revoke(TEXT, TEXT, TEXT, TEXT)
RETURNS integer AS '
DECLARE obj record;
num integer;
BEGIN
num:=0;
FOR obj IN SELECT relname FROM pg_class c
JOIN pg_namespace ns ON (c.relnamespace = ns.oid) WHERE
relkind in (''r'',''v'',''S'') AND
nspname = $4 AND
relname LIKE $3
LOOP
EXECUTE ''REVOKE '' || $2 || '' ON '' || obj.relname || '' FROM '' || $1;
num := num + 1;
END LOOP;
RETURN num;
END;
' LANGUAGE plpgsql SECURITY DEFINER;

–Create users for your database

CREATE USER userreadonly WITH PASSWORD 'userr3ad0nly';
CREATE USER userall WITH PASSWORD 'usersh0pa11';

–Grant respective access to users

select pg_grant('userreadonly ','select','%','public');
select pg_grant('userall ','select,insert,update,delete','%','public');

You might need to create lang for plpgsql if you had not done so

createlang plpgsql yrdatabasename

Tagged as: , , No Comments
   

Bhushan G Ahire



Pages

Categories

Blogroll

Archive

Meta