Chris Weldon’s Blog

I am setting up monitoring for my PostgreSQL database server, and ran across a cool way to get around specifying the username/password via the command-line every time the checks are run: pg_service.conf. Unfortunately, there is very little documentation on the config file. As best as I can figure out, if you are connecting to a remote host, your definitions should look as follows:

[service.name.here]
dbname=db.name.here
user=user.name.here
host=host.name.here
password=password.here

On a Gentoo Linux server with PostgreSQL 8.4 installed, this file will need to be placed at /etc/postgresql-8.4/pg_service.conf. Then, to reference it, simply export the PGSERVICE environment variable with the value of the name of the service in brackets.

*nix, Systems Administration postgresql, Systems Administration

As I’m trying to finish up one of my consulting projects (and enhance other active projects), I decided to take a nice long hard look a PostgreSQL permissions - the correct way of doing it. PostgreSQL applies the commonly accepted principle of assigning permissions to resources. They’re moving away from the concept of users and groups and more to roles. As of PostgreSQL 8.3, the primary command to create new users OR groups is CREATE ROLE. The existing CREATE USER / CREATE GROUP commands still exist, but are becoming non-existent in hopes of replacing the user / group permissions model with a strictly role-based model.

In an attempt to move my projects towards this model, I ended up running into several problems.
Read more…

SQL postgresql, sql