[development] Some introductory PostgreSQL pointers (was Re: Do not let postgresql hold back great patches)

[Bill Moran]

"Karoly Negyesi" <karoly at negyesi.net> wrote:
>
> Huh, nice. OK, I shall admit that I already have installed
> postgresql on Tank but I am so unsure about it... anyone could
> help the process with a tutorial which makes sure the database
> can accept connections from localhost and only there?

Once it's installed, there's another step called "initializing
the cluster" ... which is basically creating an empty database.
Some packaging systems might do this for you.  If PostgreSQL
starts, then it's done (it won't implicitly create an empty
system).

PG is designed to allow multiple version on a single machine.
I don't know how many people actually do this, but it's a godsend
when you need it.  The point is that each "cluster" (as it's called)
is a directory with all the configuration and database files for
that running instance -- usually /usr/local/pgsql or something like
like that.  do a "find / -name postgresql.conf" and you'll find
it.

That's the first file you want to look at.  There's a lot of
esoteric tuning options, but the obvious one near the beginning
is called "listen_address" which defaults to "localhost" only.
(Although different package systems may install a custom config
file, so you should check)

> Also, add
> a "drupal" user with "drupal" password which can do... anything
> whatever it means. This process is clear on MySQL and after two
> years with occassionally working with pgsql, I could not figure
> out its grant system. I am daft, I know.

I doubt you're daft, but I'm confused as to where the problem is.
PostgreSQL's grant system is simpler than MySQL's in my
experience.

The only thing that might be complex is that PG versions greater
than 8 have unified users and groups into a single concept called
a "role".  If you just think of a "user" as a role with login
permissions, you'll be OK.

If you connect with the psql command, you can issue "\du" to get
a list of users.  I'm guessing by default that there's only the
default superuser on your system.  To create a new one:
CREATE ROLE drupal WITH LOGIN;
Or, to create it with superuser permissions:
CREATE ROLE drupal WITH LOGIN SUPERUSER;
You can also tweak roles after creation:
ALTER ROLE drupal PASSWORD = 'somepassword';
Then create a database:
CREATE DATABASE drupal;
And tweak settings as you like:
GRANT all ON DATABASE drupal TO drupal;

PG has a two-pronged approach to security.  The role system which
I just described is actually the second layer.  The first layer
is called "host-based authentication".  These two layers combined
basically equate to MySQL's role system.

In the same directory as the postgresql.conf file, you'll find
a pg_hba.conf file.  In this are lines that control what users
from what hosts can connect to what databases.  For your
purposes, you probably want lines like:

host all all 127.0.0.1/32 trust
local all all             trust

The first means that any user connecting via the loopback is
allowed to connect without a password.  The second means that
any user can connect to any database via the local unix socket
without a password.

You can get more specific if you want:
# Allow anyone from local network to connect to database myaccount
# as user drupal, as long as they know the password
host drupal myaccount 192.168.5.0/24 password
# Allow anyone to connect to database drupal as any user from a
# single remote machine without a password
host all drupal 172.16.56.21/32 trust

psql is your friend.  Some useful commands for seeing what your
database looks like:
# Show all roles (The "u" is legacy from when they were users)
\du
# Show all databases
\l
# Show the table structure of table "users"
\d users
# Show the permissions on all objects in the database
\dp
# Show the built-in help
\?

If you get stuck or have questions, ask.

-- 
Bill Moran
http://www.potentialtech.com