How to add a Redshift or PostgreSQL user with access to one table

Sometimes you'll want to want to add a user to a Redshift or PostgreSQL database that can only access a specific table. Here's how to do that.

Create a new user

Do this logged in as a database superuser.

CREATE USER bigscience PASSWORD 'sjjyFl1Gyi5WxU8xnwf5eu35mSUN2QyE';

Grant access to a table

GRANT SELECT ON TABLE schemaname.tablename TO bigscience;

Log in as the new user

The new connection string will look like this:

postgresql://bigscience:sjjyFl1Gyi5WxU8xnwf5eu35mSUN2QyE@dbhost:dbport/dbname