In the default installation of PostgreSQL, it allows all users to connect to all other databases. This is not good for a shared environment. I want to make it so that user A has access to / can view only database A, and nothing else.

Here are what I did to set the privileges correctly:

1. Only allow the sameuser connection in pg_hba.conf

In pg_hba.conf file, instead of

host    all         all         127.0.0.1/32          md5

Set

host    sameuser         all         127.0.0.1/32          md5
host       all                postgres     127.0.0.1/32          md5

This allows all users on the localhost (connecting through TCP/IP) to be able to only connect to the database of the same name. postgres superuser can still connect to all databases.

While this is good enough, this only restricts the connectivity of the users - so if I decide to allow other connection methods, I need to remember to set the entries correctly. This seems somewhat scary, so I also did the next two.

2. Remove privileges from the databases themselves

By default, all users (PUBLIC) has at least CONNECT privilege assigned to all new databases (!) To see this, I did the following:

postgres=# select d.datname, d.datacl from postgres.pg_catalog.pg_database d;
 datname     |               datacl
----------------+-------------------------------------
postgres       |
dl_test        |
dl             |
template1      | {=c/postgres,postgres=CTc/postgres}
template0      | {=c/postgres,postgres=CTc/postgres}

(In the future, "\l+" command should suffice - http://archives.postgresql.org/pgsql-committers/2008-03/msg00552.php - but my pgsql doesn't seem to have this patch yet.) Blank datacl apparently means everyone can connect to it. So I need to revoke the privleges

postgres=# revoke all on database dl_test from PUBLIC;
REVOKE
postgres=# select d.datname, d.datacl from postgres.pg_catalog.pg_database d;
 datname     |               datacl
----------------+-------------------------------------

dl_test        | {dl=CTc/dl}

.. so PostgreSQL is smart enough to only leave out privileges for the owner user. Nice :)

After doing this to all of the databases, only the database owner would have permission to see her own database.

However, this still allows listing of the database names. In phpPgAdmin, this is worked around by setting 'owned_only' option. I haven't found a way to NOT be able to see this in the DB itself...

3. Change the permission of template1 database

I ran the same REVOKE command against template1 database. When a new database is created, contents of template1 is copied; so we don't have to do step 2 again after this.

皆もすなるブログといふものを、私もしてみむとてするなり。

So after years of silence, I'm finally going to try blogging. I don't know how long it will last - I've never had the tenacity to keep diaries and such - but I will see!  Like most blogs, this will be mostly for my own record keeping, but maybe someday I will write something worthwhile enough for others to read.  Who knows.

Anyway, the reason why I'm doing this is that I have been inspired after reading some blogs.  I think one of the best way to keep the motivation going is to read the things passionate people write.  Through writing, passion permeates.  I just need to remind myself to read and be inspired when I feel down.  I feel inspired to at least keep a record of myself at this point in time, so perhaps the I of tomorrow, I of the day after tomorrow and I of next year might be inspired to write as well.  I want to get this cycle going. Perhaps.

Today I'm struggling to start the Statement of Purpose for the graduate school application.  I must finish it this weekend.  This requires a lot of soul searching...  this is forcing me to really identify why the heck I want to do this.  That really IS the question.

My first post... :)