How to define the Postgres schema name in mods-available sql ?
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 ? server = "xxx" port = 5432 login = "" password = "" radius_db = "master_db" It seems I can only specify the database but not the underlying schema. Thanks,
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
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
On 27/05/2021 12:53, Alen via Freeradius-Users wrote:
But I believe I should be able to fix the postgresql/queries.conf by myself as you pointed out.
Exactly. The queries assume the default Pg schema. If you're using something else then modify the queries for what you need. It might be simpler to just create a database for RADIUS only and use the standard FreeRADIUS SQL schema and queries. -- Matthew
participants (2)
-
Alen -
Matthew Newton