PostgreSQL User Security Configuration

First, configuration instructions:

Role Name: User01

1.user01 Permission Description:

Connect to test database, cannot connect to other database when unauthorized;

In the specified schema, create \ View \ Delete The table to view the object in the schema;

2. Other Ordinary users:

Cannot connect object in test database and schema (sales), need authorization;

Second, the operation steps:

1. Instance (instance level): Role (roles)

$ psql Postgres

postgres=# CREATE ROLE User01 LOGIN nosuperuser nocreatedb;

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

$ psql Postgres

postgres=# CREATE DATABASE test;

postgres=# REVOKE all on the DATABASE test from public; --Prohibit other common role connection test

postgres=# GRANT CONNECT on the DATABASE test to User01;

3.schema (Schema level):

CREATE (Put object into schema)

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

$psql Test

test=# REVOKE All on the SCHEMA public from public;--Other common role cannot connect test

test=# CREATE SCHEMA Sales;

CREATE SCHEMA

test=# GRANT create,usage on the 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 content

5.column (Column level): Grant

Default User01 creation, drop table, and DML column contents

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1871200

PostgreSQL User Security Configuration