On Dec 23, 2011, at 6:26 PM, David Newman wrote:
> On 12/23/11 2:38 PM, Dan Langille wrote:
>>
>> On Dec 23, 2011, at 5:35 PM, David Newman wrote:
>>
>>> On 12/23/11 2:21 PM, Dan Langille wrote:
>>>> On Dec 20, 2011, at 1:19 PM, David Newman wrote:
>>>>
>>>>> bacula 5.2.2, FreeBSD 8.2-RELEASE
>>>>>
>>>>> After upgrading bacula-server from 5.0.3 to 5.2.2 using FreeBSD ports
>>>>> and updating the (PostgreSQL) bacula database, all jobs run fine except
>>>>> for the final one on the bacula server, the one that dumps the catalog
>>>>> before making a backup.
>>>>>
>>>>> The error looks like this:
>>>>>
>>>>> 20-Dec 00:08 nye-dir JobId 8183: shell command: run BeforeJob
>>>>> "/home/bacula/bin/make_catalog_backup bacula bacula"
>>>>> 20-Dec 00:08 nye-dir JobId 8183: BeforeJob: pg_dump: SQL command failed
>>>>> 20-Dec 00:08 nye-dir JobId 8183: BeforeJob: pg_dump: Error message from
>>>>> server: ERROR: permission denied for relation restore object
>>>>
>>>> This is the key line. The PostgresSQL user, with which the script is
>>>> connecting to
>>>> the database, does not have correct permissions on that table.
>>>>
>>>>> 20-Dec 00:08 nye-dir JobId 8183: BeforeJob: pg_dump: The command was:
>>>>> LOCK TABLE public.restoreobject IN ACCESS SHARE MODE
>>>>> 20-Dec 00:08 nye-dir JobId 8183: Error: Runscript: BeforeJob returned
>>>>> non-zero status=1. ERR=Child exited with code 1
>>>>>
>>>>> Running the same command manually as user pgsql also fails with the same
>>>>> permission denied error.
>>>>
>>>> If you connect to
>>>> the database using psql, you'll see something like this (I did the version
>>>> table)
>>>>
>>>> bacula=# \dp version
>>>> Access privileges
>>>> Schema | Name | Type | Access privileges | Column access
>>>> privileges
>>>> --------+---------+-------+-----------------------+--------------------------
>>>> public | version | table | bacula=arwdDxt/bacula |
>>>> : dan=arwdDxt/bacula
>>>> (1 row)
>>>>
>>>> bacula=#
>>>>
>>>> You need to grant permissions on the table appropriately. These commands
>>>> may be in the upgrade script… or you'll have to do them yourself. Now
>>>> that the
>>>> issue is known, others may be able to help.
>>>
>>> Thanks, Dan. In this case, it appears users pgsql and bacula have
>>> identical privileges:
>>>
>>> [dnewman@nye ~]$ sudo -u pgsql /usr/local/bin/psql bacula
>>> Welcome to psql 8.2.22, the PostgreSQL interactive terminal.
>>>
>>> Type: \copyright for distribution terms
>>> \h for help with SQL commands
>>> \? for help with psql commands
>>> \g or terminate with semicolon to execute query
>>> \q to quit
>>>
>>> bacula=# \dp version
>>> Access privileges for database "bacula"
>>> Schema | Name | Type | Access privileges
>>> --------+---------+-------+------------------------------------------
>>> public | version | table | {pgsql=arwdxt/pgsql,bacula=arwdxt/pgsql}
>>> (1 row)
>>
>> Now, compare the version table with the table causing the problem. Try
>>
>> \dp restore
>
> A little progress, still not fixed. In this case the table name was
> restoreobject. Orginally, \dp showed no access privileges for it. I
> fixed that:
>
> bacula=# \dp restoreobject
> Access privileges for database "bacula"
> Schema | Name | Type | Access privileges
> --------+---------------+-------+------------------------------------------
> public | restoreobject | table | {pgsql=arwdxt/pgsql,bacula=arwdxt/pgsql}
>
> But that dump command still bombs with a permissions error, even after
> adding user pgsql to the bacula group and granting write access to the
> group:
>
> [dnewman@nye ~]$ grep pgsql /etc/group
> bacula:*:910:dnewman,pgsql
>
> [dnewman@nye ~]$ sudo chmod g+w /usr/home/bacula
> /usr/home/bacula/working /usr/home/bacula/working/*
>
> [dnewman@nye ~]$ sudo -u pgsql /home/bacula/bin/make_catalog_backup
> bacula bacula
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: permission denied for
> relation restoreobject_restoreobjectid_seq
> pg_dump: The command was: SELECT sequence_name, last_value,
> increment_by, CASE WHEN increment_by > 0 AND max_value =
> 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value =
> -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN
> increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0
> AND min_value = -9223372036854775807 THEN NULL ELSE min_value END
> AS min_value, cache_value, is_cycled, is_called from
> restoreobject_restoreobjectid_seq
>
> At this point I'm unclear where the permissions problem exists.
Within PostgreSQL. The PostgreSQL user does not have permissions on that table…
This is not a Unix permissions issue.
>
> Thanks in advance for further clues.
>
> dn
>
>
>
>>
>> I am not using 5.2.2, so I did the version table as an example of what it
>> should look like.
>>
>>>
>>> bacula-# \l
>>> List of databases
>>> Name | Owner | Encoding
>>> -----------+--------+-----------
>>> bacula | bacula | SQL_ASCII
>>> postgres | pgsql | UTF8
>>> template0 | pgsql | UTF8
>>> template1 | pgsql | UTF8
>>> (4 rows)
>>>
>>> User bacula's shell is defined as /sbin/nologin, so I think it's user
>>> pgsql that's doing the work (at least it was prior to the upgrade). User
>>> bacula cannot launch psql nor can I su to that user because of the
>>> nologin setting.
>>>
>>> What permissions do I need to change to get this dump working?
>>>
>>> Thanks again!
>>>
>>> dn
>>>
>>>>
>>>>>
>>>>> I have restarted all bacula and postgresql daemons since the upgrade. I
>>>>> have not changed any permissions in the /home/bacula directory.
>>>>>
>>>>> Thanks in advance for troubleshooting clues.
>>>>>
>>>>> dn
>>>>>
>>>>>
>>>>> ------------------------------------------------------------------------------
>>>>> Write once. Port to many.
>>>>> Get the SDK and tools to simplify cross-platform app development. Create
>>>>> new or port existing apps to sell to consumers worldwide. Explore the
>>>>> Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
>>>>> http://p.sf.net/sfu/intel-appdev
>>>>> _______________________________________________
>>>>> Bacula-users mailing list
>>>>> Bacula-users@lists.sourceforge.net
>>>>> https://lists.sourceforge.net/lists/listinfo/bacula-users
>>>>
>>>
>>
>
--
Dan Langille - http://langille.org
------------------------------------------------------------------------------
Write once. Port to many.
Get the SDK and tools to simplify cross-platform app development. Create
new or port existing apps to sell to consumers worldwide. Explore the
Intel AppUpSM program developer opportunity. appdeveloper.intel.com/join
http://p.sf.net/sfu/intel-appdev
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
