Setting up read-only users in PostgreSQL

This tutorial shows how to set up read-only users in PostgreSQL for Redash.

The first thing you see in our documentation is advice for configuring data sources for read-only users. We recommend this because we meant Redash for data visualization. It is not built on INSERT , UPDATE or DELETE actions .

Since Redash supports over 40 data sources and an unlimited number of JSON-based APIs, the application cannot directly prevent users from running queries other than SELECT. By doing this, you can protect yourself against Redash users from running malicious DDL statements by configuring read-only users at the database level.

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

This article is largely written on the basis of excellent post Amazon AWS Blog About permissions Postgres.

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 followed these steps:

1. Created a new role named myapp-readonly .
2. Grant her SELECT permission on Employees and Jobs tables . Grant him SELECT privileges on the customers table to keep customers confidential.
3. Created a user named redash and added it to the myapp-readonly role .
4. Added datasource to Redash with new username and password redash .

Remember, when we look at this example, Amazon recommends DBAs to revoke permissions for all schemas from the PUBLIC role with the following note:

public . , , , - public .

. , myapp-readonly . . ( , Redash ).

1.

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Amazon. GRANT USAGE , . PostgreSQL:

[USAGE] , ( , ). , «» .

2.

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;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

employee jobs .

myapp_readonly . . . , Redash.

, Redash . *SELECT FROM customers**, . .

3. Redash .

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

redash — , Redash. secret .

4. Postgres

Redash . , , .

, , .

SELECT * FROM myapp.employees
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

, .

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

. myapp_readonly INSERTS. Redash INSERT!

, customers:

SELECT * FROM myapp.customers;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Returns a permission error because the read-only role can only access certain columns.

SELECT id, name FROM myapp.customers;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
    

Returns a complete list of clients since we only requested read-only columns for the user.

Conclusion

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