This article has participated in the good article call order activity, click to see: back end, big front end double track submission, 20,000 yuan prize pool for you to challenge!
Undertake above, after controlling access to the database door, the next thing to control can access the operation of the door behind the things, if you are the master of the room, of course, can operate everything in the room, but if you are just an ordinary visitor, but also let you can operate everything is not appropriate.
By default, all users can access the public mode
In PostgreSql, all new databases have a public mode by default. Users can create and manage their own objects in this mode. When creating basic objects, such as tables and views, in the database, if no mode is specified, these objects are also stored in pubILc mode by default.
Create database d1 and database D2 with public mode. postgres=# create database d1; CREATE DATABASE postgres=# \c d1; You are now connected to database "d1" as user "postgres". d1=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row) d1=# create database d2; CREATE DATABASE d1=# \c d2; You are now connected to database "d2" as user "postgres". d2=# \dn List of schemas Name | Owner --------+---------- Public | postgres - even if the owner is not public, new users can also create objects in this mode, switch to other mode, no permission, and do not specify a schema, the created object will be stored in a public mode. d1=# create user u1 password 'u1'; CREATE ROLE d1=# \c - u1 You are now connected to database "d1" as user "u1". d1=> \dn List of schemas Name | Owner --------+---------- d1s1 | postgres public | postgres (2 rows) d1=> create table t1(id int); CREATE TABLE d1=> insert into t1 values(1); INSERT 0 1 d1=> \d List of relations Schema | Name | Type | Owner --------+------+-------+------- public | t1 | table | u1 (1 row) d1=> create table d1s1.t1(id int); ERROR: permission denied for schema d1s1 LINE 1: create table d1s1.t1(id int); ^ d1=> select * from t1; id ---- 1 (1 row) d1=> select * from public.t1; id ---- 1 (1 row)Copy the codeBecause of this feature, when a business user creates an object in the production environment, if no schema is specified, objects such as tables may be mistakenly created in the PUBILC schema. To prevent this operation, you can run the following command to reclaim the permission.
d1=> \c - postgres
You are now connected to database "d1" as user "postgres".
d1=# revoke create on schema public from public;
REVOKE
d1=# \c - u1
You are now connected to database "d1" as user "u1".
d1=> create table t2(id int);
ERROR: permission denied for schema public
LINE 1: create table t2(id int);
Copy the codeWhoever is the owner has the permission to operate the object
Each database object has the concept of owner. When other users do not have additional authorization, only the owner can operate its own object except the administrator user.
The owner of the database has the permission to create a schema in the database. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- d1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | d2 | u2 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (7 rows) postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} u1 | | {} u2 | | {} postgres=# \c d2 u1 You are now connected to database "d2" as user "u1". d2=> \dn List of schemas Name | Owner --------+---------- public | postgres (1 row) d2=> create schema d2s1; ERROR: permission denied for database d2 d2=> \c - u2 You are now connected to database "d2" as user "u2". d2=> create schema d2s1; The CREATE SCHEMA d2 = > \ dn List of schemas Name | Owner + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- d2s1 | u2 public | postgres (2 rows) -- SCHEMA belongs The owner has the permission to create objects such as tables in this schema. Other users except the administrator do not have the permission. d1=# \dn List of schemas Name | Owner --------+---------- d1s1 | u1 public | postgres (2 rows) d1=# \c - u1 You are now connected to database "d1" as user "u1". ^ d1=> create table d1s1.t1(id int); CREATE TABLE d1=> insert into d1s1.t1 values(1); INSERT 0 1 d1=> \c - u2 You are now connected to database "d1" as user "u2". d1=> create table d1s1.t2(id int); ERROR: permission denied for schema d1s1 LINE 1: create table d1s1.t2(id int); Table and view are the same. Except for the administrator user, only the owner has the permission to query and modify itself, which is not described herein.Copy the codeQuery permissions
D1 => select roles from pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid ---------------------------+----------+------------+---------------+-------------+-------------+----------------+------- -------+-------------+---------------+--------------+-----------+------- pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200 pg_read_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4569 postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10 pg_write_server_files | f | t | f | f | f | f | -1 | ******** | | f | | 4570 pg_execute_server_program | f | t | f | f | f | f | -1 | ******** | | f | | 4571 pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375 pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373 syd | f | t | f | f | t | f | -1 | ******** | | f | | 16387 u1 | f | t | f | f | t | f | -1 | ******** | | f | | 41008 pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374 pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377 u2 | f | t | f | f | t | f | -1 | ******** | | f | | 41009 repmgr | t | t | f | f | t | f | -1 | ******** | | f | | 32769 (13 rows)Copy the codeAren’t users and roles the same? Why not call it a user?
This is because the concept of a role is the same as that of an Oracle role, a set of permissions that can be granted to other users.
d1=# grant pg_signal_backend,pg_read_server_files,postgres to syd,u1,u2;
GRANT ROLE
Copy the codeD1 => select * from information_schema.table_privileges WHERE grantee = 'u1'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ---------+---------+---------------+--------------+------------+----------------+--------------+---------------- u1 | u1 | d1 | d1s1 | t1 | INSERT | YES | NO u1 | u1 | d1 | d1s1 | t1 | SELECT | YES | YES u1 | u1 | d1 | d1s1 | t1 | UPDATE | YES | NO u1 | u1 | d1 | d1s1 | t1 | DELETE | YES | NO u1 | u1 | d1 | d1s1 | t1 | TRUNCATE | YES | NO u1 | u1 | d1 | d1s1 | t1 | REFERENCES | YES | NO u1 | u1 | d1 | d1s1 | t1 | TRIGGER | YES | NO (7 rows)Copy the codeAuthorized permission
{} : mandatory [] : Optional
- Additional authorization when creating a user
The create user/role user name | public [[with] option];Copy the code- A user has been created to modify attributes
The create user/role user name | public [with] option;Copy the codeThe option to
Superuser | nosuperuser: super authority, have all permissions, nosuperuser by default. Inherit | noinherit: inheriting permissions, can give other users/roles in addition to the superuser privileges inheritance, inherit by default. The login | nologin: login permissions, as a user, the default nologin, unless the create user login (the default). Password ‘password’ | null: set the password, the user password is used only for the login properties, do not use password authentication, you can omit this option, you can choose to empty password explicit written as null. Valid until ‘timestamp’ : Indicates the password validity period. If this parameter is not set, the password is valid. Createdb | nocreatedb: build library privileges, nocreatedb by default. Createrole | nocreaterole: constructs the role authorization, create, modify, delete, nocreaterole by default. The replication | noreplication: copy permissions for physical or logical copy, copy and delete slots), default is noreplication. Bypassrls | nobypassrls: security policy RLS permissions, nobypassrls by default. Connection limit ConnLimit: Limits the number of concurrent users. The default value is -1. Normal connections are restricted, but background connections and PREPARED transactions are not. Public: all users.
- A user has been created and authorized to operate the database
Grant {{create | | connect temporary | temp} | all [privileges]} on the database database name to user name | public [with grant option];Copy the codeCreate: For a database, new schemas, tables, and indexes can be created in the database. Connect: Allows the user to connect to the specified database. This permission is checked when the connection is started. Temporary, temp: Allows temporary tables to be created when a specified database is used. All Privileges: Grants all available privileges at one time. Public: all users.
- You have created operation rights for the user in authorization mode
Grant {{create | usage} | all [privileges]} on the schema model name to user name | public [with grant option];Copy the codeCreate: For schema, new tables and indexes can be created in the schema. Usage: For schema, allow access to objects contained in the specified schema. For sequences, the currval and nextval functions are allowed. For types and fields, you are allowed to use types or fields when creating tables, functions, and other schema objects. All Privileges: Grants all available privileges at one time. Public: all users.
- Users have been granted basic object operation rights
Grant {{select | insert | update | delete | truncate | references | trigger} | all [privileges]} on {[table] table name | all tables in the schema Schema name} to user name | public [with grant option];Copy the codeSelect: Allows select from any column of a specified table, view, or sequence or from a specific column listed. Copy to is also allowed. This permission is also required to reference existing column values in update or DELETE. For sequences, this permission also allows the use of the currval function. For large objects, this permission allows the object to be read. Insert: Allows new rows to be inserted into the specified table. If specific columns are listed, they can only be assigned in the INSERT command (so the other columns will receive the default values). Copy from is also allowed. Update: Allows the update of any column of a specified table or a specific column listed, requiring select permission. Delete: Allows rows in a specified table to be deleted. Select permission is required. Truncate: Allows the specified table to be cleared. References: Allows you to create a foreign key constraint that references a specified table or a specified column of the table. Trigger: Allows a trigger to be created on a specified table. Execute: Allows use of the specified function or procedure as well as in the function. All Privileges: Grants all available privileges at one time. Public: all users.
-- Authorizes all users to perform all operations on database D1. postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- d1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | d2 | u2 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | repmgr | repmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 | syd | syd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (7 rows) postgres=# grant all on database d1 to public; GRANT postgres=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- d1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =CTc/postgres + | | | | | postgres=CTc/postgres d2 | u2 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | repmgr | repmgr | UTF8 | en_US.UTF-8 | en_US.UTF-8 | syd | syd | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | Postgres =CTc/postgres (7 rows) -- Users U1 and U2 can create schemas in the D1 database. postgres=# \c d1 u1 You are now connected to database "d1" as user "u1". d1=> \dn List of schemas Name | Owner --------+---------- d1s1 | u1 public | postgres (2 rows) d1=> create schema d1s2; CREATE SCHEMA d1=> \c - u2 You are now connected to database "d1" as user "u2". d1=> create schema d1s3; CREATE SCHEMA d1=> \dn List of schemas Name | Owner --------+---------- d1s1 | u1 d1s2 | u1 d1s3 | u2 public | postgres (4 rows)Copy the codeRevoke permissions
[grant, revoke option for] {the above all permissions} from {username | public} [cascade | restrict];Copy the codePublic: all users. Cascade: Reclaim permission in a cascade. Restrict: indicates that the permission to reclaim is not casccasced.
D1 =# revoke all user privileges on database d1 from public; REVOKE d1=# \c d1 u1 FATAL: permission denied for database "d1" DETAIL: User does not have CONNECT privilege. -- The User does not have CONNECT privilege. -- The User does not have CONNECT privilege. d1=# grant connect on database d1 to public; GRANT d1=# \c - u1 You are now connected to database "d1" as user "u1". d1=> \dn List of schemas Name | Owner --------+---------- d1s1 | u1 d1s2 | u1 d1s3 | u2 public | postgres (4 rows) d1=> create schema d1s4; ERROR: Permission denied for database D1 Has no permission to create the schema. Therefore, the all permission has been reclaimed.Copy the code