search

sympa-community / sympa

Sympa, Mailing List Management Software
https://www.sympa.community/sympa
GNU General Public License v2.0
237 stars 94 forks source link

Sympa tries creating temporary views in PostgreSQL databases unnecessarily #1812

Open woodfighter opened 3 months ago

woodfighter commented 3 months ago

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 https://github.com/sympa-community/sympa/commit/baed879af9b08b2fe7851880e7cab2793ae6b561 . 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: https://github.com/sympa-community/sympa/blob/a9dfb33bb87303af1de3734f46e21ab81443184f/src/lib/Sympa/Family.pm#L521) or has another elegant idea of solving it.

ikedas commented 3 months ago

Hi @woodfighter , Thanks for reporting bug!

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

woodfighter commented 3 months ago

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

Yes, that seems to do the trick.