We offer access to both MySQL and postgreSQL database servers for all Andrew users. These allow for much more interesting CGIs, such as forums.
To make use of this service, you must have login information and a database created for you on these servers, which is possible through My Contrib. The name of the database on both servers will be your andrewID with the prefix contrib_. You will have two database accounts, one with the same username as your andrewID and another with the added suffix _cgi. The primary (no suffix) database account will have full access to your database, while the latter lacks these administrative permissions. See the below section Admin User vs. CGI User for additional information.
| MySQL Server: | contrib-mysql.club.cc.cmu.edu:3306 |
| PostgreSQL Server: | contrib-postgres.club.cc.cmu.edu:5432 |
| Admin Username: | foo |
| CGI Username: | foo_cgi |
| Database Name: | contrib_foo |
Both of the SQL servers can be accessed from any campus IP address, but are blocked from external access. In order to create tables or manipulate the permissions of the CGI user on the database, you will need to use a standalone client for the respective server. See http://www.mysql.com or http://www.postgresql.org to find clients for your operating system. To keep things simple for everyone, the only features offered by My Contrib will be those functions that you cannot perform with an SQL client. Every other administrative task should be done using one.
Your "admin" user (the database username without the _cgi prefix) has full rights to your database, including the ability to create and drop tables. You may even have the permissions required to drop the entire database itself. This isn't fatal if accidentally done since you can always recreate your databases through My Contrib, but some care in dropping either tables or the database must be exercised because this action isn't undoable (and we do not offer specific assurances of a backup schedule on our part).
On the MySQL server, this admin user does not have the permission to grant any rights to your database to any other user, including the CGI user (see below for a complete explanation of the rights the CGI user has). We are forced to enforce this due to limitations in the MySQL permissions system.
On the MySQL server, the CGI user has significant but not overly destructive rights on your database, including such things as being able to select, insert, update, and delete from any table (these are inherited by newly-created tables) but not such rights as the ability to create or drop tables. This offers limited protection in the event a CGI script running under your user is compromised (no tables can be dropped, but they could be emptied via a delete command).
On the PostgreSQL server, the admin user does have the permission to grant rights (including the ability to create and drop tables) to any other user present in the database server's users list (this includes your CGI user along with any other andrew user that has created an SQL database). If you find this ability useful, feel free to exercise it.
On the PostgreSQL server, the CGI user has no rights to your database at all by default. Using your admin user, you must grant the appropriate permissions to your CGI user on a per-table basis. For simplicity, we would prefer to give the CGI user typical permissions on the entire database as is done on the MySQL server, but no permissions can be specified for a database as a whole in PostgreSQL; rather, only on tables. This makes life somewhat harder for you (you must manually grant permissions), but it simultaneously offers the opportunity for the granting of more finely-grained permissions to the CGI user. For instance, it is possible in SQL to grant a user specific permissions on a specific column of a given table, with differing (or no) access to other columns.