Re: postgresql HOWTO :)
ok :) it's not a patch, just 4 lines added :) P.S. I also remind, that your sql.conf does not work with postgres :) On Wed, 20 Feb 2002, Alan DeKok wrote: Igor Chen [EMAIL PROTECTED] wrote: First of all, you should use freeradius-snapshot to work with op field. 1) db_postgresql.sql is not still modified, so just do ALTER TABLE radcheck ADD COLUMN op varchar(2); ALTER TABLE radreply ADD COLUMN op varchar(2); ALTER TABLE radgroupcheck ADD COLUMN op varchar(2); ALTER TABLE radgroupreply ADD COLUMN op varchar(2); Can you submit a patch to the db_postgresql.sql? I think you did so before, but I can't find it, sorry. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- cron-ripe /* * --- David Nicklay [ Wed Nov 3 23:18:46 EST 1999 ] */ /* * - Postgres wants C style comments. * - not sure how to do sequences without using SERIAL * (i.e. these below are limited to int4 right now) * numeric(10) doesn't seem to work for sequences... * haven't tried int8 yet as a sequence type yet * - datetimeOS DEFAULT '-00-00 00:00:00' should be * DEFAULT now() in postgres * - postgres apparently creates an index for each * column specified as UNIQUE * Consider implicit creation of tablename_id_seq for each SERIAL field! */ /* * Table structure for table 'dictionary' */ CREATE TABLE dictionary ( id SERIAL, Type VARCHAR(30), Attribute VARCHAR(32), Value VARCHAR(32), Format VARCHAR(20), Vendor VARCHAR(32), PRIMARY KEY (id) ); /* * Table structure for table 'nas' */ CREATE TABLE nas ( id SERIAL, nasname VARCHAR(128), shortname VARCHAR(32), ipaddr VARCHAR(15), type VARCHAR(30), ports int4, secret VARCHAR(60), community VARCHAR(50), snmp VARCHAR(10), PRIMARY KEY (id) ); /* * Table structure for table 'radacct' */ CREATE TABLE radacct ( RadAcctId SERIAL, AcctSessionId VARCHAR(32) DEFAULT '' NOT NULL, AcctUniqueId VARCHAR(32) DEFAULT '' NOT NULL, UserName VARCHAR(32) DEFAULT '' NOT NULL, Realm VARCHAR(30) DEFAULT '', NASIPAddress VARCHAR(15) DEFAULT '' NOT NULL, NASPortId NUMERIC(12), NASPortType VARCHAR(32), AcctStartTime datetime DEFAULT now() NOT NULL, AcctStopTime datetime DEFAULT now() NOT NULL, AcctSessionTime NUMERIC(12), AcctAuthentic VARCHAR(32), ConnectInfo_start VARCHAR(32), ConnectInfo_stop VARCHAR(32), AcctInputOctets NUMERIC(12), AcctOutputOctets NUMERIC(12), CalledStationId VARCHAR(10) DEFAULT '' NOT NULL, CallingStationId VARCHAR(10) DEFAULT '' NOT NULL, AcctTerminateCause VARCHAR(32) DEFAULT '' NOT NULL, ServiceType VARCHAR(32), FramedProtocol VARCHAR(32), FramedIPAddress VARCHAR(15) DEFAULT '' NOT NULL, AcctStartDelay NUMERIC(12), AcctStopDelay NUMERIC(12), PRIMARY KEY (RadAcctId) ); create index radacct_UserName on radacct (UserName); create index radacct_AcctSessionId on radacct (AcctSessionId); create index radacct_AcctUniqueId on radacct (AcctUniqueId); create index radacct_FramedIPAddress on radacct (FramedIPAddress); create index radacct_NASIPAddress on radacct (NASIPAddress); create index radacct_AcctStartTime on radacct (AcctStartTime); create index radacct_AcctStopTime on radacct (AcctStopTime); /* * Table structure for table 'radcheck' */ CREATE TABLE radcheck ( id SERIAL, UserName VARCHAR(30) DEFAULT '' NOT NULL, Attribute VARCHAR(30), Value VARCHAR(40), op VARCHAR(2), PRIMARY KEY (id) ); create index radcheck_UserName on radcheck (UserName,Attribute); /* * Table structure for table 'radgroupcheck' */ CREATE TABLE radgroupcheck ( id SERIAL, GroupName VARCHAR(20) DEFAULT '' NOT NULL, Attribute VARCHAR(40), Value VARCHAR(40), op VARCHAR(2), PRIMARY KEY (id) ); create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute); /* * Table structure for table 'radgroupreply' */ CREATE TABLE radgroupreply ( id SERIAL, GroupName VARCHAR(20) DEFAULT '' NOT NULL, Attribute VARCHAR(40), Value VARCHAR(40), op VARCHAR(2), PRIMARY KEY (id) ); create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute); /* * Table structure for table 'radreply' */ CREATE TABLE radreply ( id SERIAL, UserName VARCHAR(30) DEFAULT '' NOT NULL, Attribute VARCHAR(30), Value VARCHAR(40), op VARCHAR(2), PRIMARY KEY (id) ); create index radreply_UserName on radreply (UserName,Attribute); /* * Table structure for table 'usergroup' */ CREATE TABLE usergroup ( id SERIAL, UserName VARCHAR(30) DEFAULT '' NOT NULL, GroupName VARCHAR(30), PRIMARY KEY (id) ); create index usergroup_UserName on usergroup (UserName); /* * Table structure for table 'realmgroup' */ CREATE TABLE realmgroup ( id SERIAL, RealmName VARCHAR(30) DEFAULT '' NOT NULL, GroupName VARCHAR(30), PRIMARY KEY (id) ); create
Re: postgresql HOWTO :)
Igor Chen [EMAIL PROTECTED] wrote: ok :) it's not a patch, just 4 lines added :) I've added it, thanks. I also remind, that your sql.conf does not work with postgres :) OK. I'll try to dig up the old message where you posted a working postgres sql.conf... Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: postgresql HOWTO :)
Igor Chen [EMAIL PROTECTED] wrote: First of all, you should use freeradius-snapshot to work with op field. 1) db_postgresql.sql is not still modified, so just do ALTER TABLE radcheck ADD COLUMN op varchar(2); ALTER TABLE radreply ADD COLUMN op varchar(2); ALTER TABLE radgroupcheck ADD COLUMN op varchar(2); ALTER TABLE radgroupreply ADD COLUMN op varchar(2); Can you submit a patch to the db_postgresql.sql? I think you did so before, but I can't find it, sorry. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
postgresql HOWTO :)
First of all, you should use freeradius-snapshot to work with op field.
1) db_postgresql.sql is not still modified, so just do
ALTER TABLE radcheck ADD COLUMN op varchar(2);
ALTER TABLE radreply ADD COLUMN op varchar(2);
ALTER TABLE radgroupcheck ADD COLUMN op varchar(2);
ALTER TABLE radgroupreply ADD COLUMN op varchar(2);
on your postgres database
2) Then take my sql.conf as example (modify it and copy to raddb)
3) insert something to radcheck and radreply (or to radgroupcheck/reply)
ex.:
insert into radcheck (username,attribute,value,op) values
('steve','Password','test','==');
insert into radreply (username,attribute,value,op) values
('steve','Reply-Message','Test passed','=');
4) run radiusd -X
5) test it with radtest
(radtest steve test localhost 0 your_secret_key )
6) Did i miss something? :)
On Thu, 14 Feb 2002, please please wrote:
I have installed freeradius 0.4 with postgreSQL.
But if I uncomment sql in authorize, authenticate
and accounting (radiusd.conf), I can not see any
RADIUS packet when I execute radiusd -X (so I can
not know why a request is rejected).
What can I do?
By the way, I would like to know why the op field
does not exist in radcheck, radreply, radgroupcheck
and radgroupreply (Look at db_postgresql.sql).
Regards.
__
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
--
cron-ripe
#
# Configuration for the SQL module.
#
sql {
# Database type
# Current supported are: rlm_sql_mysql, rlm_sql_postgresql, rlm_sql_iodbc,
rlm_sql_oracle
driver = rlm_sql_postgresql
# Connect info
server = localhost
login = radius
password = radpass
# Database table configuration
radius_db = radius
# If you want both stop and start records logged to the
# same SQL table, leave this as is. If you want them in
# different tables, put the start table in acct_table1
# and stop table in acct_table2
acct_table1 = radacct
acct_table2 = radacct
authcheck_table = radcheck
authreply_table = radreply
groupcheck_table = radgroupcheck
groupreply_table = radgroupreply
usergroup_table = usergroup
# Remove stale session if checkrad does not see a double login
deletestalesessions = yes
# Print all SQL statements when in debug mode (-x)
sqltrace = yes
sqltracefile = ${logdir}/sqltrace.sql
# number of sql connections to make to server
num_sql_socks = 15
# Query config: Username
# This is the username that will get substituted, escaped, and added
# as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used below
# everywhere a username substitution is needed so you you can be sure
# the username passed from the client is escaped properly.
#
#sql_user_name = %{Stripped-User-Name}:-%{User-Name}}
# ^^^ --That doesn't work because someone screwed up decode_attribute()
#sql_user_name = %{Stripped-User-Name};
sql_user_name = %{User-Name}
# Authorization Queries
# These queries compare the check items for the user
# in ${authcheck_table} and setup the reply items in
# ${authreply_table}. You can use any query/tables
# you want, but the return data for each row MUST
# be in the following order:
#
# 0. Row ID (currently unused)
# 1. UserName/GroupName
# 2. Item Attr Name
# 3. Item Attr Value
# 4. Item Attr Operation
# Use these for case sensitive usernames. WARNING: Slower queries!
# authorize_check_query = SELECT id,UserName,Attribute,Value,op FROM
${authcheck_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY id
# authorize_reply_query = SELECT id,UserName,Attribute,Value,op FROM
${authreply_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY id
authorize_check_query = SELECT id,UserName,Attribute,Value,op FROM
${authcheck_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id
authorize_reply_query = SELECT id,UserName,Attribute,Value,op FROM
${authreply_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id
Re: postgresql HOWTO :)
I have done all steps but I get this message from
radtest:
radclient:Unknown User-Password
And I do not see any RADIUS packet in debug mode
(radiusd -X).
How can I know if I am getting RADIUS packets?
Regards.
--- Igor Chen [EMAIL PROTECTED] wrote:
First of all, you should use freeradius-snapshot to
work with op field.
1) db_postgresql.sql is not still modified, so just
do
ALTER TABLE radcheck ADD COLUMN op varchar(2);
ALTER TABLE radreply ADD COLUMN op varchar(2);
ALTER TABLE radgroupcheck ADD COLUMN op varchar(2);
ALTER TABLE radgroupreply ADD COLUMN op varchar(2);
on your postgres database
2) Then take my sql.conf as example (modify it and
copy to raddb)
3) insert something to radcheck and radreply (or to
radgroupcheck/reply)
ex.:
insert into radcheck (username,attribute,value,op)
values
('steve','Password','test','==');
insert into radreply (username,attribute,value,op)
values
('steve','Reply-Message','Test passed','=');
4) run radiusd -X
5) test it with radtest
(radtest steve test localhost 0 your_secret_key )
6) Did i miss something? :)
On Thu, 14 Feb 2002, please please wrote:
I have installed freeradius 0.4 with postgreSQL.
But if I uncomment sql in authorize,
authenticate
and accounting (radiusd.conf), I can not see any
RADIUS packet when I execute radiusd -X (so I
can
not know why a request is rejected).
What can I do?
By the way, I would like to know why the op
field
does not exist in radcheck, radreply,
radgroupcheck
and radgroupreply (Look at db_postgresql.sql).
Regards.
__
Do You Yahoo!?
Send FREE Valentine eCards with Yahoo! Greetings!
http://greetings.yahoo.com
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
__
Do You Yahoo!?
Got something to say? Say it better with Yahoo! Video Mail
http://mail.yahoo.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: postgresql HOWTO :)
That is the problem with radclient. My problem is that I do not see any RADIUS packet in debug mode if I enable 'sql' in radiusd.conf. If I disable it, I can see RADIUS packets. Regards. --- Alan DeKok [EMAIL PROTECTED] wrote: please please [EMAIL PROTECTED] wrote: radtest: radclient:Unknown User-Password That attribute isn't in your dictionary file. Fix your dictionary file. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html __ Do You Yahoo!? Got something to say? Say it better with Yahoo! Video Mail http://mail.yahoo.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
