Create read-only user action in postgresql database

Create read-only users in the pg database can be used as follows. The general implementation is to assign relevant permissions of a specific schema to read-only users.

--Create a user
CREATE USER readonly WITH ENCRYPTED PASSWORD '123456';
--Set the user to enable read-only transactions by default
ALTER USER readonly SET default_transaction_read_only = ON;
--WillschemamiddleusagePermissions are granted toreadonlyuser,Access all existing tables
GRANT usage ON SCHEMA xyh TO readonly;
--Willschemamiddle表的查询Permissions are granted toreadonlyuser,Access all existing tables
GRANT SELECT ON ALL tables IN SCHEMA xyh TO readonly;
--Future visitsxyhAll newly created tables in mode：
ALTER DEFAULT privileges IN SCHEMA xyh GRANT SELECT ON tables TO readonly;

Supplement: Postgresql creates read-only users and opens permissions for some tables

The inspection platform developed by our team assists users in routine operations for two years. The platform stores about 300TB of unstructured data.

At present, due to the requirements of Party A's headquarters, an unstructured data storage platform is required to extract all unstructured data from our platform, so that they can intelligently process the unstructured data.

We use the postgresql database. For this reason, we need to create users with read-only permissions for unstructured platform manufacturers and open some unstructured tables to them to facilitate their data extraction.

In the postgresql database, the method of creating read-only users and opening permissions for partial tables is as follows:

1. Create role information

The role name is poss_sc

CREATE ROLE poss_sc;

2. Grant access permissions

poss is the pattern name, poss_sc is the role name

GRANT USAGE ON SCHEMA poss TO poss_sc;

3. Set permissions to access the specified table

Grant query permissions for tables related to unstructured data to related roles

GRANT SELECT ON poss.dm_image,poss.dm_defect,poss.dm_result_data,poss.dm_hidden_trouble TO poss_sc;

4. Create a logged-in user (with expired parameter settings) and associate it with the role

The login user name is poss_sc_user, and the expiration time is 2019-11-30 23:00;

CREATE USER poss_sc_user WITH PASSWORD 'Poss_sc_$%^' VALID UNTIL '2019-11-30 23:00';
GRANT poss_sc TO poss_sc_user;

The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.