Password-based PostgreSQL Users

The CSL has a facility for users to request password-based users on postgres.cs.wisc.edu. This is offered for a few reasons:

• Several applications assume the use of passwords to connect to the database server. The CSL normally uses Kerberos authentication to connect, which few applications support out-of-the-box. Doing this allows users to deploy applications with much less modification.
• User settings can be altered without affecting normal database logins. This allows for greater flexibility in applications and is more in line with what some applications expect.
• We can now offer the use of the database server to environments and interfaces that do not support Kerberos, such as stock PostgreSQL and the JDBC.
• These users can also act as groups, allowing for easier collaboration on projects. Details and caveats are given below.

Requesting a user

To request a user, fill out the Request a Password-Based PostgreSQL User webform . A request will be sent to the CSL and you will be notified when it is completed.

Usernames may have letters, numbers, dashes, or underscores. Also, in order to not conflict with normal usernames, they are required to be at least 9 characters long.

Using the user

When a user is created, the person who requested is granted access in that user. What this means is that this user can log in normally as themselves, become the new user, and act as them or manage them. This includes being able to reset their password.

For instance, suppose the user bbadger requests a user named bbadger-project to work in the database bbadger_db:

$ /s/postgresql/bin/psql -h postgres.cs.wisc.edu -p 5432 bbadger_db
...
=> select current_user;
current_user
--------------
bbadger (1 row)

The user is bbadger. To become the user bbadger-project issue the following command. Please note that the quotes are important in the following example because of the dash in the user name:

=> set role to "bbadger-project";
SET => select current_user;
current_user
-----------------
bbadger-project (1 row)

Now the user is bbadger-project, and commands can be issued as bbadger-project, including setting/resetting its password.

=> alter user "bbadger-project" password '[password]';
ALTER ROLE

Connecting as the passworded user

After setting the user’s password, you can connect normally as that user and run commands as that user - you just have to add user and/or password arguments to the normal ways to connect.

Using Java and the JDBC

One of the primary advantages of offering password-based users is allowing people to develop and use Java applications with the CSL PostgreSQL server.

Before running the program, in order to find the PostgreSQL JDBC libraries, you need to set your CLASSPATH environment variable to include /s/postgresql/java/postgresql.jar. For example, you can run this command or add it to your .bashrc.local or .cshrc.local file:

export CLASSPATH=/s/postgresql/java/postgresql.jar:$CLASSPATH:. (bash/ksh) setenv CLASSPATH /s/postgresql/java/postgresql.jar:$CLASSPATH:. (tcsh/csh)

Here’s a simple example program showing how to connect using the JDBC.

import java.sql.*; // import the JDBC
import java.util.*;
public class Jdbc {
  public static void main (String[] args) {
    try {
      Class.forName("org.postgresql.Driver"); // Load the PostgreSQL JDBC driver
      // Connect to the database, the SSL lines are required to connect.
      Properties props = new Properties();
      props.setProperty("user", "bbadger-project");
      props.setProperty("password", "[password]");
      props.setProperty("ssl", "true");
      props.setProperty("sslfactory", "org.postgresql.ssl.NonValidatingFactory");
      Connection conn = DriverManager.getConnection("jdbc:postgresql://postgres.cs.wisc.edu:5432/bbadger_db", props);
      Statement st = conn.createStatement();
      ResultSet rs = st.executeQuery("select col1 from my_table");
      while (rs.next()) {
        System.out.print("Row returned: ");
        System.out.println(rs.getString(1));
      }
      // close up shop
      rs.close();
      st.close();
      conn.close();
      } catch (Throwable ex) {
        System.err.println("Uncaught exception in main...");
        ex.printStackTrace();
    }
  }
}

More information can be found at the following locations:

• JDBC Overview
• PostgreSQL JDBC Documentation

Using the user as a group

PostgreSQL users can double as groups. By granting and revoking membership in a particular user, you can give another user the same permissions as that user. You can then manage permissions as you would with any other group mechanism and collaborate without managing a horribly large list of permissions or sharing passwords.

You can achieve this by using the GRANT and REVOKE SQL statements.

GRANT "bbadger-project" TO bucky;

Now the user bucky is a member of this user/group and has the privilege to run queries with the same permissions as bbadger-project, and you didn’t have to add another user to your table’s permissions.

REVOKE "bbadger-project" FROM bucky;

bucky is no longer a member of this user/group and can no longer use the same permissions as bbadger-project. bucky will still be able to access any database objects with permissions granted directly to him or other groups he is a member of.

Caveats and warnings

By granting permission in a user, the grantee gains privileges of that user. This includes the ability to SET their role and run commands as that user, or to run commands with the same permissions of that user. If a user has DELETE privileges on a table, any member of that user can delete data from a table or remove said table. Any member can also GRANT or REVOKE privileges on database objects by setting their role to that user.

Moreover, if you include WITH GRANT OPTION when you GRANT membership, you then allow that member to manage membership in said user. They can then in turn GRANT membership to others without your consent, or they could REVOKE your membership and cause you to lose the ability to properly manage your user.

This is a very powerful tool, but it comes with some rather significant gotchas. Keep in mind that whoever requests the role will be considered responsible for its usage. Please be careful about how and to whom you grant membership privileges.

CSL Policy

Group privileges and policies may only be used with password-based users. For ease of management and a proper separation of these two sets of users, normal logins/Kerberos-based users are not allowed to double as groups.

A nightly script will enforce this policy.

What about my normal user and login?

Your normal database login to postgres.cs.wisc.edu is still handled by Kerberos. You do not need to set or use a password to connect.

Keep in mind that your normal login cannot be used as a group per above, and that use of the JDBC and other non-Kerberos-aware interfaces will not work with your normal username and login.