Re: [Bacula-users] PG database error

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

------------------------------------------------------------------------------
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-com
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users