T. Plancon wrote:

Hello,

I'm setting up bacula 2.4.2 on a CentOS 5 system using postgresql as the
db. I've used the scripts in /usr/lib/bacula, running as user
'postgres'

Whoops. You should create the tables under user `bacula' if that's the user ID your director runs under.

create_postgresql_database, make_postgresql_tables,
grant_postgresql_privileges in that order. An initial backup ran fine,
but I got this error when doing the catalog backup:

07-Apr 14:00 p-plover-dir JobId 1: BeforeJob: run command "/usr/lib/bacula/make_catalog_backup bacula bacula"
07-Apr 14:00 p-plover-dir JobId 1: BeforeJob: pg_dump: SQL command failed
07-Apr 14:00 p-plover-dir JobId 1: BeforeJob: pg_dump: Error message from server: ERROR:  permission denied for relation contact_userid_seq

If you connect to the database `bacula' as user `postgres' (usually:
    sudo su postgres -c "psql bacula"
) and run:
    \dt+
you'll probably find that the tables are owned by a user other than `bacula' - in your case, `postgres'.

I landed up with a database where Bacula had been GRANTed SELECT, INSERT, UPDATE and DELETE rights, but didn't have the right to LOCK tables, so pg_dump would fail when run as user `bacula' by the director. You probably have related rights issues.

To resolve this, just:

    ALTER TABLE tablename OWNER TO bacula;

after connecting to the database as user `postgres' (since that'll be the current owner in your case).

Unfortunately, Pg offers no way to recursively change ownership. Either script it, or just enter the tables by hand; there aren't that many.

IMO, the make_postgreql_tables SQL script really needs to `\c - bacula' (or "\c - ${bacula_user}" ... whatever) to ensure that it creates the tables with appropriate ownership.

--
Craig Ringer