1. Modify the user postgres password
#alter user postgres with password ‘xxxx';(inxxxxIt is the modified password)。
2. Check the owner of the current schema:
// Check the current owner of the schema, which is equivalent to the \du element commandSELECT AS "Name", pg_catalog.pg_get_userbyid() AS "Owner" FROM pg_catalog.pg_namespace n WHERE !~ '^pg_' AND <> 'information_schema' ORDER BY 1;
3. The query results are shown in the figure. The owner of the mode "abc" is the postgresql user
For the mode "abc", use the super administrator postgresql to authorize ordinary users test, and the command is as follows:
// The last command is to grant initial permissionsgrant select on all tables in schema abc to test; grant usage on schema abc to test; alter default privileges in schema abc #Authorize the table mytable to testUser;#GRANT SELECT ON TABLE mytable TO testUser;
4. Check the default permissions
Authorization is completed, and the default permissions are viewed through the pg_default_acl table:
// View initial permissionsselect * from pg_catalog.pg_default_acl;
5. Change the owner of the mode "abc" to the dbadmin user (can be created in advance), and execute the following command:
// The owner of the modification mode "abc" is: dbadminALTER SCHEMA abc OWNER TO "dbadmin"; // The owner of the view mode is equivalent to the \du element commandSELECT AS "Name", pg_catalog.pg_get_userbyid() AS "Owner" FROM pg_catalog.pg_namespace n WHERE !~ '^pg_' AND <> 'information_schema' ORDER BY 1;
6. Postgre query all users, and postgre query the permissions owned by the user
select * from pg_roles; select * from pg_user;
Permission query:
select * from information_schema.table_privileges where grantee='cc';
View all permissions of the current user
select * from information_schema.table_privileges where grantee='user_name';
7. Assign all permissions applicable to the object to the target role.
Use a special name PUBLIC to assign the permissions of the object to all roles in the system. Writing ALL on the permission declaration means giving all permissions applicable to the object to the target role.
beigang=# grantall on schema csm_ca to public; GRANT beigang=# revoke all on schema csm_ca frompublic; REVOKE
8. First create a role xxx, then create a super user csm and ordinary user csm_ca. The csm user creates a database testdb, create a schema: csm_ca in this database, and then give the ordinary user csm_ca permission to operate the tables in schema: csm_ca in the database testdb.
#create role： #create role xxx with superuser; #Create user： # create user csm with superuserpassword 'csm'; # create user csm_ca with password 'csm_ca';
9. Super user csm grants permission to operate schema csm_ca to ordinary user csm_ca
beigang=# grant all on schema csm_ca to csm_ca; GRANT beigang=# grant all on all tables in schema csm_ca to csm_ca; GRANT
10. Create a user
#Create a normal userpostgres=# create user test encrypted password 'test'; #Create a super userpostgres=# create user test2 superuser; #Create an ordinary user and give relevant permissions# create user test createdb createrole inherit password 'test'; #Modify super user to normal user# alter user test nosuperuser; #Modify the user to super userpostgres=# alter user test superuser; #Modify user passwordpostgres=# alter user test2 password 'test'; #Modify usernamepostgres=# alter user test2 rename to test3; #Lock/Unlock the user, do not allow/allow them to log inpostgres=# alter user test nologin; postgres=# alter user test login; #Set the number of connections for users, where 0 means login is not allowed, and -1 means unlimitedpostgres=# alter user test connection limit 10;
11. Grant user database permissions
GRANT ALL PRIVILEGES ON DATABASE Database name TO username;
12. Grant the user permission to view the tables in the newly authorized database
GRANT ALL PRIVILEGES ON TABLE Table name TO username;
13. With one: the type of modified table
alter table Table name alter Field name type type;
14. Attached one: Add new fields to the table
alter table Table name add column Field name text（Field Type）;
15. New: Set the primary key to increase
CREATE SEQUENCE user_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
alter table sys_user alter COLUMN id set DEFAULT nextval('user_id_seq');
16. New: postgres creates B-Tree index
-- create index 'Index Name' on 'Table name' ('Fields that require indexing')
CREATE INDEX ip_store_inde on ip_store (ip_network);
Add various constraints
(1) Add primary key
alter table goods add primary key(sid);
(2) Add foreign keys
alter table orders add foreign key(goods_id) references goods(sid) on update cascade on delete cascade;
on update cascade: When the referenced row is updated, the referenced row will be automatically updated;
on update restrict: The referenced row is prohibited from being updated;
on delete cascade: When the referenced row is deleted, the referenced rows are also deleted together;
on dellete restrict: The referenced lines are prohibited from deletion;
(3). Delete foreign keys
alter table orders drop constraint orders_goods_id_fkey;
(4). Add unique constraints
alter table goods add constraint unique_goods_sid unique(sid);
(5). Delete the default value
alter table goods alter column sid drop default;
(6). Modify the data type of the field
alter table goods alter column sid type character varying;
(7). Rename the field
alter table goods rename column sid to ssid;
17. Create a unique key constraint
constraint user_info_unique_userid unique(userid)
Extended
Edit configuration files
document:
Location: /var/lib/pgsql/data/
Add/Modify: Listen on all IP addresses, allowing remote connection to the database server:
listening_address: '*'
File: pg_hba.conf
Location: /var/lib/pgsql/data/pg_hba.conf
Add/modify: Allow any user to access the database in a password from any machine, and add the downline as the first rule:
host all all 0.0.0.0/0 md5
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.