Example Analysis of user Security configuration in postgresql

Editor to share with you the example analysis of user security configuration in postgresql, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's learn about it!

I. configuration instructions:

Role name: user01

1.user01 permission description:

Connect to test database, but cannot connect to other databases without authorization

In the specified schema, create\ View\ delete tables and view objects in schema

two。 Other ordinary users:

Unable to connect to test database and objects in schema (sales), authorization is required

Second, the operation steps:

Instance (instance level): role (role)

$psql postgres

Postgres=# CREATE ROLE user01 LOGIN NOSUPERUSER NOCREATEDB

2.database (database level): CREATE (schemas) and CONNECT (database)

$psql postgres

Postgres=# CREATE DATABASE test

Postgres=# REVOKE ALL ON DATABASE test FROM public;-prohibit other ordinary role from connecting to test

Postgres=# GRANT CONNECT ON DATABASE test TO user01

3.schema (Architectural level):

CREATE (put object into schema)

USAGE (allow us to actually look into a schema and see which objects are present).

$psql test

Test=# REVOKE ALL ON SCHEMA public FROM public;-- other ordinary role cannot connect to test

Test=# CREATE SCHEMA sales

CREATE SCHEMA

Test=# GRANT CREATE,USAGE ON SCHEMA sales TO user01;-user01 can create and view objects in sales

GRANT

Test=#\ Q

$psql test-U user01

Test= > CREATE TABLE sales.t_sales (id int4)

CREATE TABLE

4.table (table level): grant

Default user01 creation, drop table and DML table contents

5.column (column level): grant

Default user01 creation, drop table, and DML column content

The above is all the contents of the article "sample Analysis of user Security configuration in postgresql". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!