The Chinese version of redash takes PostgreSQL as an example to set user permissions

Time:2021-7-26

As a data visualization software, users use the Chinese version of redash more to find and analyze the data source without editing and changing the data content. When establishing the data source, configuring a database user with permission control can better ensure data security and avoid data loss caused by user misoperation. Next, take the PostgreSQL data source as an example to demonstrate how to configure the read-only data mode.

First, create a new database named business and the owner is admin. Create three tables employees, jobs and customers in the database and fill in the data. The database administrator can control all permissions of public users. Directly revoking the permissions of public roles will affect all current users and roles. Therefore, in the actual architecture, the processing method is generally to set some roles with read-only permissions in the database, and the system also retains other roles with different permissions.

1. Create read-only role readonly

CREATE ROLE readonly;
GRANT CONNECT ON DATABASE defaultbusiness TO readonly;
GRANT USAGE ON SCHEMA business TO readonly; 

In the operation of PostgreSQL, in the operation of PostgreSQL, the configuration only allows access to the objects contained in the usage mode, so the lookup function in the read-only mode will not be affected.

2. Grant new role permissions

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

The first two statements grant full read permission to the employees and jobs tables.

The third statement restricts the readonly role to only view the ID and name of the customers table, all other fields in the table are hidden, and the data of other customers information fields can only be viewed by the administrator, so as to ensure that other users in the system will not intentionally see the data. If the user tries to execute select * from business, the database will cause a permission error, and only the query ID and name fields can be displayed.

3. Create a database named redash_ User is a read-only user, which is used to connect to the Chinese version of redash

CREATE USER redash_user WITH PASSWORD 'secret';
GRANT readonly TO redash_user; 

When adding a data source, the user name redash needs to be filled in the setting interface_ User, “secret” indicates the database password. A strong password can be set here to replace it.

4. Connect to Postgres with a new read-only user

It’s easy to connect to the Chinese version of redash. Just provide the host name, port, database user name and password. An example is as follows.

The Chinese version of redash takes PostgreSQL as an example to set user permissions

Next, we can execute some queries to check the permissions:

SELECT * FROM business.employees

The Chinese version of redash takes PostgreSQL as an example to set user permissions

Can return data.

Proceed as follows:

INSERT INTO business.employees(name) VALUES (‘Hal’)

The Chinese version of redash takes PostgreSQL as an example to set user permissions

Permission error! The readonly role does not allow inserting data.

Execute the query from the customers table: select * from business.customers;

The Chinese version of redash takes PostgreSQL as an example to set user permissions

A permission error was returned because a read-only role can only access specific columns.

Execute: select id, name from my.customers;

The Chinese version of redash takes PostgreSQL as an example to set user permissions

After execution, it is found that the specified data can be queried, because we only requested the column names that the read-only user has the right to query.