[rt-users] postgres permission denied

[Thomas Krichel]

  Hi RT gang!

  rt-users-request at lists.bestpractical.com writes

> Here are a few searchable places, listed in order of
> likely usefulness:
> 
>   http://wiki.bestpractical.com         - community wiki
>   http://gossamer-threads.com/lists/rt/ - searchable list archives
>   http://google.com                     - "RT [MostUniqueErrMsg]"

  Searched those, did not fine anything...
 
  I recently upgraded our RT from version 3.8. 

  Here is what I have now.

root at rt:~# aptitude show postgresql | grep Version
Version: 9.1+134wheezy4

root at rt:~# aptitude show libperl5.14 | grep Version
Version: 5.14.2-21+deb7u1

root at rt:~# aptitude show request-tracker4 | grep Version
Version: 4.0.7-5+deb7u2

  RT appears to be running just fine but I get odd permissions denied
  denied problems to the datasbase in the log. I did

postgres at rt:~$ psql -U postgres
psql (9.1.11)
Type "help" for help.

postgres=# grant all on database rtdb to rtuser;
GRANT
postgres=# ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO rtuser;
ALTER DEFAULT PRIVILEGES
postgres=# \q
postgres at rt:~$

  but the troubles keep coming

Jan 29 10:10:13 rt RT: DBD::Pg::st execute failed: ERROR:  permission 
 denied for relation topics at /usr/share/perl5/DBIx/SearchBuilder/Handle.pm
  line 587. (/usr/share/perl5/DBIx/SearchBuilder/Handle.pm:587)

Jan 29 10:10:13 rt RT: RT::Handle=HASH(0x7fb7b85d5158) couldn't
 execute the query 'SELECT * FROM Topics WHERE LOWER(ObjectType) =
 LOWER(?) AND LOWER(Name) = LOWER(?) AND ObjectId = ? AND Parent = ?'
 at /usr/share/perl5/DBIx/SearchBuilder/Handle.pm line
 600#012#011DBIx::SearchBuilder::Handle::SimpleQuery('RT::Handle=HASH(0x7fb7b85d5158)',
 'SELECT * FROM Topics WHERE LOWER(ObjectType) = LOWER(?) AND ...',
 'RT::System', 'Queues', 1, 0) called at
 /usr/share/perl5/DBIx/SearchBuilder/Record.pm line
 1206#012#011DBIx::SearchBuilder::Record::_LoadFromSQL('RT::Topic=HASH(0x7fb7ba247b80)',
 'SELECT * FROM Topics WHERE LOWER(ObjectType) = LOWER(?) AND ...',
 'RT::System', 'Queues', 1, 0) called at
 /usr/share/perl5/DBIx/SearchBuilder/Record.pm line
 1125#012#011DBIx::SearchBuilder::Record::LoadByCols('RT::Topic=HASH(0x7fb7ba247b80)',
 'Parent', 0, 'ObjectId', 1, 'LOWER(Name)', 'HASH(0x7fb7ba35a768)',
 'LOWER(ObjectType)', 'HASH(0x7fb7ba28bc78)', ...) called at
 /usr/share/perl5/DBIx/SearchBuilder/Record/Cachable.pm line
 120#012#011DBIx::SearchBuilder::Record::Cachable::LoadByCols('RT::Topic=HASH(0x7fb7ba247b80)',
 'LOWER(ObjectType)', 'HASH(0x7fb7ba28bc78)', 'LOWER(Name)',
 'HASH(0x7fb7ba35a768)', 'ObjectId', 1, 'Parent', 0, ...) called at
 /usr/share/request-tracker4/lib/RT/Record.pm line
 393#012#011RT::Record::LoadByCols('RT::Topic=HASH(0x7fb7ba247b80)' ,
 'Name', 'Queues', 'Parent', 0, 'ObjectType', 'RT::System',
 'ObjectId', 1, ...) called at
 /usr/share/request-tracker4/html/Articles/Elements/BeforeMessageBox
 line 206#012#011HTML::Mason::Commands::__ANON__('Action', 'Comment',
 'id', 2205, 'DefaultStatus', 'resolved') called at
 /usr/share/perl5/HTML/Mason/Component.pm line
 135#012#011HTML::Mason::Component::run('HTML::Mason::Component::FileBased=HASH(0x7fb7b9610c00)',
 'Action', 'Comment', 'id', 2205, 'DefaultStatus', 'resolved') called
 at /usr/share/perl5/HTML/Mason/Request.pm line 1302#012#011eval {...}
 called at /usr/share/perl5/HTML/Mason/Request.pm line
 1292#012#011HTML::Mason::Request::comp(undef, undef, 'Action',
 'Comment

  I did try to reset the permissions. As suggested in the doc, I made
  sure that postgres can read the configuration file. I did it this way
  
root at rt:~# ls -l /etc/request-tracker4/RT_SiteConfig.pm
-rw-r----- 1 root www-data 2311 Dec 28 04:30 /etc/request-tracker4/RT_SiteConfig.pm

root at rt:~# grep postgres /etc/group | grep www-data
www-data:x:33:postgres

  Indeed

postgres at rt:/root$ cat  /etc/request-tracker4/RT_SiteConfig.pm | head -1
# This file was generated by running "update-rt-siteconfig".

  Now trying to run 

root at rt:~# sudo -u postgres /usr/sbin/rt-setup-database --action acl
In order to create or update your RT database, this script needs to connect to your  Pg instance on localhost as rtuser
Please specify that user's database password below. If the user has no database
password, just press return.

Password: 
Working with:
Type:   Pg
Host:   localhost
Name:   rtdb
User:   rtuser
DBA:    rtuser
Now inserting database ACLs.
DBD::Pg::st execute failed: ERROR:  permission denied for sequence classes_id_seq at /usr/share/request-tracker4/lib/RT/Handle.pm line 439, <STDIN> line 1.
DBD::Pg::st execute failed: ERROR:  permission denied for sequence classes_id_seq at /usr/share/request-tracker4/lib/RT/Handle.pm line 439, <STDIN> line 1.

  At the password prompt, I entered the password set in

Set($DatabasePassword , '...');

  And the problem persists. I can reproduce it by resolving a test 
  ticket.

  Any hint as what can I try next?

-- 

  Cheers,

  Thomas Krichel                  http://openlib.org/home/krichel
                                              skype:thomaskrichel