In the actual production environment, we often encounter this situation: because of the needs of business scenarios, some important business data tables in this department need to be given to other departments to view permissions. Due to business expansion and adjustment, more table query permissions may be required in the later stage. To solve this kind of business needs, we can use the method of creating views to solve it, and we can already meet the needs by creating shadow users. This article mainly introduces the creation and authorization methods of shadow users.
Scenario 1: Only use on schema permissions are granted
session 1:
--Create a readonly user and assign the test mode to the readonly user.
postgres=# create user readonly with password 'postgres'; CREATE ROLE postgres=# grant usage on schema test to readonly; GRANT postgres=# \dn List of schemas Name | Owner -------+------- test | postgres
session 2:
--Login readonly users can query all existing tables in test mode.
postgres=# \c postgres readonly You are now connected to database "postgres" as user "readonly". postgres=> select * from ; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+------------+---------+---------+-------- 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 7788 | test | ANALYST | 7566 | 1982-12-09 | 3000.00 | | 20 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | | 20 1111 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 (14 rows)
Switch to session 1 to create a new table t1
postgres=# create table test.t1 as select * from ; CREATE TABLE
Switch to session 2 readonly user, the t1 table cannot be queried
postgres=> select * from test.t1 ; 2021-03-02 15:25:33.290 CST [21059] ERROR: permission denied for table t1 2021-03-02 15:25:33.290 CST [21059] STATEMENT: select * from test.t1 ; **ERROR: permission denied for table t1
Conclusion: If you only grant usage on schema permissions, readonly can only view tables and objects that already exist in test mode. A new table created after granting usage on schema permissions cannot be viewed.
Scenario 2: After granting usage on schema permissions, then grant select on all tables in schema permissions
session 2 **ERROR: permission denied for table t1 error handling
postgres=> select * from test.t1 ; **ERROR: permission denied for table t1
session 1: Grant readonly user select on all tables permissions
postgres=# grant select on all tables in schema test TO readonly ;
session 2: readonly user query t1 table
postgres=> select * from test.t1; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+------------+---------+---------+-------- 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 7788 | test | ANALYST | 7566 | 1982-12-09 | 3000.00 | | 20 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | | 20 1111 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 (14 rows)
session1: Create a new table t2 in test mode of postgres user
postgres=# create table test.t2 as select * from ; SELECT 14
session 2: readonly user query t2 table permissions are insufficient
postgres=> select * from test.t2 ; ERROR: permission denied for table t2
session 1: Give grant select on all tables again
postgres=# grant select on all tables in schema test TO readonly ;
session 2: readonly users can view the T2 table again
postgres=> select * from test.t2 ; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+------------+---------+---------+-------- 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 7788 | test | ANALYST | 7566 | 1982-12-09 | 3000.00 | | 20 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | | 20 1111 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 (14 rows)
Shadow user creation
If you want to make readonly readonly users not to manually assign grant select on all tables in schema test TO readonly permission every time the postgres user creates a new table in test mode. You need to grant default access to test, which will also take effect for newly created test mode.
session 1: In the future, access all newly created tables in test mode to create a t5 table.
postgres=# alter default privileges in schema test grant select on tables to readonly ; ALTER DEFAULT PRIVILEGES postgres=# create table test.t5 as select * from ; CREATE TABLE
session 2: Query readonly user
postgres=> select * from test.t5; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+--------+-----------+------+------------+---------+---------+-------- 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | | 20 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | | 30 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 7788 | test | ANALYST | 7566 | 1982-12-09 | 3000.00 | | 20 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100.00 | | 20 1111 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 (14 rows)
Summary: Steps to Shadow User Creation
--Create shadow users create user readonly with password 'postgres'; --WillschemamiddleusagePermissions are granted toreadonlyuser,Access all existing tables grant usage on schema test to readonly; grant select on all tables in schema test to readonly; --Future visitstestAll newly created tables in mode alter default privileges in schema test grant select on tables to readonly ;
This is the end of this article about postgresql shadow user practice. For more related postgresql shadow user content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!