One of the first things you see in our documentation is advice to configure your data sources with read only users. We recommend it because we meant Redash to visualize data. It's not built around INSERT, UPDATE, or DELETE actions.

However, because Redash supports 40+ data sources and an unlimited number of JSON based APIs, the application can't directly prevent users from running non-SELECT queries. You can ensure that Redash users do not emit harmful DDL statements by configuring read only users at the database level.

PostgreSQL is one of our most popular data sources. This article gives an example to configure read-only access in any Postgres data source, including Amazon Redshift and RDS.

This article draws heavily on Amazon's excellent AWS blog post about Postgres permissions.

Overview

Before starting, I created a new database schema called myapp owned by a user named app-admin. This schema includes tables for Employees, Jobs and Customers filled with dummy data. I'll follow these steps:

Create a new role called myapp-readonly.
Grant it SELECT permissions on the Employees and Jobs tables. Grant it narrowSELECT privileges on the customers table to preserve customer privacy.
Create a user called redash and add it to the myapp-readonly role.
Add a data source to Redash with the new redash username and password.

Remember as we go through this example that Amazon encourages DB admins to revoke permissions on all schemas from the PUBLIC role with the following note:

Revoking permissions from the public role impacts all existing users and roles. Any users and roles that should be able to connect to the database or create objects in the public schema should be granted the permissions explicitly before revoking any permissions from the public role in the production environment.

For simplicity I don't do that here. In the steps that follow we can guarantee that the myapp-readonly role is properly restricted. But there may be other roles in your environment with more volatile permissions. I encourage you to fully audit your database permissions (whether you use Redash or not 😀).

Step 1: Create a read only role

CREATE ROLE myapp_readonly;
GRANT CONNECT ON DATABASE defaultdb TO myapp_readonly;
GRANT USAGE ON SCHEMA myapp TO myapp_readonly;

These steps come straight from Amazon's example. The GRANT USAGE statement is important because without it other permissions don't work. From PostgreSQL's documentation:

For schemas, [USAGE] allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema.

Step 2: Grant permissions to the new role

GRANT SELECT ON TABLE "myapp"."employees" TO myapp_readonly;
GRANT SELECT ON TABLE "myapp"."jobs" TO myapp_readonly;
GRANT SELECT (id, name) ON TABLE myapp.customers TO myapp_readonly;

The first statement gives full read permission on the employee and jobs tables.

The second statement explicitly allows the myapp_readonly role to view only customer ID's and names. All other fields on the table are hidden. This includes fields for credit card numbers and contact information. That data should only be seen by admins, not Redash users.

This rule ensures that my Redash users don't see that data, intentionally or not. If a user attempts to SELECT * FROM customers the database will raise a permissions error. And only the ID and name fields will appear in the schema browser.

Step 3: Create user for Redash and assign it the read only role

CREATE USER redash WITH PASSWORD 'secret';
GRANT myapp_readonly TO redash;

The redash user is what we will plug in to the data source setup screen in Redash. You should substitute 'secret' with a strong password.

Step 4: Connect to Postgres with new read only user

Connecting Redash is simple. Just provide the host name, port, and database user redash that I just created.

Now let's run a few queries to see how well the permissions are working.

SELECT * FROM myapp.employees

Returns data as expected.

INSERT INTO myapp.employees (name) VALUES ('Hal')

Raises a permissions error from the database. The myapp_readonly role does not allow INSERTS. And Redash isn't meant for performing INSERT statements anyway!

Finally, selecting from the customers table:

SELECT * FROM myapp.customers;

Returns a permissions error because the read only role can only access specific columns.

SELECT id, name FROM myapp.customers;

Returns the full customer list since we requested only those columns to which the read only user has access.

Conclusion

Data is one of the most important assets for your business. Redash encourages you to use the security controls of your database to protect it. Taking measures like these ensures that your internal users can prepare useful insights while keeping sensitive information safe from compliance missteps or prying eyes.

Setting up Read Only Users in PostgreSQL