创建用户的语句为:
CREATE ROLE guest LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
注意上述Role guest拥有LOGIN的权限,所以叫它用户。
创建角色的语句为:
CREATE ROLE "group" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
注意这里没有LOGIN权限,所以是角色。
上述角色和用户的创建语句中,都没有赋予超级用户、创建数据库等权限。
怎么样控制登录用户操作数据库对象的权限呢?
答案是让用户成为角色的成员,此时用户即可拥有角色的权限,进一步限制了登录用户操作数据库对象的权限。
如把上述角色group赋予guest用户:
GRANT "group" TO guest;
之后,guest用户就拥有了group角色所拥有的数据库对象权限。
==============================================================================
highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
highgo | Superuser, Create role, Create DB, Replication | {}
highgo=# create schema a;
CREATE SCHEMA
highgo=# create schema b;
CREATE SCHEMA
highgo=# create role a login password 'highgo123';
CREATE ROLE
highgo=# create role b login password 'highgo123';
CREATE ROLE
highgo=# alter schema a owner to a;
ALTER SCHEMA
highgo=# alter schema b owner to b;
ALTER SCHEMA
highgo=# create table a.test (id int);
CREATE TABLE
highgo=# insert into a.test values (1);
INSERT 0 1
highgo=# create table b.test (id int);
CREATE TABLE
highgo=# insert into b.test values (2);
INSERT 0 1
highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> select * from test;
错误: 对关系 test 权限不够
highgo=> select * from a.test;
错误: 对关系 test 权限不够
highgo=> select
CREATE ROLE guest LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
注意上述Role guest拥有LOGIN的权限,所以叫它用户。
创建角色的语句为:
CREATE ROLE "group" NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
注意这里没有LOGIN权限,所以是角色。
上述角色和用户的创建语句中,都没有赋予超级用户、创建数据库等权限。
怎么样控制登录用户操作数据库对象的权限呢?
答案是让用户成为角色的成员,此时用户即可拥有角色的权限,进一步限制了登录用户操作数据库对象的权限。
如把上述角色group赋予guest用户:
GRANT "group" TO guest;
之后,guest用户就拥有了group角色所拥有的数据库对象权限。
==============================================================================
highgo=# \c
You are now connected to database "highgo" as user "highgo".
highgo=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
highgo | Superuser, Create role, Create DB, Replication | {}
highgo=# create schema a;
CREATE SCHEMA
highgo=# create schema b;
CREATE SCHEMA
highgo=# create role a login password 'highgo123';
CREATE ROLE
highgo=# create role b login password 'highgo123';
CREATE ROLE
highgo=# alter schema a owner to a;
ALTER SCHEMA
highgo=# alter schema b owner to b;
ALTER SCHEMA
highgo=# create table a.test (id int);
CREATE TABLE
highgo=# insert into a.test values (1);
INSERT 0 1
highgo=# create table b.test (id int);
CREATE TABLE
highgo=# insert into b.test values (2);
INSERT 0 1
highgo=> \c
You are now connected to database "highgo" as user "a".
highgo=> select * from test;
错误: 对关系 test 权限不够
highgo=> select * from a.test;
错误: 对关系 test 权限不够
highgo=> select