Details on using foreign data wrapping to access WRDS

wrds_fdw.md

Issues

Some issues I see with using the WRDS PostgreSQL server include:

1. Too many schema and tables

• Added complexity for the user.

2. No ability to create tables.

• So there's no way to store results of long-running queries.
• No ability to use compute() with dplyr.

3. Difficult to combine WRDS data with large datasets.

• My paradigmatic case is StreetEvents, which I have in a nicely structured form in PostgreSQL. If I want to merge conference call data with, say, CRSP to get announcement returns, I really want the data in "one place" (i.e., accessible from one database).

Possible solution

One possible solution is foreign data wrappers. These create "foreign tables" in the local server that represent tables in the WRDS database. The steps to do this are as follows:

1. Install the postgres_fdw extension using CREATE EXTENSION.

CREATE EXTENSION postgres_fdw;

2. Create a foreign server object, using CREATE SERVER, to represent each remote database you want to connect to. Specify connection information, except user and password, as options of the server object.

CREATE SERVER wrds
   FOREIGN DATA WRAPPER postgres_fdw
   OPTIONS (host 'wrds-pgdata.wharton.upenn.edu', dbname 'wrds', port '9737');

3. Create a user mapping, using CREATE USER MAPPING, for each database user you want to allow to access each foreign server. Specify the remote user name and password to use as user and password options of the user mapping.

CREATE USER MAPPING FOR igow SERVER wrds OPTIONS (user 'iangow', password 'XXXXXXX');

4. Create a foreign table, using CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA, for each remote table you want to access. The columns of the foreign table must match the referenced remote table. You can, however, use table and/or column names different from the remote table's, if you specify the correct remote names as options of the foreign table object.

CREATE SCHEMA ibes_wrds;

IMPORT FOREIGN SCHEMA ibes
    FROM SERVER wrds INTO ibes_wrds;

Could this approach be used by a school wanting to set up a local database with a fairly lightweight version of WRDS? Not quite. Each user on the local database would need to have a USER MAPPING and that user mapping would have to include the WRDS password. While I believe users would be able to create their own user mappings, the password is stored in the database in raw form. While only database admins can see this, I think this is a bit of a weak link (a view expressed more pessimistically here).

One workaround is to create one user mapping for a database admin with a WRDS ID and access to the tables in question and use views to give access to other users. For example, I could do this:

CREATE VIEW ibes.det_guidance
AS SELECT * FROM ibes_wrds.det_guidance;

GRANT SELECT ON VIEW ibes.det_guidance TO crsp_basic;

Now users with crsp_basic (e.g., GRANT crsp_basic TO some_user;) have access to ibes_wrds.det_guidance without needing a custom user mapping.