SELinux will block PHP/Apache from connecting to PostgreSQL (and probably any other DB) by default on some Linux distributions. If you are trying to get PHP to connect to a PostreSQL DB on a linux box for the first time and you are sure your pg_hba.conf on the target box is setup correctly then try this:
setsebool -P httpd_can_network_connect 1
This should configure SELinux to allow Apache/PHP to connect to other hosts.
The Oracle “CLOB” (Character Large Object) is a data type used to store up to 4 Gigabytes of text. Retrieving the contents of a CLOB is not as intuitive as you might think.
Let’s say you have a CLOB field/column named “mychars” in an Oracle DB table named “mytable” along with some other fields. You want to simply echo out the text in the “mychars” field:
The above code will give you an error that looks like the following:
If you try to do a print_r() on the CLOB in an attempt to figure out what you are dealing with you will get something that looks like:
This is because a Lob object is returned instead of the contents of the CLOB.
To get the CLOB contents you will need to call the load() or read() methods on the returned object. The latter will require the length of data to read in bytes but has the advantage of not being limited by the script memory limit:
load();
//or
echo $row['mychars']->read(2000);
}
}
?>
In my previous post I covered setting up/installing MySQL and connecting via MySQL Administrator from a remote host. In this post I will talk about the PostgreSQL equivalent.
As a side note, any database system should always be behind a firewall of some kind. Putting up a database server on a public IP using the default port (5432 for PostgreSQL and 3306 for MySQL) is begging for trouble. Most web applications should only require revealing the web server to the world and even then only ports 80 and 443 (for SSL). Ideally database servers should never see the light of a port scanner.
in the upper left hand corner to pull up the “New Server Registration” window. Enter any name you like for “Name”, the IP or DNS name of your PostgreSQL Server for “Host”, “postgres” for the “Username” and nothing for the password.If you try to connect from a remote host now without configuring your PostgreSQL server bindings you will likely see something similar to the following error:
The server doesn’t accept connections: the connection library reports
could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host "192.168.1.100" and accepting TCP/IP connections on port 5432?
The problem is that by default PostgreSQL does not listen on or bind to any IP addresses (not even localhost). Like MySQL, this was probably done so PostgreSQL is locked down by default which is a good security feature.
Change:
Now pgAdmin III will make a connection but will likely return the following error:
It still doesn’t work but we are a bit closer. PostgreSQL checks that users are connecting from allowable IP addresses or IP address ranges. By default, the “postgres” default user can only connect locally so we will need to fix this.
Path names vary between Linux distributions so this may take a little hunting.
Note that the IP subnet is specified in CIDR notation. 192.168.0.0/16 specifies that any host with an IP address that starts with 192.168 will have access.
To:
Usually it is bad practice to do everything with your default account so you might want to create another user. This is particularly the case if you are going to use it for connecting from PHP. In PostgreSQL, the concept of a user translates to a “Login Role”.
Now you can use pgAdmin III to create your first database and table.
That’s it for this post. Good luck!
There are a few “gotchas” if you are trying to setup MySQL on Linux server, Ubuntu in this case, and connect to it using MySQL Administrator from somewhere else. Here is what worked for me…
If you try to connect to your server using MySQL Administrator from a remote host without configuring your MySQL server bindings you will likely see something similar to the following error:
MySQL Error Number 2003
Can’t connect to MySQL server on ‘mysqlhost.example.com'(10061)
The problem is that by default MySQL server is only bound to the localhost/loopback IP of 127.0.0.1. This was probably done so MySQL is locked down by default which is a good security feature.
Change:
Now MySQL Administrator will make a connection but will likely return the following error:
It still doesn’t work but we are a bit closer. MySQL associates IP addresses and/or DNS names with users. By default, root can only connect from the localhost so we will need to fix this.
When prompted, enter your root password and then you should see an “mysql>” prompt.
The GRANT statement is granting all permission to the ‘root’ user connecting from the IP range or DNS name you have defined. If you wanted to do this by a DNS name you might do something like:
All this is well and good but usually it is bad practice to do everything with your root account so you might want to create another user. This is particularly the case if you are going to use it for connecting from PHP.
Note the IP range. This is set so that you can connect via this user from MySQL Administrator running on your remote host if you like. Depending on what you are going to do with this user, you may want to restrict the permissions further to a specific database or even specific tables. You can also restrict the user to only read. It is best practice to only give your SQL user the minimum permissions needed to the job.
Now that you are able to connect with MySQL Administrator you can close out of the command client on the server (“\q” -> Enter) and use MySQL Administrator to create your first database and table.
Well that’s it for this post. I hope someone finds it useful.