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

#!/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 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