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 'Tr3NnfwM4z2kIkREuCKEz21ejRXTnJuS';

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:Tr3NnfwM4z2kIkREuCKEz21ejRXTnJuS@dbhost:dbport/dbname