Skip to main content

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 


    image.png

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

    image.png


  • 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" 

      image.png

      image.png
Connect to PostgreSQL from console:
  • Login/SSH to your project’s VM, then issue this command:
    psql -h psql.cs.uwindsor.ca -U username -d db_name

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

    image.png

  • In the new windows, select the username (always start with app_): 

    image.png


    image.png


  • In the "Privileges" tab, you must enable "Can Login" 

    image.png


  •  In the "Membership" tab, add "app_users" (this step is to allow this new user to login remotely)

    image.png

     
  • Then, we need to grant this new user, access/privileges on your database. right click on your database, and select "Properties":

    image.png

  • Make sure you are the owner of the database (contact IT if you cannot change it)

    image.png

  • In the "Security" Tab, click on the plus (+) sign, to add the new user to the database:

    image.png

    image.png

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.

Back to top