insufficient permission for postgres tables in FreeRADIUS 3.x

[John Dennis]

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
sequence object. After creating the schema you'll see things like this:

> 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 the
table grants. Currently postgresql/setup.sql does not establish these
sequence grants, as a result you'll see errors like this:

> 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.sql

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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: freeradius-postgres-sql.patch
Type: text/x-patch
Size: 545 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-devel/attachments/20131213/470554f8/attachment.bin>