Shulou(Shulou.com)06/01 Report--

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)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.