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 theEmployees
andJobs
tables. Grant it narrowSELECT
privileges on thecustomers
table to preserve customer privacy. - Create a user called
redash
and add it to themyapp-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 thepublic
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 thepublic
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.