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

27 May 2021

      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@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