PostgreSQL authorizes existing, newly created tables and views to the user

due to development requirements:

(1) Create a readonly user on multiple PostgreSQL instacne, only have SELECT permission on the table or view (including materialized view) in the database, and have SELECT permission for the newly created table and view. We know PostgreSQL.

    New table under schema, for an already existing user does not automatically give the SELECT permission, we need to use the grant SELECT ... Manual to execute, so it is troublesome, always can not create a new table every time, we have to empower once, we have a lot of examples, we can not put the time in such a thing without       meaning, and besides, we can't always monitor what PostgreSQL has a new table , so we need to pre-authorise the future table, check out the PostgreSQL website and find the command alter default privileges ... This function can be implemented.

(2) Alter default privileges does not have the ability to authorize existing tables and views, so you want to empower both present and future objects, and you will need to use the grant Select to assign the available tables.

(3) because there are so many Db/schenma to execute, a single command is less realistic and requires scripting and batch execution.

(4) How to achieve the specific implementation can refer to the test process:

http://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 isn't in (' Postgres ' , ' template1 ', ' template0 '); "| Grep-v "^$" >/tmpb_list.log While read db_name Do /usr/local/pgsql/bin/psql-d ${db_name}-q-t-C "Select Schema_name from Information_schema.schemata where Schema_name no T 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.log Done </tmp/db_list.log Exit 0

It can then be executed in batches on the server.

Reference Links:

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

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

PostgreSQL authorizes existing, newly created tables and views to the user