时间:2021-07-01 10:21:17 帮助过:4人阅读
- $ psql --version
- psql (PostgreSQL) 9.4.4
我们都知道,超级用户的权限太大了,为了数据库的安全,对于非管理员账号,需要创建普通用户。
- $ psql
- psql (9.4.4)
- Type "help" for help.
- postgres=# \h create role
- Command: CREATE ROLE
- Description: define a new database role
- Syntax:
- CREATE ROLE name [ [ WITH ] option [ ... ] ]
- where option can be:
- SUPERUSER | NOSUPERUSER
- | CREATEDB | NOCREATEDB
- | CREATEROLE | NOCREATEROLE
- | CREATEUSER | NOCREATEUSER
- | INHERIT | NOINHERIT
- | LOGIN | NOLOGIN
- | REPLICATION | NOREPLICATION
- | CONNECTION LIMIT connlimit
- | [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password‘
- | VALID UNTIL ‘timestamp‘
- | IN ROLE role_name [, ...]
- | IN GROUP role_name [, ...]
- | ROLE role_name [, ...]
- | ADMIN role_name [, ...]
- | USER role_name [, ...]
- | SYSID uid
- postgres=# create table t1 ( id serial, name varchar(64) );
- CREATE TABLE
- postgres=# \dt
- List of relations
- Schema | Name | Type | Owner
- --------+------+-------+----------
- public | t1 | table | postgres
- (1 row)
- postgres=# create role u1 with login password ‘123456‘;
- CREATE ROLE
login是赋予登录权限,否则是不能登录的
因为创建的普通用户默认是没有任何权限的
- postgres=# grant select on all tables in schema public to u1;
- GRANT
- postgres=# \c - u1
- You are now connected to database "postgres" as user "u1".
- postgres=> select * from t1;
- id | name
- ----+------
- (0 rows)
- postgres=> \c - postgres
- You are now connected to database "postgres" as user "postgres".
- postgres=# create table t2 ( id serial, name varchar(64) );
- CREATE TABLE
- postgres=# \dt
- List of relations
- Schema | Name | Type | Owner
- --------+------+-------+----------
- public | t1 | table | postgres
- public | t2 | table | postgres
- (2 rows)
- postgres=# \c - u1
- You are now connected to database "postgres" as user "u1".
- postgres=> select * from t1;
- id | name
- ----+------
- (0 rows)
- postgres=> select * from t2;
- ERROR: permission denied for relation t2
可见u1是有t1表的读权限,但没有t2表的读权限,这样是不是意味着每次新建表就要赋一次权限?
- postgres=> \c - postgres
- You are now connected to database "postgres" as user "postgres".
- postgres=# alter default privileges in schema public grant select on tables to u1;
- ALTER DEFAULT PRIVILEGES
- postgres=# create table t3 ( id serial, name varchar(64) );
- CREATE TABLE
- postgres=# \dt
- List of relations
- Schema | Name | Type | Owner
- --------+------+-------+----------
- public | t1 | table | postgres
- public | t2 | table | postgres
- public | t3 | table | postgres
- (3 rows)
- postgres=# \c - u1
- You are now connected to database "postgres" as user "u1".
- postgres=> select * from t3;
- id | name
- ----+------
- (0 rows)
赋予schema中所有表的默认权限给u1,这样以后新建表就不用再赋权限了。
- postgres=# create role u2 with login password ‘123456‘;
- CREATE ROLE
- postgres=# alter default privileges in schema public grant select,insert,update,delete on tables to u2;
- ALTER DEFAULT PRIVILEGES
- postgres=# create table t4 ( id serial, name varchar(64) );
- CREATE TABLE
- postgres=# \dt
- List of relations
- Schema | Name | Type | Owner
- --------+------+-------+----------
- public | t1 | table | postgres
- public | t2 | table | postgres
- public | t3 | table | postgres
- public | t4 | table | postgres
- (4 rows)
- postgres=# \c - u2
- You are now connected to database "postgres" as user "u2".
- postgres=> insert into t4 values ( 1, ‘aa‘ );
- INSERT 0 1
- postgres=> select * from t4;
- id | name
- ----+------
- 1 | aa
- (1 row)
- postgres=> update t4 set name = ‘bb‘ where id = 1;
- UPDATE 1
- postgres=> select * from t4;
- id | name
- ----+------
- 1 | bb
- (1 row)
- postgres=> delete from t4 where id = 1;
- DELETE 1
- postgres=> select * from t4;
- id | name
- ----+------
- (0 rows)
可以正常增删改查
在insert的时候,指定列插入,主键id是serial类型会默认走sequence的下一个值,但前面只赋予了表的权限,所以会出现下面的问题:
- postgres=> insert into t4 ( name ) values ( ‘aa‘ );
- ERROR: permission denied for sequence t4_id_seq
解决方法就是再赋一次sequence的值就行了
- postgres=> \c - postgres
- You are now connected to database "postgres" as user "postgres".
- postgres=# alter default privileges in schema public grant usage on sequences to u2;
- ALTER DEFAULT PRIVILEGES
- postgres=# create table t5 ( id serial, name varchar(64) );
- CREATE TABLE
- postgres=# \c - u2
- You are now connected to database "postgres" as user "u2".
- postgres=> insert into t5 ( name ) values ( ‘cc‘ );
- INSERT 0 1
- postgres=> select * from t5;
- id | name
- ----+------
- 1 | cc
- (1 row)
- postgres=> \c - postgres
- You are now connected to database "postgres" as user "postgres".
- postgres=# drop role u2;
- ERROR: role "u2" cannot be dropped because some objects depend on it
- DETAIL: privileges for table t5
- privileges for sequence t5_id_seq
- privileges for default privileges on new sequences belonging to role postgres in schema public
- privileges for table t4
- privileges for default privileges on new relations belonging to role postgres in schema public
当我们删除用户的时候,会提示有权限依赖,所以我们要删除这些权限
- postgres=# alter default privileges in schema public revoke usage on sequences from u2;
- ALTER DEFAULT PRIVILEGES
- postgres=# alter default privileges in schema public revoke select,insert,delete,update on tables from u2;
- ALTER DEFAULT PRIVILEGES
- postgres=# revoke select,insert,delete,update on all tables in schema public from u2;
- REVOKE
- postgres=# revoke usage on all sequences in schema public from u2;
- REVOKE
- postgres=# drop role u2;
- DROP ROLE
这样太麻烦了,有没有更快的办法?
- postgres=# drop role u1;
- ERROR: role "u1" cannot be dropped because some objects depend on it
- DETAIL: privileges for table t5
- privileges for table t4
- privileges for table t3
- privileges for default privileges on new relations belonging to role postgres in schema public
- privileges for table t1
- postgres=# drop owned by u1;
- DROP OWNED
- postgres=# drop role u1;
- DROP ROLE
PostgreSQL权限管理之创建可更新表的普通用户
标签: