SQL Questions - Revision history

Rwh at 19:42, 17 November 2010

2010-11-17T19:42:25Z

← Older revision		Revision as of 15:42, 17 November 2010
Line 1:		Line 1:
			__TOC__
	= What Is The root Password For The MySQL Database? =		= What Is The root Password For The MySQL Database? =

Paul Blankenbaker: /* The Hard Way */

2010-11-02T13:17:37Z

The Hard Way

← Older revision		Revision as of 09:17, 2 November 2010
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 ~]$

Paul Blankenbaker: /* What Username/Password Do I Use To Log Into phpPgAdmin? */

2010-11-02T12:46:08Z

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

@@ Line 84: / Line 84: @@
   Bye
   [pkb@localhost ~]$
 = What Username/Password Do I Use To Log Into phpPgAdmin? =

Paul Blankenbaker: /* The Hard Way */

2010-11-02T00:50:58Z

The Hard Way

← Older revision		Revision as of 20:50, 1 November 2010
Line 37:		Line 37:
	== The Hard Way ==		== 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:		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		[pkb@localhost ~]$ telnet 192.168.0.65 3306
Line 43:		Line 43:
	Connected to 192.168.0.65 (192.168.0.65).		Connected to 192.168.0.65 (192.168.0.65).
	Escape character is '^]'.		Escape character is '^]'.
	(		IHost '192.168.0.12' is not allowed to connect to this MySQL serverConnection closed by foreign host.
	3.23.~~58!kyAvOi@,^A^]closeConnection~~ closed by foreign host.
	[pkb@localhost ~]$		[pkb@localhost ~]$

Line 50:		Line 49:

	[pkb@localhost ~]$ mysql -u root -h 192.168.0.65 -D mysql -p		[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		ERROR 1130 (00000): Host '192.168.0.58' is not allowed to connect to this MySQL server
	[pkb@localhost ~]$		[pkb@localhost ~]$
Line 57:		Line 57:
	[[File:Mysqlaccess.png\|center\|frame\|The user Table After Permitting 192.168.0.58 Access]]		[[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:		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		[pkb@localhost ~]$ mysql -u root -h 192.168.0.65 -D mysql -p
Line 84:		Line 84:
	Bye		Bye
	[pkb@localhost ~]$		[pkb@localhost ~]$


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

Paul Blankenbaker at 00:27, 2 November 2010

2010-11-02T00:27:19Z

← Older revision		Revision as of 20:27, 1 November 2010
Line 5:		Line 5:
	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.		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~~? =		= How Do I List The MySQL Databases? =

	~~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.~~		Use the '''show databases''' command as demonstrated below:

	~~You can find the~~ password ~~by looking for~~ the ~~value assigned~~ to ~~the~~: ~~"''NSTCTPOSTGRESQLPASSWD''" variable in the the file: "''/etc/nst~~.~~conf''"~~.		[root@probe root]# mysql -u root -p
			Enter password:PASSWORD
	~~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~~.		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? =		= How Do I Enable TCP Connections to MySQL? =
Line 68:		Line 84:
	Bye		Bye
	[pkb@localhost ~]$		[pkb@localhost ~]$


			= 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? =		= How Do I Enable TCP Connections to PostgreSQL? =

Paul Blankenbaker: /* The Hard Way */

2010-11-02T00:24:26Z

The Hard Way

← Older revision		Revision as of 20:24, 1 November 2010
Line 39:		Line 39:
	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.		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]		[[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:		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:
Line 67:		Line 67:
	mysql> quit		mysql> quit
	Bye		Bye
	[pkb@localhost ~]$		[pkb@localhost ~]$


	= How Do I Enable TCP Connections to PostgreSQL? =		= How Do I Enable TCP Connections to PostgreSQL? =

Paul Blankenbaker: /* The Hard Way */

2010-11-02T00:21:58Z

The Hard Way

← Older revision		Revision as of 20:21, 1 November 2010
Line 88:		Line 88:
	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'' 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 255.255.255.255 md5

Paul Blankenbaker: /* The Easy Way */

2010-11-02T00:20:42Z

The Easy Way

← Older revision		Revision as of 20:20, 1 November 2010
Line 74:		Line 74:
	== The Easy Way ==		== 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~~. This is the ''easy way'' - you can stop reading now if you take this route~~.		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 ==		== The Hard Way ==

Paul Blankenbaker: /* The Easy Way */

2010-11-02T00:18:54Z

The Easy Way

← Older revision		Revision as of 20:18, 1 November 2010
Line 17:		Line 17:
	== The Easy Way ==		== 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 ~~specif~~ what systems are permitted to connect.		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 ==		== The Hard Way ==

Paul Blankenbaker: /* What Username/Password Do I Used To Log Into phpPgAdmin? */

2010-11-02T00:18:19Z

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

← Older revision		Revision as of 20:18, 1 November 2010
Line 5:		Line 5:
	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.		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 ~~Used~~ To Log Into phpPgAdmin? =		= 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.		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.