How to define the Postgres schema name in mods-available sql ?

[Alen]

Thank you. That was very helpful.

So I have to place the name of my schema 'radius` in front of every table.

        authorize_check_query = "\
        SELECT id, UserName, Attribute, Value, Op \
        FROM ${authcheck_table} \
        WHERE Username = '%{SQL-User-Name}' \
        ORDER BY id"

Like this:

        authorize_check_query = "\
        SELECT id, UserName, Attribute, Value, Op \
        FROM radius.${authcheck_table} \
        WHERE Username = '%{SQL-User-Name}' \
        ORDER BY id"

In MySql a database is equal a schema in postgres. Therefore in Postgres ecosystem there is usually one database that contains multiple schema.

Hence I was expecting to find a variable where I could define the name of the schema. But I believe I should be able to fix the postgresql/queries.conf by myself as you pointed out.

If I'm missing anything, please correct me.
Thanks



‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, May 27, 2021 12:28 PM, Matthew Newton <mcn at freeradius.org> wrote:

> On 27/05/2021 12:17, Alen via Freeradius-Users wrote:
>
> > In our postgres db we have a master_db and two underlying schemas, main and radius.
> > How do I define the schema in /etc/freeradius/3.0/mods-available/sql ?
>
> The standard schema is in
> raddb/mods-config/sql/main/postgresql/schema.sql. Import that into your
> database manually.
>
> > server = "xxx"
> > port = 5432
> > login = ""
> > password = ""
> > radius_db = "master_db"
> > It seems I can only specify the database but not the underlying schema.
>
> There's an INCLUDE line at the bottom of that file to load the queries
> for the database you are using. The queries in
> raddb/mods-config/sql/main/postgresql/queries.conf. They use the above
> schema.
>
> You can of course edit both if you want, and you know what you are
> doing. I would start with the default and get that working to begin with.
>
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> Matthew
>
> --------
>
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html