Inserting or Changing Primary Key

SELECT script is easy to be obtained from PostgreSQL tables by using the GUI such as pgAdmin. By right-clicking on a table and then select the SELECT script, we get the script. Another way could be affordable by the coding.

First, create a table.
CREATE TABLE coba
(
pk character(12) NOT NULL,
nourut smallint NOT NULL,
nourutku smallint NOT NULL,
unit_id character(3) NOT NULL,
CONSTRAINT coba_pkey PRIMARY KEY (pk)
)
WITH (OIDS=FALSE);
ALTER TABLE coba OWNER TO binsar;


List the table's fields:
SELECT column_name FROM information_schema.columns WHERE table_name = 'coba'

Output:
"pk"
"nourut"
"nourutku"
"unit_id"


Another way...using coding
SELECT script can be generated through the following script:

SELECT ' SELECT ' || array_to_string(ARRAY(SELECT '' ||column_name
FROM information_schema.columns
WHERE table_name = 'coba'), ',') || ' FROM binsar.coba;';

Output:
SELECT pk,nourut,nourutku,unit_id FROM binsar.coba;


We may face the need to change the primary key or adding a new primary key based on the existing rows at the same table. The problems will be encountered, especially if there are many relationships between tables. For example, how do we simply insert some rows and only change the primary key value?

insert into binsar.coba VALUES ('1',1, 1,'1');
insert into binsar.coba SELECT '2',2, 2,'2';
insert into binsar.coba SELECT '3', nourut, nourutku, unit_id FROM binsar.coba WHERE pk='1';


The last command simply solve the need....certainly for a table with some fields.
If I have many tables and fields, ( think about fields name: new, dropped or modified ),
I was not diligent enough to do it. Were you?

Here the script:

SELECT 'SELECT ''300'',' || array_to_string(ARRAY(SELECT '' || column_name
FROM information_schema.columns
WHERE table_name = 'coba'
AND column_name NOT IN('pk')
), ',') || ' FROM binsar.coba where pk=''1'';';


Output:
SELECT '300',nourut,nourutku,unit_id FROM binsar.coba where pk='1';


So, new rows can be added with ease. We do not need to write all the field names,
just need to write down the field we do not want, also without hard coding the SELECT script.

In a stored function (PL/pgSQL), I use EXECUTE 'statement' for all tables in a database.

CREATE OR REPLACE FUNCTION cobadeh(pk character, newpk character,tabname character)
RETURNS character AS
$BODY$
DECLARE
t1 character varying (300);
t2 character varying (300);
BEGIN
t1='INSERT INTO binsar.' || tabname;
SELECT ' SELECT ' || '''' || newpk|| '''' || ',' || array_to_string(ARRAY(SELECT '' || column_name
FROM information_schema.columns
WHERE table_name = 'coba'
AND column_name NOT IN('pk')
), ',') || ' FROM binsar.coba WHERE pk=' || '''' ||pk ||'''' INTO t2;
EXECUTE t1 || t2;
RETURN t1 || t2;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION cobadeh(character,character,character) OWNER TO binsar;

then tests it:

select * from binsar.cobadeh('1','300', 'coba');
select * from binsar.cobadeh('3','ABC', 'coba');

will run these command:
"INSERT INTO binsar.coba SELECT '300',nourut,nourutku,unit_id FROM binsar.coba WHERE pk='1'"
INSERT INTO binsar.coba SELECT 'ABC',nourut,nourutku,unit_id FROM binsar.coba WHERE pk='3'


The table now has new rows:
select * from binsar.coba ;
"1 ";1;1;"1 "
"2 ";2;2;"2 "
"3 ";1;1;"1 "
"300 ";1;1;"1 "
"ABC ";1;1;"1 "

that's.

Net-SNMP on FreeBSD

1. Installation using ports
# cd /usr/ports/net-mgmt/net-snmp
# make install clean

2. List directories and files name that contain "snmpd"
# pkg_info -L net-snmp-5.4.2.1 | grep snmpd
...
/usr/local/sbin/snmpd
/usr/local/share/snmp/snmpd.conf.example
/usr/local/etc/rc.d/snmpd

3. Create snmpd.conf file from the example
# mkdir /usr/local/etc/snmp
# cp /usr/local/share/snmp/snmpd.conf.example /usr/local/etc/snmp/snmpd.conf

4. Edit the file
# vi /usr/local/etc/snmp/snmpd.conf

and modify from:
# sec.name source community
com2sec local localhost COMMUNITY
com2sec mynetwork NETWORK/24 COMMUNITY

become:
# sec.name source community
com2sec local localhost private
com2sec mynetwork 192.168.123.0/28 public

5. Modify rc configuration file
#vi /etc/rc.conf
then add this
snmpd_enable="YES"

6. Start snmp daemon
Check if port 161 active
#sockstat -4 | grep 161
---nothing displayed

Ready to start snmpd
# /usr/local/etc/rc.d/snmpd start
Starting snmpd.

# sockstat -4 | grep 161
root snmpd 16217 10 udp4 *:161 *:*

# ls /var/run | grep snmpd
snmpd.pid

Read the log file
# cat /var/log/snmpd.log

7. Is it works?
# snmpwalk -c private -v1 localhost | grep Address
# snmpwalk -c private -v2c localhost HOST-RESOURCES-MIB::hrSWRunName
# snmpwalk -c public -v1 192.168.123.1 sysDescr
SNMPv2-MIB::sysDescr.0 = STRING: OpenBSD host1.mydomain.com 4.7 GENERIC.MP#449 i386

# snmpwalk -c public -v1 192.168.123.4 sysDescr
SNMPv2-MIB::sysDescr.0 = STRING: FreeBSD host2.mydomain.com 7.0-RELEASE FreeBSD 7.0-RELEASE #0: Thu Jan 15 16:15:25 WIT 2009 root@host2.mydomain.com:/usr/src/sys/i386/compile/BINSARBSD i386

# snmpwalk -c public -v1 192.168.123.1 hrSystemUptime.0
HOST-RESOURCES-MIB::hrSystemUptime.0 = Timeticks: (15403177) 1 day, 18:47:11.77

# snmpget -c public -v1 192.168.123.4 hrSystemUptime.0
HOST-RESOURCES-MIB::hrSystemUptime.0 = Timeticks: (1598771) 4:26:27.71

# snmpget -c public -v2c 222.xxx.yyy.zzz sysUpTime.0
DISMAN-EVENT-MIB::sysUpTimeInstance = Timeticks: (15976711) 24 day, 20:22:47.11

# snmpget -c private -v1 localhost .1.3.6.1.4.1.2021.100.6.0
# snmpwalk -c public -v1 192.168.123.1 .1.3.6.1.4.1.2021.100.6.0
# snmpstatus -c private -v2c localhost
[UDP: [0.0.0.0]->[127.0.0.1]:161]=>[FreeBSD host2.mydomain.com 7.0-RELEASE FreeBSD 7.0-RELEASE #0: Thu Jan 15 16:15:25 WIT 2009 root@host2.mydomain.com:/usr/src/sys/i386/compile/BINSARBSD i386] Up: 0:44:48.24
Interfaces: 3, Recv/Trans packets: 116458/74594 IP: 85052/74569
1 interface is down!

# snmpstatus -c public -v2c 192.168.123.1
[UDP: [0.0.0.0]->[192.168.123.1]:161]=>[OpenBSD host1.mydomain.com 4.7 GENERIC.MP#449 i386] Up: 1 day, 19:15:49.53
Interfaces: 7, Recv/Trans packets: 337656/162218 IP: 219127/159655
3 interfaces are down!

# snmptest -c public -v 1 localhost
Variable: system.sysDescr.0
Variable:
Received Get Response from UDP: [0.0.0.0]->[127.0.0.1]:161
requestid 0xCC474EF errstat 0x0 errindex 0x0
SNMPv2-MIB::sysDescr.0 = STRING: FreeBSD host2.mydomain.com 7.0-RELEASE FreeBSD 7.0-RELEASE #0: Thu Jan 15 16:15:25 WIT 2009 root@host2.mydomain.com:/usr/src/sys/i386/compile/BINSARBSD i386
Variable:

You may be interested in these OID:
# snmpget -c private -v1 localhost .1.3.6.1.2.1.1.1.0

probably these too
1.3.6.1.2.1.2.2.1.10.1
1.3.6.1.2.1.2.2.1.16.1
1.3.6.1.4.1.2021
1.3.6.1.4.1.2021.2
1.3.6.1.4.1.2021.100.6.0
1.3.6.1.4.1.2021.100.11.0

In this writing, snmpd works with SNMP version 1 and 2c and some snmpd command has been used.

SNMP: Simple Network Management Protocol

1. Briefs
Simple Network Management Protocol (SNMP) is a component of the Internet Protocol Suite (IPS). The IPS is the set of communications protocols used for the Internet and other similar networks; it is commonly also known as TCP/IP, named from two of the most important protocols in it: TCP and IP.

The IPS consists of 4 abstraction layers: Link, Internet, Transport, and Application Layer. SNMP operate in the Application Layer of the IPS (Layer 7 of the OSI model). DNS, HTTP, IMAP, IRC, RPC, SMTP, SSH, Telnet, TSL/SSL also work in this layer.

SNMP is an "Internet-standard protocol for managing devices on IP networks. Devices that typically support SNMP include routers, switches, servers, workstations, printers, ups, and more.”. It is the most popular protocol in use to manage networked devices to facilitate the exchange of management information between networked devices. The SNMP protocol enables network and system administrators to remotely monitor and configure devices on the network.


2. Works
By using SNMP, we able to monitor networks and devices. Those network elements which have management agents, responsible for performing the network management functions requested by the network management stations. The SNMP is used to communicate management information between the network management stations and the agents in the network elements.

SNMP is built around the concept of "managers" and "agents." Manager software (commonly installed on a network management system) makes requests to agent software running on a host or device to gather data on the operational status, configuration, or performance statistics of that system (polling). Some agents allow configuration parameters to be changed by managers, while others provide read-only statistics and configuration information. Additionally, agents can generate ad hoc messages to manager systems to inform them of unusual events (traps).

The following is a typical what SNMP do:

• The SNMP agent receives requests on UDP port 161.
• The manager may send requests from any available source port to port 161 in the agent.
• The agent response will be sent back to the source port on the manager.
• The manager receives notifications (Traps and InformRequests) on port 162.
• The agent may generate notifications from any available port.

3. Protocols/ports

• SNMP uses 161/udp for general purpose (request/response) communications, and 162/udp for traps.
• Additionally, the SNMP multiplexing protocol uses 199/tcp.
• Another SNMP extension, the AgentX protocol uses 705/tcp.

4. Administrative Relationships
The SNMP architecture admits a variety of administrative relationships among entities that participate in the protocol. Below are the terms that are commonly found:

• SNMP Application entities. The entities residing at management stations and network elements which communicate with one another using SNMP.
• SNMP Protocol entities. The peer processes which implement the SNMP, and thus support the SNMP application entities.
• SNMP community. A pairing of an SNMP agent with some arbitrary set of SNMP application entities. Each SNMP community is named by a string of octets, that is called the community name for said community.
• Authentic SNMP message. An SNMP message originated by an SNMP application entity that is belongs to the SNMP community named by the community component ofsaid message.
• Authentication scheme. The set of rules by which an SNMP messageis identified as an authentic SNMP message for a particular SNMP community.
• Authentication service. An implementation of a function that identifies authentic SNMP messages according to one or more authentication schemes.
• SNMP MIB view. For any network element, a subset of objects in the MIB that pertain to that element.
• SNMP access mode. An element of the set { READ-ONLY, READ-WRITE }.
• SNMP community profile. A pairing of a SNMP access mode with a SNMP MIB view. A SNMP community profile represents specified access privileges to variables in a specified MIB view. For every variable in the MIB view in a given SNMP community profile, access to that variable is represented by the profile according to the following conventions:
  

1. if said variable is defined in the MIB with "Access:" of "none," it is unavailable as an operand for any operator;
2. if said variable is defined in the MIB with "Access:" of "read-write" or "write-only" and the access mode of the given profile is READ-WRITE, that variable is available as an operand for the get, set, and trap operations;
3. otherwise, the variable is available as an operand for the get and trap operations.
4. In those cases where a "write-only" variable is an operand used for the get or trap operations, the value given for the variable is implementation-specific.

• SNMP access policy. A pairing of a SNMP community with a SNMP community profile. An access policy represents a specified community profile afforded by the SNMP agent of a specified SNMP community to other members of that community. All administrative relationships among SNMP application entities are defined in terms of SNMP access policies.
• SNMP proxy access policy. For every SNMP access policy, if the network element on which the SNMP agent for the specified SNMP community resides is not that to which the MIB view for the specified profile pertains.
• SNMP proxy agent. The SNMP agent associated with a proxy access policy.
  

5. Structure of Management Information
Management information is viewed as a collection of managed objects, residing in a virtual information store, termed the Management Information Base (MIB). Collections of related objects are defined in MIB modules. These modules are written in the SNMP MIB module language, which contains elements of OSI's Abstract Syntax Notation One (ASN.1) language. STD 58, RFCs 2578, 2579, 2580, together define the MIB module language, specify the base data types for objects, specify a core set of short-hand specifications for data types called textual conventions, and specify a few administrative assignments of object identifier (OID) values.

6 SNMP Applications
It is the purpose of RFC 2573, "SNMP Applications" to describe the five types of applications which can be associated with an SNMP engine. They are:

• Applications which initiate SNMP Read-Class, and/or Write-Class requests, called 'Command Generators.'
• Applications which respond to SNMP Read-Class, and/or Write-Class requests, called 'Command Responders.'
• Applications which generate SNMP Notification-Class PDUs, called 'Notification Originators.'
• Applications which receive SNMP Notification-Class PDUs, called 'Notification Receivers.'
• Applications which forward SNMP messages, called 'Proxy Forwarders.'
  

7.Securities
SNMP versions 1 and 2c are subject to packet sniffing of the clear text community string from the network traffic, because they do not implement encryption. All versions of SNMP are subject to brute force and dictionary attacks for guessing the community strings, authentication strings, authentication keys, encryption strings, or encryption keys, because they do not implement a challenge-response handshake.

Entropy is an important consideration when selecting keys, passwords and/or algorithms. Although SNMP works over TCP and other protocols, it is most commonly used over UDP that is connectionless and vulnerable to IP spoofing attacks. Thus, all versions are subject to bypassing device access lists that might have been implemented to restrict SNMP access, though SNMPv3's other security mechanisms should prevent a successful attack.

SNMP's powerful configuration (write) capabilities are not being fully utilized by many vendors, partly due to lack of security in SNMP versions before SNMPv3 and partly due to the fact that many devices simply are not capable of being configured via individual MIB object changes.


8. SNMP development
SNMP version 1 (SNMPv1)
SNMPv1 is the initial implementation of the SNMP protocol, it is widely used and is the de facto network-management protocol in the Internet community. (RFC 1155, 1156, 1157, 1213).
Version 1 has been criticized for its poor security. Authentication of clients is performed only by a "community string", in effect a type of password, which is transmitted in cleartext.

SNMP version 2 (SNMPv2)
SNMPv2 (RFC 1441–1452), revises version 1 and includes improvements in the areas of performance, security, confidentiality, and manager-to-manager communications. However, the new party-based security system in SNMPv2, viewed by many as overly complex, was not widely accepted.

Community-Based SNMP version 2, or SNMPv2c, is defined in RFC 1901–1908. SNMPv2c comprises SNMPv2 without the controversial new SNMP v2 security model, using instead the simple community-based security scheme of SNMPv1. While officially only a "Draft Standard", this is widely considered the de facto SNMPv2 standard.

User-Based SNMP version 2, or SNMPv2u, is defined in RFC 1909–1910. This is a compromise that attempts to offer greater security than SNMPv1, but without incurring the high complexity of SNMPv2. A variant of this was commercialized as SNMP v2*, and the mechanism was eventually adopted as one of two security frameworks in SNMP v3.

SNMP version 3 (SNMPv3)
SNMPv3 makes things look much different by introducing new textual conventions, concepts, and terminology. SNMPv3 primarily added security and remote configuration enhancements to SNMP.

Security has been the biggest weakness of SNMP since the beginning. Authentication in SNMP Versions 1 and 2 amounts to nothing more than a password (community string) sent in clear text between a manager and agent. Each SNMPv3 message contains security parameters which are encoded as an octet string. The meaning of these security parameters depends on the security model being used.

SNMPv3 provides important security features:

• Confidentiality - Encryption of packets to prevent snooping by an unauthorized source.
• Integrity - Message integrity to ensure that a packet has not been tampered with in transit including an optional packet replay protection mechanism.
• Authentication - to verify that the message is from a valid source.

SNMPv 3 as defined by RFC 3411–RFC 3418 is the current standard version of SNMP.
The IETF has designated SNMPv3 a full Internet standard, the highest maturity level for an RFC.

In practice, SNMP implementations often support multiple versions: typically SNMPv1, SNMPv2c, and SNMPv3.

Relaying or redirecting access to another IP/Port

I have a machine with two cards: xl0 (192.168.168.168 and it's alias 10.10.10.10)
xl1 (222.xx.xx.xx). They would serve an internal and external connection respectively to a web server named test.fer.com. Every web access from internet will be passed to xl1, and xl0 for intranet use, and the real web server run on another machine (10.10.10.11) will serve any request.

For this configuration to work, a port must be listening on www port. I have use Apache and applied virtual host, it works fine.

I would try another way, using relayd on OpenBSD 4.7.
relayd is a daemon to relay and dynamically redirect incoming connections to a target host. Its main purposes are to run as a load-balancer, application layer gateway, or transparent proxy.

The daemon is able to monitor groups of hosts for availability, which is determined by checking for a specific service common to a host group. When availability is confirmed, layer 3 and/or layer 7 forwarding services are set up by relayd.

Layer 3 redirection happens at the packet level; to configure it, relayd communicates with pf.
To allow relayd to properly set up pfrules, the following line is required in the filter section of pf.conf:

anchor "relayd/*"

Layer 7 relaying happens at the application level and is handled by relayd itself.
Various application level filtering and protocol-specific-balancing options are available for relays.

Here an example of relayd.conf or relay daemon configuration file
#
# Macros
relayd_lokal="10.10.10.10"
relayd_lokal_int="xl0"
relayd_ip="222.xx.xx.xx"
relayd_port ="80"
relayd_int="xl1"
mywebserver ="10.10.10.11"

table { $mywebserver }
serverku_port="80"

# Global Options
interval 10
timeout 1000
prefork 5

log updates

redirect anchor_name {
listen on $relayd_ip port $relayd_port interface $relayd_int
# tag every packet that goes thru the rdr rule with RELAYD
tag RELAYD
sticky-address
forward to port $serverku_port mode roundrobin check tcp
}


http protocol "www_service" {
tcp { nodelay, socket buffer 65536 }
header append "$REMOTE_ADDR" to "X-Forwarded-For"
header append "$SERVER_ADDR:$SEVER_PORT" to "X-Forwarded-By"
}

relay "www_forwarder" {
listen on $relayd_lokal port $relayd_port
protocol "www_service"
forward to $mywebserver port $serverku_port
}


Remind that in pf.conf to put rule something like this:
pass in log on $ext_if inet proto tcp from any to $mywebserver port 80 flags S/SA synproxy state tagged RELAYD

Adding a file set later on OpenBSD

I have to install the gd library that requires the X components and experiencing this error
=================================
#cd /usr/ports/graphics/gd
#make install
===> Checking files for gd-2.0.35p0
>> Fetch http://www.libgd.org//releases/gd-2.0.35.tar.gz
..
installation failed (I forgot the error messages)
After installing xbase47.tgz and repeat the installation of gd, pieces of the following error appears

===> Verifying specs: jpeg.>=62 png.>=2 iconv.>=2 jpeg.>=62 png.>=2 iconv.>=2 c expat fontconfig freetype m z
Missing library for fontconfig
Missing library for freetype
=================================

Previously, I chose file sets without X components. Lately, I have to install some package that requires one or more files sets that are not selected before.
As you recall, when installing OpenBSD, we select the following file sets to be installed:

bsd - This is the Kernel. REQUIRED.
bsd.mp - Multi-processor (SMP) kernel
bsd.rd - RAM disk kernel
base47.tgz - Contains the base OpenBSD system. REQUIRED.
etc47.tgz - Contains all the files in /etc. REQUIRED.
comp47.tgz - Contains the compiler and its tools, headers and libraries.
man47.tgz - Contains man pages
misc47.tgz - Contains misc info, setup documentation
game47.tgz - Contains the games for OpenBSD
xbase47.tgz - Contains the base libraries and utilities for X11
xetc47.tgz - Contains the /etc/X11 and /etc/fonts configuration files
xfont47.tgz - Contains X11's font server and fonts
xserv47.tgz - Contains X11's X servers
xshare47.tgz - Contains manpages, locale settings, includes, etc. for X

For the case above, there are two ways I can do:
First, use the installation media such as CD-ROM, and choose Upgrade (rather than Install).
Second, use tar command to install the file on the root of the filesystem.

I chose the last way and do these:
1. Download these file sets and save them into the directory /home/binsar.
# wget -nd -P /home/binsar http://ftp.jaist.ac.jp/pub/OpenBSD/4.7/i386/xbase47.tgz
# wget -nd -P /home/binsar http://ftp.jaist.ac.jp/pub/OpenBSD/4.7/i386/xshare47.tgz
--2011-01-05 05:38:15-- http://ftp.jaist.ac.jp/pub/OpenBSD/4.7/i386/xshare47.tgz
Connecting to ftp.jaist.ac.jp (ftp.jaist.ac.jp)|150.65.7.130|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 2946170 (2.8M) [application/x-gzip]
Saving to: `/home/binsar/xshare47.tgz'
100%[======================================>] 2,946,170 135K/s in 22s
2011-01-05 05:38:37 (131 KB/s) - `/home/binsar/xshare47.tgz' saved [2946170/2946170]

2. Install the file sets into root directory (The 'p' option in the tar command in order to restore the file permissions properly!!!).

#tar xzvphf /home/binsar/xbase47.tgz -C /
#tar xzvphf /home/binsar/xshare47.tgz -C /

3. Update the shared library cache (without rebooting) by run ldconfig to add all the X libraries to the cache:
# ldconfig -m /usr/X11R6/lib


or simply reboot my system, and this will be done automatically by the rc startup script.

4. Try install the gd library
#cd /usr/ports/graphics/gd
#make install clean
# pkg_info
...
autoconf-2.61p3 automatically configure source code on many Un*x platforms
bzip2-1.0.5 block-sorting file compressor, unencumbered
cvsup-16.1hp2-no_x11 network file distribution system
db-4.6.21p0 Berkeley DB package, revision 4
gd-2.0.35p0 library for dynamic creation of images
gdbm-1.8.3p0 GNU dbm
...