Shulou(Shulou.com)06/01 Report--

Due to the requirements raised by the developer:

(1) create a readonly user on the instacne of multiple PostgreSQL, only have select permission for tables or views in the database (including materialized views), and also have select permission for new tables and views in the future. We know that PostgreSQL

For the newly created table under schema, we need to use grant select... for an existing user that will not automatically grant the permission to select. It's troublesome to execute it manually. We can't empower it every time we create a new table. We should know that we have a lot of examples, and we can't spend all our time on such meaningless things. Besides, we can't always supervise which PostgreSQL has new tables, so we need to authorize future tables in advance. Check the PostgreSQL website and find the command alter default privileges.... This function can be realized.

(2) alter default privileges does not have the function to authorize existing tables and views, so if you want to give weights to both present and future objects, you need to use grant select to weight existing tables.

(3) because there are so many db/schenma to be executed, it is not realistic to execute one command at a time, so we need to write scripts and execute them in batch.

(4) you can refer to the testing process for specific implementation:

Https://blog.51cto.com/darrenmemos/2086198

The script is as follows:

#! / bin/ksh-x # Name: postgreSQL_grant_readonly_privileges.sh# Location:# Function: PostgreSQL grant readonly privileges# Author:# Create Date:# update Date:####/usr/local/pgsql/bin/psql-d postgres-Q-t -c "select datname from pg_catalog.pg_database where datname not in ('postgres' 'template1','template0') "| grep-v" ^ $"> / tmpb_list.logwhile read db_namedo / usr/local/pgsql/bin/psql-d ${db_name}-Q-t-c" select schema_name from information_schema.schemata where schema_name not in pg_catalog','information_schema','pg_toast','pg_temp_1','pg_toast_temp_1') | | grep-v "^ $" > / tmp/schema_list.log while read schema_name do / usr/local/pgsql/bin/psql-d ${db_name}-Q-t-c "grant select on all tables in schema ${schema_name} to readonly;" / usr/local/pgsql/bin/psql-d ${db_name}-Q-t-c "grant usage on schema ${schema_name} to readonly "/ usr/local/pgsql/bin/psql-d ${db_name}-Q-t-c" alter default privileges in schema ${schema_name} grant select on tables to readonly; "done < / tmp/schema_list.logdone < / tmp/db_list.logexit 0

It can then be executed in batches on the server.

Reference link:

Https://www.postgresql.org/docs/9.3/static/sql-grant.html

Https://www.postgresql.org/docs/9.4/static/sql-alterdefaultprivileges.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.