一个用户的权限可以分为两大类
一类是创建用户的时候指定的:
登录 (login)[需要注意, user 自带 login 权限, role 不带]
创建用户, 角色 (createuser/role)
权限继承 (inherit)
创建数据库 (createdb)
超级用户 (superuser)
等等
举例:
highgo=# create role trole with createrole inherit;
CREATE ROLE
highgo=# \c highgo trole;
????: 28000: ?????"trole" ????
Previous connection kept
highgo=# alter user trole with login;
ALTER ROLE
highgo=# \c highgo trole;
PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production
You are now connected to database "highgo" as user "trole".
highgo=>
另一类是通过 grant 和 revoke 来管理的:
数据库中创建临时表, 模式, 普通表, 函数等
对数据库中数据的增删改查等
对序列的查询, 使用, 更新等
将一个用户的权限赋予给另一个用户
......
将数据库逻辑结构对象的操作权限赋予给某个用户 / 角色, 格式如下:
GRANT privileges ON database_obj TO user_name;
举例:
grant select,insert on table test to test;
highgo=> \c highgo test
PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production
You are now connected to database "highgo" as user "test".
highgo=> select * from test limit 6;
错误: 42501: 对关系 test 权限不够
highgo=> \c highgo highgo
PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production
You are now connected to database "highgo" as user "highgo".
highgo=# grant select,insert on table test to test;
GRANT
highgo=# \c highgo test;
PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production
You are now connected to database "highgo" as user "test".
highgo=> select * from test limit 6;
id | name | profession
----+-------+------------
1 | TOM | accounting
1 | JERRY | accounting
2 | JERRY | accounting
2 | JUGG | Business
10 | 瀚高 | 基础软件
11 | 瀚高 | 基础软件
(6 rows)
BY 海无涯
来源: http://blog.csdn.net/pg_hgdb/article/details/79220075