The Foundation of postgresql Database
Create a read-only account
1.1 to initialize account login
[root@localhost ~] # psql-U postgres
1.2 create a user
Postgres=# create role develop with login password '123456'
CREATE ROLE
Postgres=# select usename from pg_user
Usename
-
Postgres
Test
Develop
(3 rows)
1.3 switching databases
\ C current_product
1.4 Grant read-only permission
Current_product=# grant select on all tables in schema public to develop
GRANT
1.5 switch to develop user
Current_product=#\ c-develop
You are now connected to database "current_product" as user "develop".
1.6 detect whether you have read-only permission
Current_product= > select * from test
Id
-
(0 rows)
2 create a read-write account
2.1 initial account login
Psql-U postgres
2.2 View users
Postgres=# select usename from pg_user
Usename
-
Postgres
Test
Test1
U2
(4 rows)
2.3 create a read-write user
Postgres=# create role test2 with login password '123456'
CREATE ROLE
Postgres=# grant ALL on all tables in schema public to test2; # this kind of authorization is not correct. Test2 users do not have permission to current_product database.
GRANT
2.4 detect whether the user has read and write permissions
Postgres=#\ c-test2
You are now connected to database "postgres" as user "test2".
Switch database
Postgres= >\ c current_product
You are now connected to database "current_product" as user "test2".
Current_product= >\ dt
List of relations
Schema | Name | Type | Owner
-+-
Public | aaa | table | postgres
Public | test | table | postgres
(2 rows)
Current_product= > select * from aaa; # shows no permissions
ERROR: permission denied for relation aaa
2.5 the correct way to authorize is to switch to the target database and execute the authorization statement
Postgres=#\ c current_product # switch to the target database
You are now connected to database "current_product" as user "postgres".
Current_product=# grant ALL on all tables in schema public to test2; # execute authorization statement
GRANT
2.6 switch to read and write users to detect whether they have permissions
Current_product=#\ c-test2 # switch to read / write user
You are now connected to database "current_product" as user "test2".
Current_product= >\ dt # View several tables
List of relations
Schema | Name | Type | Owner
-+-
Public | aaa | table | postgres
Public | test | table | postgres
(2 rows)
Current_product= > select * from aaa; # check permissions are normal
Id
-
(0 rows)
Current_product= > insert into aaa values (1); # adding permissions is normal
INSERT 0 1
Current_product= > select * from aaa
Id
-
one
(1 row)
Current_product= > delete from aaa; # Delete permissions are normal
DELETE 1
2.7 switch to superuser
Current_product= >\ c-postgres
You are now connected to database "current_product" as user "postgres".
Current_product=# create table bbb (id int); # add a table
CREATE TABLE
2.8 switch to read-write user
Current_product=#\ c-test2
You are now connected to database "current_product" as user "test2".
Current_product= >\ dt
List of relations
Schema | Name | Type | Owner
-+-
Public | aaa | table | postgres
Public | bbb | table | postgres
Public | test | table | postgres
(3 rows)
Current_product= > select * from bbb; # shows no permissions
ERROR: permission denied for relation bbb
2.9 Solutions:
Each new table executes an authorization statement, otherwise there is no permission (other methods are being explored. )
Current_product= >\ c-postgres
You are now connected to database "current_product" as user "postgres".
Current_product=# grant ALL on all tables in schema public to test2
GRANT
Switch to read and write users to detect permissions
Current_product=#\ c-test2
You are now connected to database "current_product" as user "test2".
Current_product= > select * from bbb
Id
-
(0 rows)
Current_product= > insert into bbb values (2222)
INSERT 0 1
Current_product= > select * from bbb
Id
-
2222
(1 row)
Current_product= > delete from bbb
DELETE 1
Current_product= > select * from bbb
Id
-
(0 rows)
