Postgres permission denied

Hi RT gang!

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

Here are a few searchable places, listed in order of
likely usefulness:

http://wiki.bestpractical.com - community wiki
Carbon60: Cloud Consulting - Services and Solutions - 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@rt:~# aptitude show postgresql | grep Version
Version: 9.1+134wheezy4

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

root@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@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@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@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@rt:~# grep postgres /etc/group | grep www-data
www-data:x:33:postgres

Indeed

postgres@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@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, 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, 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

postgres=# grant all on database rtdb to rtuser;
GRANT
postgres=# ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO rtuser;
ALTER DEFAULT PRIVILEGES

These don’t seem like they would touch sequences.

I’d be inspecting the output of \z topics, and \z on the sequences
that are affected.

Also - connecting as the rtuser may be insufficient to fix
permissions. I suspect you actually need to run the acl fixes in RT
as the postgres user.

The debian packages may have a way to rerun what was done during
install, but I don’t know it off the top of my head.

Once you figure this out, I suggest considering using the
debian-backports repo to get a version of RT from 2013 not 2012.

-kevin

Kevin Falcone writes

postgres=# grant all on database rtdb to rtuser;
GRANT
postgres=# ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO rtuser;
ALTER DEFAULT PRIVILEGES

These don’t seem like they would touch sequences.

I’d be inspecting the output of \z topics, and \z on the sequences
that are affected.

\z showed no sequences. So I ran a

postgres@rt:~$ cat /usr/share/request-tracker4/etc/schema.Pg | psql

\z then showed a bunch of sequeneces. But the rt-setup-database did
issued the same error as indicated before.

Also - connecting as the rtuser may be insufficient to fix
permissions. I suspect you actually need to run the acl fixes in RT
as the postgres user.

What I had to do was to request the script explicitly to run as the
postgres user and authenticate with the postgres user password.

root@rt:~# sudo -u postgres /usr/sbin/rt-setup-database --action acl --dba postgres
In order to create or update your RT database, this script needs to connect to your Pg instanc
e on localhost as postgres
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: postgres
Now inserting database ACLs.
Done.

A test seems to suggest that the problem is gone, but I will
keep a croned job to look at for a reoccurance of the problem.

Thank you so much! If you make it to NYC and if I am in town, I’ll
buy you beer.

Once you figure this out, I suggest considering using the
debian-backports repo to get a version of RT from 2013 not 2012.

Ha! I will pass this suggestion on to my superiors.

Cheers,

Thomas Krichel Thomas Krichel’s homepage
skype:thomaskrichel

Also - connecting as the rtuser may be insufficient to fix
permissions. I suspect you actually need to run the acl fixes in RT
as the postgres user.

What I had to do was to request the script explicitly to run as the
postgres user and authenticate with the postgres user password.

root@rt:~# sudo -u postgres /usr/sbin/rt-setup-database --action acl --dba postgres

A test seems to suggest that the problem is gone, but I will
keep a croned job to look at for a reoccurance of the problem.

That command definitely looks more correct.

I’m glad this sorted the problem.

-kevin