Case: a read-only role directly has read-only access to newly created tables under a schema without separate authorization.

Case: a read-only role directly has read-only access to newly created tables under a schema without separate authorization.

First of all, you need to know that the newly created table under the default schema of PostgreSQL will not have the permission of select for an ordinary user, so I need to modify the default permission of PostgreSQl to give select to ordinary users in advance. I need to use the command alter default privileges.

Session1 (user:postgres):

# psql

Create role role1 password '123456' login

Create schema schema1

Grant usage on schema schema1 to role1

Testdb=#\ dn+ schema1

List of schemas

Name | Owner | Access privileges | Description

-+-

Schema1 | postgres | postgres=UC/postgres+ |

| | role1=U/postgres |

Alter default privileges in schema schema1 grant select on tables to role1

Testdb=#\ ddp+

Default access privileges

Owner | Schema | Type | Access privileges

-+-

Postgres | schema1 | table | role1=r/postgres

Create table schema1.t1 (C1 int,c2 varchar (10))

Insert into schema1.t1 values (1 recorder aaaa')

Testdb=#\ dp+

Access privileges

Schema | Name | Type | Access privileges | Column privileges | Policies

-+-

Schema1 | T1 | table | postgres=arwdDxt/postgres+ | |

| | role1=r/postgres | | |

Session2 read_only:

# psql-h 172.16.101.54-p 5432 testdb role1

Testdb= > select * from schema1.t1

C1 | c2

-- +-

1 | aaa

(1 row)