PostgreSQL implements read-only tables

the implementation of a read-only table in PostgreSQL can be divided into two parts: the existing tables in the database before the command operation, and the newly created tables of the database after the command operation. In this article, we will implement the two parts separately to achieve a complete read-only query.

first, the initial environment

Database: PostgreSQL 9.3

Users: User1, User2

Mode: SHEMA1 (owner is user1)

Table: shema1.t1 (id int)

Ii. Existing Tables

? for the existing table SHEMA1.T1 implementation of User2 read-only access, respectively, need to pass: access to SHEMA1 mode, the T1 table, select access rights. The operation is as follows:

To execute a command as a User1 user or Superuser:

grant usage on the schema shema1 to User2;

In PostgreSQL, the permissions on the schema are: Create and usgae two, the usage permission indicates that the user can access the pattern, and query the schema for those objects; Create permission indicates that the user can create objects in this mode. Only the User2 user is required to have access to SCHEMA1 mode, and if this permission is not given, the User2 user will be able to make an error in the query when the permissions on the schema are insufficient.

Then, continue to execute the command:

Grant Select on all tables in the schema schema1 to User2;

This command gives the User2 user the SELECT permission for all tables that are now in SHEMA1 mode.

these two commands ultimately enable User2 users to read-only access to all tables that are now present in SCHEMA1 mode. ?

iii. New Tables

User2 User's read-only access to the newly created table in User1 also needs to be divided into two parts: the permissions of the schema schema, and the SELECT permission of the newly created table.

The permissions on SCHEMA1 can be referred to above, and no longer mentioned here; Select permissions for the post-new table are implemented by the alter default privileges command (which is new after 9.0).

The specific commands are as follows:

alter Defualt privileges in the schema SHEMA1 grant select on tables user2;

This command enables the User2 user to select Read-only access to the User1 new table after the command.

Iv. PostScript

This two-part operation ultimately enables User2 users to read-only access to all tables in SHEMA1 mode for User1 users.

For the default (where all users are using the public mode), you can skip permission actions on the Shema mode.

PostgreSQL implements read-only tables