Sympa tries creating temporary views in PostgreSQL databases unnecessarily

[woodfighter]

Version

Sympa 6.2.72

Installation method

Freebsd Port

Expected behavior

Users for databases which are included as a data source for lists should be able to have minimal privileges.

Actual behavior

On every connect to a PostgreSQL database Sympa's database driver tries to create a temporary view named dual (for compatibility with Oracle) since baed879 . For the Sympa's installations user that is a tiny bit inefficient but it probably already has enough privileges anyway, but if you're including separate databases as data sources then you have to give the user the TEMPORARY privilege even though only the SELECT privilege is needed for the query itself.

Steps to reproduce

1. Create PostgreSQL user without the TEMPORARY privilege.
2. Use that user in a SQL based data source in a list.
3. See the error messages in the PostgreSQL server's log file on every connection.

Additional information

It's a minor issue and we're currently working around it by granting the necessary privileges. Perhaps someone sees a way to rewrite the SQL statements (as far as I see all usages of dual are look like this one:

sympa/src/lib/Sympa/Family.pm

Line 521 in a9dfb33

q{INSERT INTO exclusion_table

) or has another elegant idea of solving it.

[ikedas]

Hi @woodfighter ,
Thanks for reporting bug!

Could you please apply this patch and check if the problem will be solved?

[woodfighter]

Could you please apply this patch and check if the problem will be solved?

Yes, that seems to do the trick.