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

Source: Internet
Author: User
Tags postgresql psql

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.