PostgreSQL

Note: Access to PostgreSQL is restricted to students with projects, and it can be accessed via special subnet (not available publicly to all students)

How to Access Your Database:

First step, you need to request the creation of a database by sending an email to accounts@cs.uwindsor.ca
Provide details about your project, supervisor, and what is the suggested name of the database and include a list of all students (if applicable).

Once CS IT confirms the DB creation, you can log in to https://psql.cs.uwindsor.ca

If it’s your first time, click on “Add New Server”

In the (Register - Server) Windows:

Name: localhost
Hostname/address: localhost
Maintenance database: Your DB name
Username: your UWINID
Password: your UWIN password
Click on "Save"

Connect to PostgreSQL from console:

Connect to a database from your application:

When connecting to a database from your application, e.g:

<?php
$pdo = new PDO(
    'pgsql:host=psql.cs.uwindsor.ca;dbname=db_name,
    'app_testuser',
    'myPassword'
);
$stmt = $pdo->query("SELECT current_user, current_database()");
print_r($stmt->fetch());
?>

The password, must be provided in the code, we DO NOT recommend to use your UWIN username and password here!
Alternatively, create a custom username/role for the application. (as explained in the next steps)

Create New username/role for your application (Using the web interface):

Login to https://psql.cs.uwindsor.ca
Right click on "Login/Group Roles" and Create a new Role
In the new windows, select the username (always start with app_):
In the "Privileges" tab, you must enable "Can Login"
In the "Membership" tab, add "app_users" (this step is to allow this new user to login remotely)
Then, we need to grant this new user, access/privileges on your database. right click on your database, and select "Properties":
Make sure you are the owner of the database (contact IT if you cannot change it)
In the "Security" Tab, click on the plus (+) sign, to add the new user to the database:

Create New username/role for your application (Using the command prompt):

SSH/Login to your VM
Connect to the database:
psql -h psql.cs.uwindsor.ca -U username-d database
Verify you are the owner of the database:
```
SELECT datname, pg_catalog.pg_get_userbyid(datdba)
FROM pg_database
WHERE datname = 'database';  
```
If you are not the owner, contact CS IT and ask them to change the owner of the database.
Create a new application role/username:
CREATE ROLE app_testing LOGIN PASSWORD 'password';
Grant the new username/role access to app_users (so you can login with the new username):
GRANT app_users TO app_testing;
Grant the new username access to your database:
GRANT ALL ON DATABASE "az-testsis" TO app_testing;

Now you can use the new username and password to access your database from your web application.