SQL Questions

What Is The root Password For The MySQL Database?

The default password for the MySQL root user is is defined by the NSTCTMYSQLPASSWD variable found in the file: "/etc/nst.conf". By default this password is randomly generated when the NST system is first booted. You can change this randomly generated password during the MySQL set up process when using the NST WUI.

If you change the password of the root account after setting up the MySQL server, you will need to change the value of NSTCTMYQLPASSWD in the "/etc/nst.conf" file accordingly. Otherwise the NST WUI will not be able to access the MySQL server.

What Username/Password Do I Use To Log Into phpPgAdmin?

When attempting to use the phpPgAdmin web based front end to PostgreSQL, you will need to provide a user name and password. If you use the Network Security Toolkit Web User Interface (NST WUI), or the setup_postgresql script, the username will be: "postgres". Unless you take the time to specify the password during setup, the password will be randomly generated.

You can find the password by looking for the value assigned to the: "NSTCTPOSTGRESQLPASSWD" variable in the the file: "/etc/nst.conf".

If you change the password of the postgres account after setting up the PostgreSQL server, you will need to change the value of NSTCTPOSTGRESQLPASSWD in the "/etc/nst.conf" file accordingly. Otherwise the NST WUI will not be able to access the PostgreSQL server.

How Do I Enable TCP Connections to MySQL?

The Easy Way

Use the Network Security Toolkit Web User Interface (NST WUI) to setup MySQL. You will have a simple check box which allows you to choose whether or not to enable TCP connections. You will also have a text input field where you can specify what systems are permitted to connect.

The Hard Way

By default, the MySQL server will accept connection on port 3306. You can verify this using the simple telnet command as shown below:

[pkb@localhost ~]$ telnet 192.168.0.65 3306
Trying 192.168.0.65...
Connected to 192.168.0.65 (192.168.0.65).
Escape character is '^]'.
(
3.23.58!kyAvOi@,^A^]closeConnection closed by foreign host.
[pkb@localhost ~]$ 

Even though MySQL permits one to make TCP connections, it won't actually allow anyone to log in. For example, if I try to connect remotely using mysql, I get the following message:

[pkb@localhost ~]$ mysql -u root -h 192.168.0.65 -D mysql -p
ERROR 1130 (00000): Host '192.168.0.58' is not allowed to connect to this MySQL server
[pkb@localhost ~]$ 

By default, MySQL won't allow ANY users access to any of the databases if they connect over a TCP connection. In order to permit the connection, you must create a entry in the user table of the MySQL database (make sure you select the PASSWORD function to encrypt your password). In particular, the Host field needs to indicate which host(s) are permitted to connect. If you specify % (which I would not recommend), then a user would be able to connect from any host.

[File|Mysqlaccess.png|center|frame|The user Table After Permitting 192.168.0.58 Access]

After creating the new user, you will need to restart the MySQL server. Once it has been restarted, you should be able to connect as demonstrated below:

[pkb@localhost ~]$ mysql -u root -h 192.168.0.65 -D mysql -p
Enter password:PASSWORD (not echoed)
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18 to server version: 3.23.58 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select User,Host from user;
+------+--------------+
| User | Host         |
+------+--------------+
| root | 192.168.0.58 |
|      | localhost    |
| root | localhost    |
|      | probe        |
| root | probe        |
+------+--------------+
5 rows in set (0.01 sec)

mysql> quit
Bye
[pkb@localhost ~]$ 

How Do I Enable TCP Connections to PostgreSQL?

The Easy Way

If you use the Network Security Toolkit Web User Interface (NST WUI) to setup PostgreSQL, you will have a simple check box which allows you to choose whether or not to enable TCP connections and what systems are permitted to connect.

The Hard Way

By default, the setup_postgresql script starts the PostgreSQL database such that it only accepts Unix-domain connections. This means you will not be able to connect to the database from external machines.

To enable the PostgreSQL server to accept TCP connections, you must modify two configuration files and then restart the server.

You must add the -i option to the: "/etc/sysconfig/pgsql/postgresql" configuration file which is used to start the server.

PGDATA=/var/nst/var/lib/pgsql/data
PGPORT=5432
PGOPTS="-i"

Simply adding the -i option is not enough. You must also update: "/var/lib/pgsql/data/pg_hba.conf" and add configuration lines indicating what host(s) you wish to permit access via TCP connections. The following shows the two lines that need to be added to the end of this file to enable the localhost and all of the machines on the 192.168.100.0/24 network access.

host   all all 127.0.0.1   255.255.255.255 md5
host   all all 192.168.100.0 255.255.255.0   md5

Once you've updated the configuration files, you will need to restart the PostgreSQL server.

[root@probe root]# /etc/rc.d/init.d/postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]
[root@probe root]#