Difference between revisions of "SQL Questions"

From NST Wiki
Jump to navigationJump to search
(What Username/Password Do I Use To Log Into phpPgAdmin?)
(The Hard Way)
Line 124: Line 124:
 
  PGOPTS="-i"
 
  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.
+
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 127.0.0.1/24 md5
  host  all all 192.168.100.0 255.255.255.0  md5
+
  host  all all 192.168.100.0/24 md5
  
 
Once you've updated the configuration files, you will need to restart the PostgreSQL server.
 
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
+
  [root@probe root]# service postgresql restart
 
  Stopping postgresql service:                              [  OK  ]
 
  Stopping postgresql service:                              [  OK  ]
 
  Starting postgresql service:                              [  OK  ]
 
  Starting postgresql service:                              [  OK  ]
 
  [root@probe root]#
 
  [root@probe root]#
 +
 +
= How do I list the databases in PostgreSQL? =
 +
 +
To list all of the databases which the PostgreSQL server has, you need to log in to the PostgreSQL server using a account which has access to all of the databases (like the ''postgres'' account) and then use the "''\l''" command.
 +
 +
[pkb@rice ~]$ psql -h 192.168.1.136 -U postgres postgres
 +
Password for user postgres:
 +
psql (8.4.5)
 +
Type "help" for help.
 +
 +
postgres=# \l
 +
                                  List of databases
 +
    Name    |  Owner  | Encoding |  Collation  |    Ctype    |  Access privileges 
 +
-----------+----------+----------+-------------+-------------+-----------------------
 +
  postgres  | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 |
 +
  template0 | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
 +
                                                              : postgres=CTc/postgres
 +
  template1 | postgres | UTF8    | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
 +
                                                              : postgres=CTc/postgres
 +
(3 rows)
 +
 +
postgres=# \q
 +
[pkb@rice ~]$

Revision as of 09:17, 2 November 2010

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.

How Do I List The MySQL Databases?

Use the show databases command as demonstrated below:

[root@probe root]# mysql -u root -p
Enter password:PASSWORD
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21 to server version: 3.23.58

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

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@probe root]# 

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 TCP connections 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 '^]'.
IHost '192.168.0.12' is not allowed to connect to this MySQL serverConnection 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
Enter password:
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.

The user Table After Permitting 192.168.0.58 Access

After creating the row, 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 ~]$

Why doesn't PostgreSQL work on a live boot?

When running from a live boot off of read-only memory, a magical live file system is inserted such that the read only file system appears to be rewritable. In order to make this possible, memory is consumed every time you update files under this file system.

Since many NST applications, like databases, like to log data and write to disk quite often, it is undesirable for them to do this to the live file system area. To get around this, during a live boot, the NST mounts a tmpfs file system to /var/nst and then uses /var/nst as the root of the output directory for NST configured applications. This works quite well in most situations and the use of the tmpfs file system avoids the memory leakage issue inherent in the live file system.

Unfortunately, PostgreSQL requires features in a file system which are not available in a tmpfs file system. Hence, the default -rdir /var/nst option will fail when setting up PostgreSQL on a live NST boot.

In general, it is recommended to avoid using PostgreSQL on a live boot, however, if you really need to, you can use one of the strategies below:

  • Create a different mount point (like: /home/nst) and mount a real file system (ext4, ext3, etc). Then use the -rdir option, like: "-rdir /home/nst", when setting up PostgreSQL.
  • Create a root directory on the live file system (like: /home/nst) and pass it as the "-rdir /home/nst" option when setting up PostgreSQL. However, be prepared to have your system lock up in a rather short time period if you are performing a high number or database operations.

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 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/24 md5
host   all all 192.168.100.0/24 md5

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

[root@probe root]# service postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]
[root@probe root]#

How do I list the databases in PostgreSQL?

To list all of the databases which the PostgreSQL server has, you need to log in to the PostgreSQL server using a account which has access to all of the databases (like the postgres account) and then use the "\l" command.

[pkb@rice ~]$ psql -h 192.168.1.136 -U postgres postgres
Password for user postgres: 
psql (8.4.5)
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
(3 rows)

postgres=# \q
[pkb@rice ~]$