John Dennis
2013-12-13 18:18:26 UTC
The setup.sql for postgres in 3.x
raddb/mods-config/sql/main/postgresql/setup.sql
does not grant sufficient permissions to update tables. I'm not a
postgres expert but apparently when you use a SERIAL or BIGSERIAL type
to create a unique key postgres does that by creating something called a
table grants. Currently postgresql/setup.sql does not establish these
GRANT SELECT, USAGE on radacct_radacctid_seq TO radius;
GRANT SELECT, USAGE on radpostauth_id_seq TO radius;
Attached is a trivial patch that does that and stops the INSERT errors
and should be applied to the 3.x branch (and master?).
It seems odd to me that postgres requires permission beyond INSERT to
create a row having a unique key, but apparently after googling a bit
this is the case for postgres > 8.3(?). If you're a postgres expert and
see an issue with the above please chime in.
raddb/mods-config/sql/main/postgresql/setup.sql
does not grant sufficient permissions to update tables. I'm not a
postgres expert but apparently when you use a SERIAL or BIGSERIAL type
to create a unique key postgres does that by creating something called a
psql:schema.sql:180: NOTICE: CREATE TABLE will create implicit sequence "radpostauth_id_seq" for serial column "radpostauth.id"
Those sequence object need permission grants on them in addition to thetable grants. Currently postgresql/setup.sql does not establish these
rlm_sql (sql): Executing query: 'INSERT INTO radpostauth (username, pass, reply, authdate) VALUES('paptestuser', 'mypassword', 'Access-Accept', NOW())'
rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
rlm_sql_postgresql: Error permission denied for sequence radpostauth_id_seq
rlm_sql_postgresql: Postgresql Fatal Error: [42501: INSUFFICIENT PRIVILEGE] Occurred!!
rlm_sql (sql): Database query error: ERROR: permission denied for sequence radpostauth_id_seq
The solution is to add these grants to postgresql/setup.sqlrlm_sql_postgresql: Status: PGRES_FATAL_ERROR
rlm_sql_postgresql: Error permission denied for sequence radpostauth_id_seq
rlm_sql_postgresql: Postgresql Fatal Error: [42501: INSUFFICIENT PRIVILEGE] Occurred!!
rlm_sql (sql): Database query error: ERROR: permission denied for sequence radpostauth_id_seq
GRANT SELECT, USAGE on radacct_radacctid_seq TO radius;
GRANT SELECT, USAGE on radpostauth_id_seq TO radius;
Attached is a trivial patch that does that and stops the INSERT errors
and should be applied to the 3.x branch (and master?).
It seems odd to me that postgres requires permission beyond INSERT to
create a row having a unique key, but apparently after googling a bit
this is the case for postgres > 8.3(?). If you're a postgres expert and
see an issue with the above please chime in.
--
John
John