1 public permission interpretation
Users have public permissions by default, and public has permissions to create and use schemas by default, so it means that objects can be created (including tables) in the schema, listed objects in the schema, and accessed when their permissions allow them.
So the first thing you do after creating a database or schema is
1.1 After the database creation is completed
--RecycleschemaofpublicPermissions --revoke all on schema public from public; --Use this normally revoke all on schema public,tiger,tiger_data,topology from public; --usepostgis扩展use这个 --fromcoreDatabase recoverypublicof所有Permissions revoke all on database core from public; revoke all on database postgres from public; --ReplaceschemaGrant to database owners grant all on schema public,tiger,tiger_data,topology to Database owner;
1.2 After creating the schema is completed
create schema history; revoke all on schema history from public; --Replaceschema historyGrant to database owners grant all on schema history to Database owner;
2 Test
Suppose there is now a database core.
2.1 Postgrese user connects to core database
psql -h localhost -p 5432 -U postgres -d core
--Create a nameselUsers create user sel with nosuperuser nocreatedb nocreaterole noinherit login noreplication nobypassrls password '123'; --Allow connection to the databasecore grant connect on database core to sel; --View role usage\duS+ \duS+ --or \duS+ sel --View the user inschemaPermissions \dp --查看数据库Users \l+
Permission explanation:
r -- SELECT ("read")
w -- UPDATE ("Write")
a -- INSERT ("Add")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES （For the table，Changes to other objects）
* -- Authorization options for the aforementioned privileges
2.2 Running in another process
psql -h localhost -p 5432 -U sel -d core
drop table aaa; create table aaa( objectid integer ); select * from features;
The above SQL report
ERROR: table "aaa" does not exist ERROR: no schema has been selected to create in ERROR: relation "features" does not exist
2.3 Use postgres user to log in to the core database to authorize the sel user
psql -h localhost -p 5432 -U postgres -d core
Grant the permissions you need. Here you can grant all objects under the public schema by sel.
grant usage on schema public to sel; grant select on all tables in schema public to sel;
2.4 Go again to Run in another process
psql -h localhost -p 5432 -U sel -d core
select * from features; delete from features;
Now you can query data normally, but this user only has query permissions.
3 Delete the user
3.1 Connect to the core database using postgrese user
Note what permissions you grant to the user must be revoke before dropping the user, and then deleted, otherwise the dependency error will be reported. Therefore, the authorization file is saved so that users can be deleted in the future, otherwise the query correlation will be very troublesome.
psql -h localhost -p 5432 -U postgres -d core
--Recycle assigned permissions and delete users revoke select on all tables in schema public from sel; revoke usage on schema public from sel; revoke connect on database core from sel; drop role sel;
Additional: PostgreSQL User and Permission Management
Default user
After the postgres installation is completed, a user named postgres and a database named postgres will be automatically created in the operating system and postgres database respectively.
Log in
Method 1: Specify parameters to log in
psql -U username -d database_name -h host -W
Parameter meaning: -U specifies the user -d database to connect to -h host to connect to -W prompts for password.
Method 2: Switch to the user with the same name of postgres and log in
su username psql
When no parameters are specified psql uses the username of the current user of the operating system as the login username of postgres and the database name to be connected to. Therefore, after the PostgreSQL installation is completed, you can log in through the above method.
Create a user
Method 1: Create in the system command using createuser command
createuser username
Method 2: Create using the CREATE ROLE directive in the PostgresSQL command line
CREATE ROLE rolename;
Method 3: Create using the CREATE USER directive in the PostgresSQL command line
CREATE USER username;
The difference between CREATE USER and CREATE ROLE is that the user created by the CREATE USER directive has login permission by default, while CREATE ROLE does not.
The \du command displays user and user's user attributes
Set user attributes when creating a user
Basic syntax format
CREATE ROLE role_name WITH optional_permissions;
Example: Set login permissions when creating a user.
CREATE ROLE username WITH LOGIN;
You can view all set management permissions through the \h CREATE ROLE command
Modify user attributes
Modify the command format of permissions
ALTER ROLE username WITH attribute_options;
For example: Users can be prohibited from logging in by the following methods
ALTER ROLE username WITH NOLOGIN;
Set access permissions
The syntax format is as follows:
GRANT permission_type ON table_name TO role_name;
Example:
GRANT UPDATE ON demo TO demo_role; --Grantdemo_role demoTable ofupdatePermissions GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to demo_role; --Grantdemo_role所有Table ofSELECTPermissions
Special symbol: ALL represents the access permissions, PUBLIC represents all users
GRANT ALL ON demo TO demo_role; --Assign all permissions to the user GRANT SELECT ON demo TO PUBLIC; --WillSELECTAllow permissions to all users
The \z or \dp directive displays user access permissions.
\h GRANT Displays all set-up access permissions
Revoke user access
The syntax format is as follows:
REVOKE permission_type ON table_name FROM user_name;
Where permission_type and table_name have the same meaning as in the GRANT directive.
User Group
In postgres, the user is actually role, and the group is also role. A role containing other roles is a group.
Example of creating a group:
CREATE ROLE temporary_users; GRANT temporary_users TO demo_role; GRANT temporary_users TO test_user;
Switch ROLE
SET ROLE role_name; --Switch torole_nameuser RESET ROLE; --Switch back to the originalrole
INHERIT permissions: This property allows group members to have all permissions in the group
ALTER ROLE test_user INHERIT;
Delete users and groups
Deleting users and groups is simple:
DROP ROLE role_name; DROP ROLE IF EXISTS role_name;
Deleting a group role will only delete the group's role itself, and the group members will not be deleted.
The above is personal experience. I hope you can give you a reference and I hope you can support me more. If there are any mistakes or no complete considerations, I would like to give you advice.