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
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.
Before starting, I created a new database schema called
myapp owned by a user named
app-admin. This schema includes tables for
Customers filled with dummy data. I'll follow these steps:
- Create a new role called
- Grant it
SELECTpermissions on the
Jobstables. Grant it narrow
SELECTprivileges on the
customerstable to preserve customer privacy.
- Create a user called
redashand add it to the
- Add a data source to Redash with the new
redashusername 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
publicrole 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
publicrole 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;
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
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.
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.