SRE文档PostgreSQL 给角色授权
在数据库中所有的权限都和角色（用户）挂钩，public是一个特殊角色，代表所有人。
超级用户是有允许任意操作对象的，普通用户只能操作自己创建的对象。
另外有一些对象是有赋予给public角色默认权限的，所以建好之后，所以人都有这些默认权限
当前权限所有表状态
select relname,relacl from pg_class where relkind='r';select relname,relacl from pg_class where relkind='r';查看一个表权限状态
postgres=# \dp+ pg_sequence;
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)postgres=# \dp+ pg_sequence;
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+------+-------------------+-------------------+----------
(0 rows)https://www.postgresql.org/docs/12/sql-grant.html
PG 中常见的几个概念，包括了 Cluster、Database、Schemas
Schemas
Schema 是 PG-7.3 引入的特性，在 PG 中是一个逻辑概念，一个用户可以访问他所连接数据库中的任意 schema 中的对象，只要他有权限即可。之所以又添加了 schema，主要有如下原因
- 允许多个用户互不干扰地使用同一个数据库，从逻辑上将 DB 分组以方便管理
- 提供 database 下的命名空间隔离，这样的好处是，灵活。例如，可以创建月份的 schema，每个里面的表结构都相同
- PG 的限制是一个连接只能连接到一个数据库，这样一个连接就可以操作不同 schema 下的对象
每个客户端的连接都会存在一个搜索路径 (search path)，如果没有指定 schema，那么 PG 就会按照该路径进行搜索
sql
postgres@postgres=>SHOW SEARCH_PATH;
search_path
-----------------
"$user", public
(1 row)
----- 可以修改搜索路径
postgres=# SET SEARCH_PATH TO 'foo','bar','public';
SETpostgres@postgres=>SHOW SEARCH_PATH;
search_path
-----------------
"$user", public
(1 row)
----- 可以修改搜索路径
postgres=# SET SEARCH_PATH TO 'foo','bar','public';
SET另外的一些常见操作可以参考如下：
sql
----- 查询schema
postgres@postgres=>\dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
cron | postgres | |
dba | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(3 rows)
----- 创建schema以及其中的表
postgres=# CREATE SCHEMA foo;
CREATE SCHEMA
postgres=# CREATE TABLE foo.foobar(id INTEGER);
CREATE TABLE
----- 删除schema，如果非空，则通过cascade强制删除
postgres=# DROP SCHEMA foo CASCADE;----- 查询schema
postgres@postgres=>\dn+
List of schemas
Name | Owner | Access privileges | Description
--------+----------+----------------------+------------------------
cron | postgres | |
dba | postgres | |
public | postgres | postgres=UC/postgres+| standard public schema
| | =UC/postgres |
(3 rows)
----- 创建schema以及其中的表
postgres=# CREATE SCHEMA foo;
CREATE SCHEMA
postgres=# CREATE TABLE foo.foobar(id INTEGER);
CREATE TABLE
----- 删除schema，如果非空，则通过cascade强制删除
postgres=# DROP SCHEMA foo CASCADE;除了 public 和用户创建的 schema 之外，还存在一个 pg_catalog，它包含了系统表所有的内建数据类型、函数、操作符，它总是搜索路径的一部分，只是没有在搜索路径中显示
Databases
CREATE DATABASE db_name
[
[ WITH]
[ OWNER [ = ] user_name ] # 所属用户，默认属于创建的用户
[ TEMPLDATE [ =] templdate ] # 创建数据库需要的模板，默认template1
[ ENCODING [ = ] encoding ] # 数据库使用的编码，如utf8
[ LC_COLLATE [ = ] lc_collate ] # 排序类型
[ LC_CTYPE [ = ] lc_ctype ] # 同上
[ TABLESPACE [ = ] tablespace ] # 数据库的默认表空间
[ CONNECTION LIMIT [ = ] connection limit ] # 数据库的最大连接数，默认为-1，禁用连接
]
----- 示例
POSTGRES=# CREATE DATABASE demodb TEMPLATE template0 ENCODING 'UTF8' TABLESPACE ts_demo01 CONNECTION LIMIT 200;
CREATE DATABASECREATE DATABASE db_name
[
[ WITH]
[ OWNER [ = ] user_name ] # 所属用户，默认属于创建的用户
[ TEMPLDATE [ =] templdate ] # 创建数据库需要的模板，默认template1
[ ENCODING [ = ] encoding ] # 数据库使用的编码，如utf8
[ LC_COLLATE [ = ] lc_collate ] # 排序类型
[ LC_CTYPE [ = ] lc_ctype ] # 同上
[ TABLESPACE [ = ] tablespace ] # 数据库的默认表空间
[ CONNECTION LIMIT [ = ] connection limit ] # 数据库的最大连接数，默认为-1，禁用连接
]
----- 示例
POSTGRES=# CREATE DATABASE demodb TEMPLATE template0 ENCODING 'UTF8' TABLESPACE ts_demo01 CONNECTION LIMIT 200;
CREATE DATABASE如果是 superuser 可以指定不同的 OWNER ，否则使用当前的用户。TEMPLATE 则指定了复制的模版，会将该模版中定义的所有 tables、views、data types、functions、operators 复制到新的数据库目录下，默认采用 template1
text
----- 查看详细信息
postgres=# \l+----- 查看详细信息
postgres=# \l+在 PG 中，数据库、表、索引的关系如下
- 数据库：一个 PG 数据库下可以管理多个数据库，当应用连接的时候，只能连接到其中的一个数据库，而一个数据库只能属于一个实例
- 表、索引：一个数据库里可以有多个表与索引，PG 称为 “Relation”
- 数据行：在每张表中可以有很多数据行，PG 称为 “Tuple”
表空间，tablespaces
这是 PG-8.0 引入的特性，将 DBs、tables、indexs 保存到指定的 tablespace 中，会在创建时指定数据的保存目录，也就是说表空间就是一个简单的目录，其主要用途分两个
- 单独扩展表空间用，一旦磁盘或分区被耗尽，可以创建一个表空间到其他磁盘或分区上面
- 区分不同对象的存储位置，比如可以将冷热数据进行分别存放
与 Oracle 中的表空间被独占不同，PG 的表空间是可以被共享的，当创建了一个表空间后，这个表空间可以被多个数据库、表、索引等数据库对象使用
sql
CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory';CREATE TABLESPACE tablespacename [ OWNER username ] LOCATION 'directory';创建时必须要满足如下的条件
- superuser
- OS 必须支持符号连接，也就是说 Windows 不支持
- 目录必须已经存在且为空,目录所属主
创建时，PG 会在后台执行一系列操作，基本流程为
1,将目录权限修改为 700----->2,创建一个 PG_VERSION 文件-------3,在 pg_tablespace 添加一行，并新建一个 OID(object-id)-------->4,在 $PGDATA/pg_tblspc 目录下创建一个 OID 指向的符号连接
当在 tablespace 中创建对象时，例如 database，会先创建一个目录，然后才会在该目录下创建对象，之所以这样，是为了避免 OID 冲突
sql
----- 查看表空间
postgres@postgres=>SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;
spcname | pg_tablespace_location
------------+------------------------
pg_default |
pg_global |
(2 rows)
postgres@postgres=>\db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 1981 MB |
pg_global | postgres | | | | 639 kB |
(2 rows)
----- 创建表空间，目录需要先创建
postgres=# CREATE TABLESPACE ts_foobar LOCATION '/tmp/foobar';
CREATE TABLESPACE
postgres=# SELECT oid, * FROM pg_tablespace WHERE spcname = 'ts_foobar';
oid | spcname | spcowner | spcacl | spcoptions
-------+-----------+----------+--------+------------
16435 | ts_foobar | 10 | |
(1 row)
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 35 MB |
pg_global | postgres | | | | 464 kB |
ts_foobar | postgres | /tmp/foobar | | | 0 bytes |
(3 rows)
-- 创建数据库，并指向表空间
postgres=# CREATE DATABASE mydb OWNER postgres TABLESPACE ts_foobar;
CREATE DATABASE
postgres=# SELECT oid, datname FROM pg_database;
oid | datname
-------+-----------
1 | template1
12918 | template0
12923 | postgres
16439 | mydb
(4 rows)
postgres=# \connect mydb;
You are now connected to database "mydb" as user "postgres".
----- 在上述创建的表空间中添加表
mydb=# CREATE TABLE foobar(x int, y varchar(30), z date) TABLESPACE ts_foobar;
CREATE TABLE
mydb=# INSERT INTO foobar VALUES(1, 'ShangHai', now()), (2, 'NanJing', now()), (3, 'HangZhou', now());
INSERT 0 3
mydb=# CREATE INDEX idx_foobar_x on foobar(x) TABLESPACE ts_foobar;
CREATE INDEX
----- 添加表的主键约束或是唯一键约束的时候指定表空间
mydb=# ALTER TABLE foobar ADD CONSTRAINT uk_foobar_y UNIQUE(y) USING INDEX TABLESPACE ts_foobar;
ALTER TABLE
------ 查看对应的OID
mydb=# SELECT oid FROM pg_class WHERE relname = 'foobar';
oid
-------
16440
(1 row)
mydb=# SELECT oid FROM pg_database WHERE datname = 'mydb';
oid
-------
16439
(1 row)
----- 查看表所在文件，可以直接在目录下查找对应文件
mydb=# SELECT pg_relation_filepath('foobar');
pg_relation_filepath
----------------------------------------------
pg_tblspc/16435/PG_9.5_201510051/16439/16440
(1 row)
$ ls -l $PGDATA/pg_tblspc
total 0
lrwxrwxrwx 1 postgres postgres 11 9月 27 23:04 16435 -> /tmp/foobar
total 0
-bash-4.2$ ls /tmp/foobar/PG_9.5_201510051/ -l
total 0
drwx------ 2 postgres postgres 40 9月 27 23:13 13294
drwx------ 2 postgres postgres 5400 9月 27 23:20 16439
----- 也可以修改表空间名称
postgres=# ALTER TABLESPACE ts_foobar RENAME TO ts_foobar01;
ALTER TABLESPACE
----- 此时表T的表空间名称会相应发生变化
postgres=# \d t
Table "public.t"
Column | Type | Modifiers
--------+-----------------------+-----------
x | integer |
y | character varying(30) |
z | date |
Indexes:
"uk_t_y" UNIQUE CONSTRAINT, btree (y), tablespace "ts_foobar01"
"idx_t_x" btree (x), tablespace "ts_foobar01"
Tablespace: "ts_foobar01"
----- 也可以把表、数据库在表空间上的移动
postgres=# CREATE TABLESPACE ts_foobar02 LOCATION '/tmp/foobar02';
CREATE TABLESPACE
postgres=# ALTER DATABASE mydb SET TABLESPACE ts_foobar02;
ALTER TABLE
postgres=# ALTER TABLE t SET TABLESPACE ts_foobar02;
ALTER TABLE
----- 修改表空间的默认用户
postgres=# ALTER TABLESPACE ts_foobar OWNER TO 'foobar';
postgres=# ALTER USER foobar SET default_tablespace = 'ts_foobar';----- 查看表空间
postgres@postgres=>SELECT spcname, pg_tablespace_location(oid) FROM pg_tablespace;
spcname | pg_tablespace_location
------------+------------------------
pg_default |
pg_global |
(2 rows)
postgres@postgres=>\db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+----------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 1981 MB |
pg_global | postgres | | | | 639 kB |
(2 rows)
----- 创建表空间，目录需要先创建
postgres=# CREATE TABLESPACE ts_foobar LOCATION '/tmp/foobar';
CREATE TABLESPACE
postgres=# SELECT oid, * FROM pg_tablespace WHERE spcname = 'ts_foobar';
oid | spcname | spcowner | spcacl | spcoptions
-------+-----------+----------+--------+------------
16435 | ts_foobar | 10 | |
(1 row)
postgres=# \db+
List of tablespaces
Name | Owner | Location | Access privileges | Options | Size | Description
------------+----------+-------------+-------------------+---------+---------+-------------
pg_default | postgres | | | | 35 MB |
pg_global | postgres | | | | 464 kB |
ts_foobar | postgres | /tmp/foobar | | | 0 bytes |
(3 rows)
-- 创建数据库，并指向表空间
postgres=# CREATE DATABASE mydb OWNER postgres TABLESPACE ts_foobar;
CREATE DATABASE
postgres=# SELECT oid, datname FROM pg_database;
oid | datname
-------+-----------
1 | template1
12918 | template0
12923 | postgres
16439 | mydb
(4 rows)
postgres=# \connect mydb;
You are now connected to database "mydb" as user "postgres".
----- 在上述创建的表空间中添加表
mydb=# CREATE TABLE foobar(x int, y varchar(30), z date) TABLESPACE ts_foobar;
CREATE TABLE
mydb=# INSERT INTO foobar VALUES(1, 'ShangHai', now()), (2, 'NanJing', now()), (3, 'HangZhou', now());
INSERT 0 3
mydb=# CREATE INDEX idx_foobar_x on foobar(x) TABLESPACE ts_foobar;
CREATE INDEX
----- 添加表的主键约束或是唯一键约束的时候指定表空间
mydb=# ALTER TABLE foobar ADD CONSTRAINT uk_foobar_y UNIQUE(y) USING INDEX TABLESPACE ts_foobar;
ALTER TABLE
------ 查看对应的OID
mydb=# SELECT oid FROM pg_class WHERE relname = 'foobar';
oid
-------
16440
(1 row)
mydb=# SELECT oid FROM pg_database WHERE datname = 'mydb';
oid
-------
16439
(1 row)
----- 查看表所在文件，可以直接在目录下查找对应文件
mydb=# SELECT pg_relation_filepath('foobar');
pg_relation_filepath
----------------------------------------------
pg_tblspc/16435/PG_9.5_201510051/16439/16440
(1 row)
$ ls -l $PGDATA/pg_tblspc
total 0
lrwxrwxrwx 1 postgres postgres 11 9月 27 23:04 16435 -> /tmp/foobar
total 0
-bash-4.2$ ls /tmp/foobar/PG_9.5_201510051/ -l
total 0
drwx------ 2 postgres postgres 40 9月 27 23:13 13294
drwx------ 2 postgres postgres 5400 9月 27 23:20 16439
----- 也可以修改表空间名称
postgres=# ALTER TABLESPACE ts_foobar RENAME TO ts_foobar01;
ALTER TABLESPACE
----- 此时表T的表空间名称会相应发生变化
postgres=# \d t
Table "public.t"
Column | Type | Modifiers
--------+-----------------------+-----------
x | integer |
y | character varying(30) |
z | date |
Indexes:
"uk_t_y" UNIQUE CONSTRAINT, btree (y), tablespace "ts_foobar01"
"idx_t_x" btree (x), tablespace "ts_foobar01"
Tablespace: "ts_foobar01"
----- 也可以把表、数据库在表空间上的移动
postgres=# CREATE TABLESPACE ts_foobar02 LOCATION '/tmp/foobar02';
CREATE TABLESPACE
postgres=# ALTER DATABASE mydb SET TABLESPACE ts_foobar02;
ALTER TABLE
postgres=# ALTER TABLE t SET TABLESPACE ts_foobar02;
ALTER TABLE
----- 修改表空间的默认用户
postgres=# ALTER TABLESPACE ts_foobar OWNER TO 'foobar';
postgres=# ALTER USER foobar SET default_tablespace = 'ts_foobar';当创建 cluster 时，默认会创建 pg_global、pg_default 两个 tablespace，其中前者保存了 cluster-wide 相关的数据，如 pg_database、pg_group 等，当然你不能在该 tablespace 下创建对象。
后者，则保存在 $PGDATA/base 目录下，是系统默认表空间，可通过 set default tablespace=ts-name 指定为其他表空间。
要注意的是，如果创建对象时没有指定 tablespace，它会按照上一级的对象所在 tablespace 创建相应的对象。如，创建 index 时默认与 table 相同；创建 table 时默认与 schema 相同，以此类推
删除
sql
-- 如果删除时表空间中仍然有数据库，那么会报错
postgres=# DROP TABLESPACE ts_foobar;
ERROR: tablespace "ts_foobar" is not empty
----- 查看那些DBs保存在了该表空间中
SELECT d.datname, t.spcname FROM pg_database d JOIN pg_tablespace t ON d.dattablespace = t.oid
WHERE t.spcname = 'ts_foobar';
----- 据说这个有效的，不过DBs的信息没有保存在pg_class中
SELECT c.relname, t.spcname FROM pg_class c JOIN pg_tablespace t ON c.reltablespace = t.oid
WHERE t.spcname = 'ts_foobar';-- 如果删除时表空间中仍然有数据库，那么会报错
postgres=# DROP TABLESPACE ts_foobar;
ERROR: tablespace "ts_foobar" is not empty
----- 查看那些DBs保存在了该表空间中
SELECT d.datname, t.spcname FROM pg_database d JOIN pg_tablespace t ON d.dattablespace = t.oid
WHERE t.spcname = 'ts_foobar';
----- 据说这个有效的，不过DBs的信息没有保存在pg_class中
SELECT c.relname, t.spcname FROM pg_class c JOIN pg_tablespace t ON c.reltablespace = t.oid
WHERE t.spcname = 'ts_foobar';tablesapce 与 schema 的区别
tablespace 实际只影响到 cluster 中数据的存储位置；而 schema 则会影响到数据在 database 中的逻辑组织方式，也就是会影响到一个对象名的解析，而 tablespace 则不会。
也就是说，当创建了 tablespace 之后，实际就不需要再关心了，而 schema 则不同
创建一个只读用户 readonly，将 db1 数据的所有表的只读权限赋予给 readonly 用户
1.创建用户
sql
postgres=# create user readonly with password 'password';postgres=# create user readonly with password 'password';2.切换当前数据库
postgres=# \c db1
You are now connected to database "db1" as user "postgres".postgres=# \c db1
You are now connected to database "db1" as user "postgres".3.给 readonly 用户授权
sql
db1=# grant select on all tables in schema public to readonly;db1=# grant select on all tables in schema public to readonly;4.创建用户并配置密码
sql
#pg10之间版本，创建用户并配置密码
create user moni with password 'moniff61384e';
#创建数据库并属于用户
create database d_moni owner moni;
#授权
grant all privileges on database d_moni to moni;
#pg9.1
postgres@db5-> psql
psql (9.1.3)
Type "help" for help.
postgres=# create role digoal_01 nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'digoal_01';
CREATE ROLE
postgres=# create database digoal_01 encoding 'UTF8' template template0 owner digoal_01;
CREATE DATABASE
postgres=# \c digoal_01 digoal_01
You are now connected to database "digoal_01" as user "digoal_01".
digoal_01=> create schema digoal_01 authorization digoal_01;#pg10之间版本，创建用户并配置密码
create user moni with password 'moniff61384e';
#创建数据库并属于用户
create database d_moni owner moni;
#授权
grant all privileges on database d_moni to moni;
#pg9.1
postgres@db5-> psql
psql (9.1.3)
Type "help" for help.
postgres=# create role digoal_01 nosuperuser nocreatedb nocreaterole noinherit login encrypted password 'digoal_01';
CREATE ROLE
postgres=# create database digoal_01 encoding 'UTF8' template template0 owner digoal_01;
CREATE DATABASE
postgres=# \c digoal_01 digoal_01
You are now connected to database "digoal_01" as user "digoal_01".
digoal_01=> create schema digoal_01 authorization digoal_01;5.修改用户密码
5.1方式1
test1=> \password
Enter new password:
Enter it again:
test1=>
我将原密码hello，修改为hellojava.123456
这种修改方式相当于向postgres server 发送了如下命令：
ALTER USER postgres PASSWORD '02c3c8c0f38ff917487c06eb57d4b984';
后面的字符串是 hellojava经过md5加密后的字符串
12345
注意：尽量不要使用postgres作为用户密码，防止被攻击
或者
psql -c "ALTER USER postgres WITH PASSWORD 'securep@free';"test1=> \password
Enter new password:
Enter it again:
test1=>
我将原密码hello，修改为hellojava.123456
这种修改方式相当于向postgres server 发送了如下命令：
ALTER USER postgres PASSWORD '02c3c8c0f38ff917487c06eb57d4b984';
后面的字符串是 hellojava经过md5加密后的字符串
12345
注意：尽量不要使用postgres作为用户密码，防止被攻击
或者
psql -c "ALTER USER postgres WITH PASSWORD 'securep@free';"5.2sql修改
这种方式不仅仅限于psql了，其余客户端也能修改，如pgadmin等
ALTER USER test1 PASSWORD 'secret'
弊端：通过sql修改，有可能会将修改语句记录在相关工具的log里。 例如：通过psql 运行该条sql，则在.psql_history文件中会有相应语句的记录 有密码泄露的风险
2.PostgreSQL 角色权限管理
在 PG 中，对于不同的对象，可以进行配置的权限是不同的，详细内容查看 PostgreSQL Documentation - GRANT 的定义。例如，数据库有 CREATE、CONNECT 等权限，而表有 SELECT、INSERT、UPDATE、DELETE、TRUNCATE 等权限。
另外，WITH ADMIN OPTION 表示被赋予权限的用户，还可以将对应的权限赋予给其他人
PostgreSQL中使用”角色”的概念，表示用户账户。拥有登录权限的角色称为可登录角色。一个角色可以继承其他角色的权限，从而成为其成员角色，一个拥有成员角色的角色被称为组角色。
PostgreSQL新版本去除了”用户”和”组”的概念，取而待之的是”可登录角色”和”组角色”。但是为保持前向兼容，create user 和 create group 这两个命令依然支持，但建议最好不要使用。
在PostgreSQL 安装过程中数据库初始化，系统会默认创建一个postgres的角色，同时会创建一个同名（postgres）的数据库。安装完成后要做的第一件事就是用psql或者pgAdmin工具以postgres角色身份登录，然后创建其他已规划好的角色
1.CREATE ROLE创建的用户默认不带LOGIN属性，而CREATE USER创建的用户默认带有LOGIN属性，如下：
postgres=# CREATE ROLE pg_test_user_1; /*默认不带LOGIN属性*/
CREATE ROLE
postgres=# CREATE USER pg_test_user_2; /*默认具有LOGIN属性*/
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_1 | Cannot login | {}
pg_test_user_2 | | {}
postgres | Superuser | {}
: Create role
: Create DB
postgres=#postgres=# CREATE ROLE pg_test_user_1; /*默认不带LOGIN属性*/
CREATE ROLE
postgres=# CREATE USER pg_test_user_2; /*默认具有LOGIN属性*/
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_1 | Cannot login | {}
pg_test_user_2 | | {}
postgres | Superuser | {}
: Create role
: Create DB
postgres=#2.在创建用户时赋予角色属性
sql
postgres=# CREATE ROLE pg_test_user_3 CREATEDB; /*具有创建数据库的属性*/
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_1 | Cannot login | {}
pg_test_user_2 | | {}
pg_test_user_3 | Create DB | {}
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=# CREATE ROLE pg_test_user_4 CREATEDB PASSWORD '123456'; /*具有创建数据库及带有密码登陆的属性 */
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_1 | Cannot login | {}
pg_test_user_2 | | {}
pg_test_user_3 | Create DB | {}
: Cannot login
pg_test_user_4 | Create DB | {}
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=#postgres=# CREATE ROLE pg_test_user_3 CREATEDB; /*具有创建数据库的属性*/
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_1 | Cannot login | {}
pg_test_user_2 | | {}
pg_test_user_3 | Create DB | {}
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=# CREATE ROLE pg_test_user_4 CREATEDB PASSWORD '123456'; /*具有创建数据库及带有密码登陆的属性 */
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_1 | Cannot login | {}
pg_test_user_2 | | {}
pg_test_user_3 | Create DB | {}
: Cannot login
pg_test_user_4 | Create DB | {}
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=#3.给已存在用户赋予各种权限
sql
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_3 | Create DB | {}
: Cannot login
pg_test_user_4 | Create DB | {}
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=# ALTER ROLE pg_test_user_3 WITH LOGIN; /*赋予登录权限*/
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_3 | Create DB | {}
pg_test_user_4 | Create DB | {}
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=# ALTER ROLE pg_test_user_4 WITH CREATEROLE;/*赋予创建角色的权限*/
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_3 | Create DB | {}
pg_test_user_4 | Create role | {}
: Create DB
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=# ALTER ROLE pg_test_user_4 WITH PASSWORD '654321';/*修改密码*/
ALTER ROLE
postgres=# ALTER ROLE pg_test_user_4 VALID UNTIL 'JUL 7 14:00:00 2012 +8'; /*设置角色的有效期*
ALTER ROLEpostgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_3 | Create DB | {}
: Cannot login
pg_test_user_4 | Create DB | {}
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=# ALTER ROLE pg_test_user_3 WITH LOGIN; /*赋予登录权限*/
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_3 | Create DB | {}
pg_test_user_4 | Create DB | {}
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=# ALTER ROLE pg_test_user_4 WITH CREATEROLE;/*赋予创建角色的权限*/
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
----------------+--------------+-----------
pg_test_user_3 | Create DB | {}
pg_test_user_4 | Create role | {}
: Create DB
: Cannot login
postgres | Superuser | {}
: Create role
: Create DB
postgres=# ALTER ROLE pg_test_user_4 WITH PASSWORD '654321';/*修改密码*/
ALTER ROLE
postgres=# ALTER ROLE pg_test_user_4 VALID UNTIL 'JUL 7 14:00:00 2012 +8'; /*设置角色的有效期*
ALTER ROLE2.1角色
1.创建普通用户角色
create role username1 login password '123456' createdb valid until 'infinity';
create role username2 login password '123456' craterole valid until 'infinity';
create role username3 login password '123456' login valid until 'infinity’;create role username1 login password '123456' createdb valid until 'infinity';
create role username2 login password '123456' craterole valid until 'infinity';
create role username3 login password '123456' login valid until 'infinity’;valid 是可选的，其功能是为此角色权限设置有效期，过期后所有权限都将失效，默认为'infinity'，即永不过期
createdb修饰符表示为此角色赋予创建新数据库的权限
craterole修饰符表示为此角色赋予创建新角色的权限
login 修饰符表示此角色只有登录权限
2.创建超级用户角色
create role username4 login password '123456' superuser valid until '2020-1-1 00:00:00';
3.修改角色权限
alter role username4 nologin nocreatedb;
2.2组角色
1.创建组角色
create role username5 inherit;
grant username5 to username1;
grant username5 to username2;
Inherit表示username5的任何一个成员角色都将自动继承除“超级用户权限”外的所有权限。出于安全考虑，PostgreSQL不允许超级用户权限通过继承的方式传递。
2.从组角色继承权限
PostgreSQL还有一个奇葩功能是禁止组角色将其权限授予其成员角色，该功能通过NOINHERIT 关键字控制。因此创建组角色时务必显式声明INHERIT或者NOINHERIT关键字。
有些权限无法继承，例如前面提到的SUPERUSER超级用户权限。但是其成员角色可以通过SET ROLE 命令来实现冒名顶替其父角色的身份，从而得到超级用户权限，但是这种冒名顶替仅在当前会话存储期间有效。
例如：username1是username5的成员角色，其可以通过下列命令实现冒名顶替的目的。
SET ROLE username5
当然这是非永久授权行为，一旦会话结束，超级用户权限将被回收
命令：SET SESSION AUTHORIZATION username1,更加强大。
两者比较：
（1）首先，只有具备SUPERUSER权限的角色才可以执行SET SESSION AUTHORIZATION，SET ROLE任何一个成员角色都可以执行；
（2）其次，SET SESSION AUTHORIZATION可以使当前角色扮演系统中任何一个其他角色，SET ROLE仅限于扮演父角色。
（3）从系统内部实现机理看，每个会话会有两个表示当前用户身份的环境变量：一个是session_user，即当前用户登录带的原始身份；一个是current_user，即当前用户所扮演的身份，默认二者一致。SET SESSION AUTHORIZATION命令会将session_user和current_user都替换为所扮演角色的相应身份ID，而SET ROLE只会修改current_user，而保持session_user不变。这也意味着SET SESSION AUTHORIZATION命令会对后续的SET ROLE命令产生影响，因为原始身份session_user发生了变化；而SET ROLE不会对后续的SET SESSION AUTHORIZATION产生影响，因为原始身份session_user未发生变化
2.3删除角色
如果要删除的用户还拥有数据库对象，或者这个用户在某些数据库对象上还拥有权限时，不能删除
sql
----- 重新将某个用户的权限赋值给另外的用户，如果不知道可以赋给postgres
REASSIGN OWNED BY 'olduser' TO 'newuser';
----- 删除赋给该用户的所有权限
DROP OWNED BY 'olduser';----- 重新将某个用户的权限赋值给另外的用户，如果不知道可以赋给postgres
REASSIGN OWNED BY 'olduser' TO 'newuser';
----- 删除赋给该用户的所有权限
DROP OWNED BY 'olduser';2.4角色与用户的关系
在 PostgreSQL 中，实际上这是两个完全相同的对象，唯一的区别是在通过 CREATE USER 命令创建时会默认添加 LOGIN 权限
sql
----- 只创建角色，则在尝试登陆的时候会报错
postgres=# CREATE ROLE foobar1 PASSWORD 'kidding';
----- 添加登陆的权限
postgres=# ALTER ROLE foobar1 LOGIN;
----- 创建用户时，默认会添加LOGIN权限
postgres=# CREATE USER foobar2 PASSWORD 'kidding';
postgres=# CREATE ROLE foobar2 PASSWORD 'kidding' LOGIN;----- 只创建角色，则在尝试登陆的时候会报错
postgres=# CREATE ROLE foobar1 PASSWORD 'kidding';
----- 添加登陆的权限
postgres=# ALTER ROLE foobar1 LOGIN;
----- 创建用户时，默认会添加LOGIN权限
postgres=# CREATE USER foobar2 PASSWORD 'kidding';
postgres=# CREATE ROLE foobar2 PASSWORD 'kidding' LOGIN;在 PG 中，默认有一个 public 角色，代表所有人的意思
2.5查看权限
sql
postgres@han_db=>select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+------------------------------
test | 17732 | f | f | f | f | ******** | |
app | 17735 | f | f | f | f | ******** | | {default_tablespace=tbl_app}
postgres | 10 | t | t | t | t | ******** | |
readonly | 17803 | f | f | f | f | ******** | |
(4 rows)
postgres@han_db=>\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
app | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readonly | | {}
test | | {}
postgres@han_db=>select * from information_schema.table_privileges where grantee='用户';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)postgres@han_db=>select * from pg_user;
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+------------------------------
test | 17732 | f | f | f | f | ******** | |
app | 17735 | f | f | f | f | ******** | | {default_tablespace=tbl_app}
postgres | 10 | t | t | t | t | ******** | |
readonly | 17803 | f | f | f | f | ******** | |
(4 rows)
postgres@han_db=>\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
app | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
readonly | | {}
test | | {}
postgres@han_db=>select * from information_schema.table_privileges where grantee='用户';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
(0 rows)[!WARNING]
查看用户拥有哪些权限，需要切换到所在数据库下，然后进行查看
text
postgres=arwdDxt/postgres
表示postgres这个角色所拥有的权限，对于public则为空；对应的权限为arwdDxt，相应含义如下；该权限是postgres所赋于的。
rolename=xxxx -- privileges granted to a role
=xxxx -- privileges granted to PUBLIC
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege
/yyyy -- role that granted this privilegepostgres=arwdDxt/postgres
表示postgres这个角色所拥有的权限，对于public则为空；对应的权限为arwdDxt，相应含义如下；该权限是postgres所赋于的。
rolename=xxxx -- privileges granted to a role
=xxxx -- privileges granted to PUBLIC
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege
/yyyy -- role that granted this privilege3.用户权限管理
用户权限管理文档
注意：
创建好用户（角色）之后需要连接的话，还需要修改2个权限控制的配置文件（pg_hba.conf、pg_ident.conf）。并且创建用户（user）和创建角色（role）一样，唯一的区别是用户默认可以登录，而创建的角色默认不能登录。创建用户和角色的各个参数选项是一样
**安装*PostgreSQL*会自动创建一个postgres用户，需要切换到该用户下访问PostgreSQL
CREATE USER/ROLE name [ [ WITH ] option [ ... ] ] : 关键词 USER,ROLE； name 用户或角色名；
where option can be:
SUPERUSER | NOSUPERUSER :超级权限，拥有所有权限，默认nosuperuser。
| CREATEDB | NOCREATEDB :建库权限，默认nocreatedb。
| CREATEROLE | NOCREATEROLE :建角色权限，拥有创建、修改、删除角色，默认nocreaterole。
| INHERIT | NOINHERIT :继承权限，可以把除superuser权限继承给其他用户/角色，默认inherit。
| LOGIN | NOLOGIN :登录权限，作为连接的用户，默认nologin，除非是create user（默认登录）。
| REPLICATION | NOREPLICATION :复制权限，用于物理或则逻辑复制（复制和删除slots），默认是noreplication。
| BYPASSRLS | NOBYPASSRLS :安全策略RLS权限，默认nobypassrls。
| CONNECTION LIMIT connlimit :限制用户并发数，默认-1，不限制。正常连接会受限制，后台连接和prepared事务不受限制。
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL :设置密码，密码仅用于有login属性的用户，不使用密码身份验证，则可以省略此选项。可以选择将空密码显式写为PASSWORD NULL。
加密方法由配置参数password_encryption确定，密码始终以加密方式存储在系统目录中。
| VALID UNTIL 'timestamp' :密码有效期时间，不设置则用不失效。
| IN ROLE role_name [, ...] :新角色将立即添加为新成员。
| IN GROUP role_name [, ...] :同上
| ROLE role_name [, ...] :ROLE子句列出一个或多个现有角色，这些角色自动添加为新角色的成员。 （这实际上使新角色成为“组”）。
| ADMIN role_name [, ...] :与ROLE类似，但命名角色将添加到新角色WITH ADMIN OPTION，使他们有权将此角色的成员资格授予其他人。
| USER role_name [, ...] :同上
| SYSID uid :被忽略，但是为向后兼容性而存在。
示例：
3.1 创建不需要密码登陆的用户zjy：
postgres=# CREATE ROLE zjy LOGIN;
CREATE ROLEpostgres=# CREATE ROLE zjy LOGIN;
CREATE ROLE创建该用户后，还不能直接登录。需要修改 pg_hba.conf 文件（后面会对该文件进行说明），加入：
①：本地登陆：local all all **trust **②：远程登陆：host all all 192.168.163.132/32 trust
3.2 创建需要密码登陆的用户zjy
postgres=# CREATE USER zjy1 WITH PASSWORD 'zjy1';
CREATE ROLEpostgres=# CREATE USER zjy1 WITH PASSWORD 'zjy1';
CREATE ROLE{% em %} 和ROLE的区别是：USER带LOGIN属性{% endem %}。也需要修改 pg_hba.conf 文件（后面会对该文件进行说明），加入： host all all 192.168.163.132/32 md5
3.3 创建有时间限制的用户zjy2
postgres=# CREATE ROLE zjy2 WITH LOGIN PASSWORD 'zjy2' VALID UNTIL '2019-05-30';
CREATE ROLEpostgres=# CREATE ROLE zjy2 WITH LOGIN PASSWORD 'zjy2' VALID UNTIL '2019-05-30';
CREATE ROLE和.3.2的处理方法一样，修改 pg_hba.conf 文件，该用户会的密码在给定的时间之后过期不可用
3.4 创建有创建数据库和管理角色权限的用户admin
postgres=# CREATE ROLE admin WITH CREATEDB CREATEROLE;
CREATE ROLEpostgres=# CREATE ROLE admin WITH CREATEDB CREATEROLE;
CREATE ROLE注意：拥有创建数据库，角色的用户，也可以删除和修改这些对象
3.5 创建具有超级权限的用户：admin
postgres=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin';
CREATE ROLEpostgres=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin';
CREATE ROLE3.6 创建复制账号：repl
postgres=# CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl';
CREATE ROLEpostgres=# CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl';
CREATE ROLE3.7 其他说明
sql
创建复制用户
CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD '';
CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD 'abc';
ALTER USER work WITH ENCRYPTED password '';
创建scheme 角色
CREATE ROLE abc;
CREATE DATABASE abc WITH OWNER abc ENCODING UTF8 TEMPLATE template0;
\c abc
创建schema
CREATE SCHEMA abc;
ALTER SCHEMA abc OWNER to abc;
revoke create on schema public from public;
创建用户
create user abc with ENCRYPTED password '';
GRANT abc to abc;
ALTER ROLE abc WITH abc;
##创建读写账号
CREATE ROLE abc_rw;
CREATE ROLE abc_rr;
##赋予访问数据库权限，schema权限
grant connect ON DATABASE abc to abc_rw;
GRANT USAGE ON SCHEMA abc TO abc_rw;
##赋予读写权限
grant select,insert,update,delete ON ALL TABLES IN SCHEMA abc to abc;
赋予序列权限
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA abc to abc;
赋予默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select,insert,update,delete ON TABLES TO abc;
赋予序列权限
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT ALL PRIVILEGES ON SEQUENCES TO abc;
#用户对db要有连接权限
grant connect ON DATABASE abc to abc;
#用户要对schema usage 权限，不然要select * from schema_name.table ,不能用搜索路径
GRANT USAGE ON SCHEMA abc TO abc;
grant select ON ALL TABLES IN SCHEMA abc to abc;
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select ON TABLES TO abc;
create user abc_w with ENCRYPTED password '';
create user abc_r with ENCRYPTED password '';
GRANT abc_rw to abc_w；
GRANT abc_rr to abc_r;创建复制用户
CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD '';
CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD 'abc';
ALTER USER work WITH ENCRYPTED password '';
创建scheme 角色
CREATE ROLE abc;
CREATE DATABASE abc WITH OWNER abc ENCODING UTF8 TEMPLATE template0;
\c abc
创建schema
CREATE SCHEMA abc;
ALTER SCHEMA abc OWNER to abc;
revoke create on schema public from public;
创建用户
create user abc with ENCRYPTED password '';
GRANT abc to abc;
ALTER ROLE abc WITH abc;
##创建读写账号
CREATE ROLE abc_rw;
CREATE ROLE abc_rr;
##赋予访问数据库权限，schema权限
grant connect ON DATABASE abc to abc_rw;
GRANT USAGE ON SCHEMA abc TO abc_rw;
##赋予读写权限
grant select,insert,update,delete ON ALL TABLES IN SCHEMA abc to abc;
赋予序列权限
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA abc to abc;
赋予默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select,insert,update,delete ON TABLES TO abc;
赋予序列权限
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT ALL PRIVILEGES ON SEQUENCES TO abc;
#用户对db要有连接权限
grant connect ON DATABASE abc to abc;
#用户要对schema usage 权限，不然要select * from schema_name.table ,不能用搜索路径
GRANT USAGE ON SCHEMA abc TO abc;
grant select ON ALL TABLES IN SCHEMA abc to abc;
ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select ON TABLES TO abc;
create user abc_w with ENCRYPTED password '';
create user abc_r with ENCRYPTED password '';
GRANT abc_rw to abc_w；
GRANT abc_rr to abc_r;4.授权，定义访问权限
- 获取权限列表
sql
postgres=# \dp+ *.*postgres=# \dp+ *.*- \dp 如何获取权限列表的
[ptgres@beta ~]$ psql -E -U postgres -h 127.0.0.1 -p 5532
#通过-E,可以查看快捷命令执行过程[ptgres@beta ~]$ psql -E -U postgres -h 127.0.0.1 -p 5532
#通过-E,可以查看快捷命令执行过程- 查看单独表权限
Use psql's \dp command to obtain information about existing privileges for tables and columns. For example:
=> \dp mytable
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------+-------+-----------------------+--------------------------
public | mytable | table | miriam=arwdDxt/miriam | col1:
: =r/miriam : miriam_rw=rw/miriam
: admin=arw/miriam
(1 row)
The entries shown by \dp are interpreted thus:
rolename=xxxx -- privileges granted to a role
=xxxx -- privileges granted to PUBLIC
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege
/yyyy -- role that granted this privilege
The above example display would be seen by user miriam after creating table mytable and doing:
GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
#https://github.com/digoal/blog/blob/master/201702/20170208_01.mdUse psql's \dp command to obtain information about existing privileges for tables and columns. For example:
=> \dp mytable
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+---------+-------+-----------------------+--------------------------
public | mytable | table | miriam=arwdDxt/miriam | col1:
: =r/miriam : miriam_rw=rw/miriam
: admin=arw/miriam
(1 row)
The entries shown by \dp are interpreted thus:
rolename=xxxx -- privileges granted to a role
=xxxx -- privileges granted to PUBLIC
r -- SELECT ("read")
w -- UPDATE ("write")
a -- INSERT ("append")
d -- DELETE
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege
/yyyy -- role that granted this privilege
The above example display would be seen by user miriam after creating table mytable and doing:
GRANT SELECT ON mytable TO PUBLIC;
GRANT SELECT, UPDATE, INSERT ON mytable TO admin;
GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
#https://github.com/digoal/blog/blob/master/201702/20170208_01.mdsql
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
##单表授权：授权zjy账号可以访问schema为zjy的zjy表
grant select,insert,update,delete on zjy.zjy to zjy;
##所有表授权：
grant select,insert,update,delete on all tables in schema zjy to zjy;
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##列授权，授权指定列(zjy schema下的zjy表的name列)的更新权限给zjy用户
grant update (name) on zjy.zjy to zjy;
##指定列授不同权限，zjy schema下的zjy表，查看更新name、age字段，插入name字段
grant select (name,age),update (name,age),insert(name) on zjy.xxx to zjy;
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
##序列（自增键）属性授权，指定zjy schema下的seq_id_seq 给zjy用户
grant select,update on sequence zjy.seq_id_seq to zjy;
##序列（自增键）属性授权，给用户zjy授权zjy schema下的所有序列
grant select,update on all sequences in schema zjy to zjy;
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##连接数据库权限，授权cc用户连接数据库zjy
grant connect on database zjy to cc;
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##连接schema权限，授权cc访问zjy schema权限
grant usage on schema zjy to cc;
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
##把zjy用户的权限授予用户cc。
grant zjy to cc;GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
##单表授权：授权zjy账号可以访问schema为zjy的zjy表
grant select,insert,update,delete on zjy.zjy to zjy;
##所有表授权：
grant select,insert,update,delete on all tables in schema zjy to zjy;
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##列授权，授权指定列(zjy schema下的zjy表的name列)的更新权限给zjy用户
grant update (name) on zjy.zjy to zjy;
##指定列授不同权限，zjy schema下的zjy表，查看更新name、age字段，插入name字段
grant select (name,age),update (name,age),insert(name) on zjy.xxx to zjy;
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
##序列（自增键）属性授权，指定zjy schema下的seq_id_seq 给zjy用户
grant select,update on sequence zjy.seq_id_seq to zjy;
##序列（自增键）属性授权，给用户zjy授权zjy schema下的所有序列
grant select,update on all sequences in schema zjy to zjy;
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##连接数据库权限，授权cc用户连接数据库zjy
grant connect on database zjy to cc;
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } routine_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
##连接schema权限，授权cc访问zjy schema权限
grant usage on schema zjy to cc;
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
where role_specification can be:
[ GROUP ] role_name
| PUBLIC
| CURRENT_USER
| SESSION_USER
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
##把zjy用户的权限授予用户cc。
grant zjy to cc;4.1 权限说明
sql
SELECT：允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。也允许使用COPY TO。在UPDATE或DELETE中引用现有列值也需要此权限。对于序列，此权限还允许使用currval函数。对于大对象，此权限允许读取对象。
INSERT：允许将新行INSERT到指定的表中。如果列出了特定列，则只能在INSERT命令中为这些列分配（因此其他列将接收默认值）。也允许COPY FROM。
UPDATE：允许更新指定表的任何列或列出的特定列，需要SELECT权限。
DELETE：允许删除指定表中的行，需要SELECT权限。
TRUNCATE：允许在指定的表上创建触发器。
REFERENCES：允许创建引用指定表或表的指定列的外键约束。
TRIGGER：允许在指定的表上创建触发器。
CREATE：对于数据库，允许在数据库中创建新的schema、table、index。
CONNECT：允许用户连接到指定的数据库。在连接启动时检查此权限。
TEMPORARY、TEMP：允许在使用指定数据库时创建临时表。
EXECUTE：允许使用指定的函数或过程以及在函数。
USAGE：对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。
ALL PRIVILEGES：一次授予所有可用权限。SELECT：允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。也允许使用COPY TO。在UPDATE或DELETE中引用现有列值也需要此权限。对于序列，此权限还允许使用currval函数。对于大对象，此权限允许读取对象。
INSERT：允许将新行INSERT到指定的表中。如果列出了特定列，则只能在INSERT命令中为这些列分配（因此其他列将接收默认值）。也允许COPY FROM。
UPDATE：允许更新指定表的任何列或列出的特定列，需要SELECT权限。
DELETE：允许删除指定表中的行，需要SELECT权限。
TRUNCATE：允许在指定的表上创建触发器。
REFERENCES：允许创建引用指定表或表的指定列的外键约束。
TRIGGER：允许在指定的表上创建触发器。
CREATE：对于数据库，允许在数据库中创建新的schema、table、index。
CONNECT：允许用户连接到指定的数据库。在连接启动时检查此权限。
TEMPORARY、TEMP：允许在使用指定数据库时创建临时表。
EXECUTE：允许使用指定的函数或过程以及在函数。
USAGE：对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。
ALL PRIVILEGES：一次授予所有可用权限。4.2 撤销权限
sql
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##移除用户zjy在schema zjy上所有表的select权限
revoke select on all tables in schema zjy from zjy;
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##移除用户zjy在zjy schema的zjy表的age列的查询权限
revoke select (age) on zjy.zjy from zjy;
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##序列
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##库
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT]
##
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##schena权限
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ ADMIN OPTION FOR ]
role_name [, ...] FROM role_name [, ...]
[ CASCADE | RESTRICT ]
##REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##移除用户zjy在schema zjy上所有表的select权限
revoke select on all tables in schema zjy from zjy;
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##移除用户zjy在zjy schema的zjy表的age列的查询权限
revoke select (age) on zjy.zjy from zjy;
REVOKE [ GRANT OPTION FOR ]
{ { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]
| ALL SEQUENCES IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##序列
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##库
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN domain_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER fdw_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER server_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT]
##
REVOKE [ GRANT OPTION FOR ]
{ EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } function_name [ ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA schema_name [, ...] }
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE lang_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##schena权限
REVOKE [ GRANT OPTION FOR ]
{ CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE tablespace_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ GRANT OPTION FOR ]
{ USAGE | ALL [ PRIVILEGES ] }
ON TYPE type_name [, ...]
FROM { [ GROUP ] role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
##
REVOKE [ ADMIN OPTION FOR ]
role_name [, ...] FROM role_name [, ...]
[ CASCADE | RESTRICT ]
##注意：
任何用户对public的schema都有all的权限，为了安全可以禁止用户对public schema
sql
##移除所有用户（public），superuser除外，对指定DB下的public schema的create 权限。
zjy=# revoke create on schema public from public;
REVOKE##移除所有用户（public），superuser除外，对指定DB下的public schema的create 权限。
zjy=# revoke create on schema public from public;
REVOKE**示例： ** 注意：option选项里的用户都可以通过alter role进行修改
修改用户为超级/非超级用户
alter role caocao with superuser/nosuperuser;alter role caocao with superuser/nosuperuser;修改用户为可/不可登陆用户
alter role caocao with nologin/login;alter role caocao with nologin/login;修改用户名：
alter role caocao rename to youxing;alter role caocao rename to youxing;修改用户密码，移除密码用NULL
alter role youxing with password 'youxing';alter role youxing with password 'youxing';修改用户参数，该用户登陆后的以该参数为准
sqlalter role zjy in database zjy SET geqo to 0/default;alter role zjy in database zjy SET geqo to 0/default;
4.3 赋予普通用户a创建数据库的权限
sql
alter user username createdb ;alter user username createdb ;4.4 查看用户权限
1、查看某用户的表权限
sql
postgres=# select * from information_schema.table_privileges where grantee='postgres';postgres=# select * from information_schema.table_privileges where grantee='postgres';2、查看usage权限表
sql
postgres=# select * from information_schema.usage_privileges where grantee='postgres';
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
----------+----------+----------------+--------------------+-----------------+-------------+----------------+--------------
postgres | postgres | postgres | information_schema | cardinal_number | DOMAIN | USAGE | YES
postgres | postgres | postgres | information_schema | character_data | DOMAIN | USAGE | YES
postgres | postgres | postgres | information_schema | sql_identifier | DOMAIN | USAGE | YES
postgres | postgres | postgres | information_schema | time_stamp | DOMAIN | USAGE | YES
postgres | postgres | postgres | information_schema | yes_or_no | DOMAIN | USAGEpostgres=# select * from information_schema.usage_privileges where grantee='postgres';
grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable
----------+----------+----------------+--------------------+-----------------+-------------+----------------+--------------
postgres | postgres | postgres | information_schema | cardinal_number | DOMAIN | USAGE | YES
postgres | postgres | postgres | information_schema | character_data | DOMAIN | USAGE | YES
postgres | postgres | postgres | information_schema | sql_identifier | DOMAIN | USAGE | YES
postgres | postgres | postgres | information_schema | time_stamp | DOMAIN | USAGE | YES
postgres | postgres | postgres | information_schema | yes_or_no | DOMAIN | USAGE3、查看存储过程函数相关权限表
select * from information_schema.routine_privileges where grantee='user_name';select * from information_schema.routine_privileges where grantee='user_name';5.数据库级别
数据库级别的权限，包括允许连接数据库，允许在数据库中创建schema
- 默认创建后，允许 public 角色 (任何人) 连接；
- 默认创建后，不允许除了超级用户和 owner 之外的任何人在数据库中创建 schema；
- 默认创建后，会自动创建名为 public 的 schema，这个 schema 的 all 权限已经赋予给 public 角色，即允许任何人在里面创建对象。
这意味着，默认所有角色可以在新建的数据库中创建对象
6.schema 级别
schema 级别的权限，包括允许查看 schema 中的对象，允许在 schema 中创建对象。默认情况下新建的 schema 的权限不会赋予给 public 角色，因此除了超级用户和 owner，任何人都没有权限查看 schema 中的对象或者在 schema 中新建对象
7.线上案例
现在按照一个正常项目上线的流程来创建一个应用账号为例
比如一个项目zjy上线：用管理账号来操作
创建数据库：
sqlpostgres=# create database zjy; CREATE DATABASEpostgres=# create database zjy; CREATE DATABASE创建账号：账号和数据库名字保持一致（search_path）
sqlpostgres=# create user zjy with password 'zjy'; CREATE ROLEpostgres=# create user zjy with password 'zjy'; CREATE ROLE创建schema：不能用默认的public的schma
sqlpostgres=# \c zjy You are now connected to database "zjy" as user "postgres". zjy=# create schema zjy; CREATE SCHEMApostgres=# \c zjy You are now connected to database "zjy" as user "postgres". zjy=# create schema zjy; CREATE SCHEMA
授权
sql
#访问库
zjy=# grant connect on database zjy to zjy;
GRANT
#访问schmea
zjy=# grant usage on schema zjy to zjy;
GRANT
#访问表
zjy=# grant select,insert,update,delete on all tables in schema zjy to zjy;
GRANT
#如果访问自增序列，需要授权
zjy=# grant select,update on all sequences in schema zjy to zjy;
GRANT
注意：上面的授权只对历史的一些对象授权，后期增加的对象是没有权限的，需要给个默认权限
#默认表权限
zjy=# ALTER DEFAULT PRIVILEGES IN SCHEMA zjy GRANT select,insert,update,delete ON TABLES TO zjy;
ALTER DEFAULT PRIVILEGES
#默认自增序列权限
zjy=# ALTER DEFAULT PRIVILEGES IN SCHEMA zjy GRANT select,update ON sequences TO zjy;
ALTER DEFAULT PRIVILEGES#访问库
zjy=# grant connect on database zjy to zjy;
GRANT
#访问schmea
zjy=# grant usage on schema zjy to zjy;
GRANT
#访问表
zjy=# grant select,insert,update,delete on all tables in schema zjy to zjy;
GRANT
#如果访问自增序列，需要授权
zjy=# grant select,update on all sequences in schema zjy to zjy;
GRANT
注意：上面的授权只对历史的一些对象授权，后期增加的对象是没有权限的，需要给个默认权限
#默认表权限
zjy=# ALTER DEFAULT PRIVILEGES IN SCHEMA zjy GRANT select,insert,update,delete ON TABLES TO zjy;
ALTER DEFAULT PRIVILEGES
#默认自增序列权限
zjy=# ALTER DEFAULT PRIVILEGES IN SCHEMA zjy GRANT select,update ON sequences TO zjy;
ALTER DEFAULT PRIVILEGES8.常用命令
8.1查看当前用户
sql
zjy=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser, Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
zjy | | {}
zjy=# select * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10
admin | t | t | f | f | f | f | -1 | ******** | | f | | 16456
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
zjy | f | t | f | f | t | f | -1 | ******** | | f | | 16729
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
(8 rows)
#或者
查看当前连接的用户名：
foo=#select * from current_user;
或
foo=#select user;
查看所有用户名：
foo=#\duzjy=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser, Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
zjy | | {}
zjy=# select * from pg_roles;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig | oid
----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
pg_signal_backend | f | t | f | f | f | f | -1 | ******** | | f | | 4200
postgres | t | t | t | t | t | t | -1 | ******** | | t | | 10
admin | t | t | f | f | f | f | -1 | ******** | | f | | 16456
pg_read_all_stats | f | t | f | f | f | f | -1 | ******** | | f | | 3375
zjy | f | t | f | f | t | f | -1 | ******** | | f | | 16729
pg_monitor | f | t | f | f | f | f | -1 | ******** | | f | | 3373
pg_read_all_settings | f | t | f | f | f | f | -1 | ******** | | f | | 3374
pg_stat_scan_tables | f | t | f | f | f | f | -1 | ******** | | f | | 3377
(8 rows)
#或者
查看当前连接的用户名：
foo=#select * from current_user;
或
foo=#select user;
查看所有用户名：
foo=#\du8.2查看用户权限
sql
zjy=# select * from information_schema.table_privileges where grantee='zjy';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+---------+---------------+--------------+------------+----------------+--------------+----------------
postgres | zjy | zjy | zjy | zjy | INSERT | NO | NO
postgres | zjy | zjy | zjy | zjy | SELECT | NO | YES
postgres | zjy | zjy | zjy | zjy | UPDATE | NO | NO
postgres | zjy | zjy | zjy | zjy | DELETE | NO | NOzjy=# select * from information_schema.table_privileges where grantee='zjy';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
----------+---------+---------------+--------------+------------+----------------+--------------+----------------
postgres | zjy | zjy | zjy | zjy | INSERT | NO | NO
postgres | zjy | zjy | zjy | zjy | SELECT | NO | YES
postgres | zjy | zjy | zjy | zjy | UPDATE | NO | NO
postgres | zjy | zjy | zjy | zjy | DELETE | NO | NO9，案例用户权限
三个独自子系统bas、app1、app3 bas系统的数据为app1和app3系统所共有的基础数据 app1可修改bas系统数据，但不能删除 app3只能查询bas系统数据，不能修改和删除 需求：只需配置一次，后续新增表无需再次配置
10.案例schema 和 database 权限
关于 schema 使用，需要特别注意，同一个 schema 中可能会有其它用户读取，也就是说，千万不要把自己的对象创建到别人的 schema下面
示例1
r1 创建了一个 schema r1，并把这个 schema 的所有权限给了 r2；此时，r2 和超级用户 postgres 分别在 r1 这个 schema 下面创建了一个表；此时，r1 可以把 r2 和 postgres 在 schema r1 下创建的表删掉
text
postgres=# CREATE ROLE r1 LOGIN;
CREATE ROLE
postgres=# CREATE ROLE r2 LOGIN;
CREATE ROLE
postgres=# GRANT ALL ON DATABASE postgres TO r1;
GRANT
postgres=# GRANT ALL ON DATABASE postgres TO r2;
GRANT
postgres=# \c postgres r1;
You are now connected to database "postgres" as user "r1".
postgres=> CREATE SCHEMA r1;
CREATE SCHEMA
postgres=> GRANT ALL ON SCHEMA r1 TO r2;
GRANT
postgres=> \c postgres r2;
You are now connected to database "postgres" as user "r2".
postgres=> CREATE TABLE r1.t(id int);
CREATE TABLE
postgres=> \c postgres postgres
postgres=# CREATE TABLE r1.t1(id int);
CREATE TABLE
postgres=# \c postgres r1
postgres=> DROP TABLE r1.t;
DROP TABLE
postgres=> DROP TABLE r1.t1;
DROP TABLEpostgres=# CREATE ROLE r1 LOGIN;
CREATE ROLE
postgres=# CREATE ROLE r2 LOGIN;
CREATE ROLE
postgres=# GRANT ALL ON DATABASE postgres TO r1;
GRANT
postgres=# GRANT ALL ON DATABASE postgres TO r2;
GRANT
postgres=# \c postgres r1;
You are now connected to database "postgres" as user "r1".
postgres=> CREATE SCHEMA r1;
CREATE SCHEMA
postgres=> GRANT ALL ON SCHEMA r1 TO r2;
GRANT
postgres=> \c postgres r2;
You are now connected to database "postgres" as user "r2".
postgres=> CREATE TABLE r1.t(id int);
CREATE TABLE
postgres=> \c postgres postgres
postgres=# CREATE TABLE r1.t1(id int);
CREATE TABLE
postgres=# \c postgres r1
postgres=> DROP TABLE r1.t;
DROP TABLE
postgres=> DROP TABLE r1.t1;
DROP TABLEr1 甚至可以直接 DROP SCHEMA CASCADE 来删除整个 schema
示例2
对于 database 的 owner 也存在这个问题，它同样具有删除 database 中任何其他用户创建的对象的权力
text
----- 添加创建数据库的权限
postgres=# ALTER USER r1 WITH CREATEDB;
----- 普通用户r1创建的数据库
postgres=# \c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=> CREATE DATABASE db1;
CREATE DATABASE
postgres=> GRANT ALL ON DATABASE db1 TO r2;
GRANT
----- 其他用户在这个数据库中创建对象
postgres=> \c db1 r2
You are now connected to database "db1" as user "r2".
db1=> CREATE SCHEMA r2;
CREATE SCHEMA
db1=> CREATE TABLE r2.t(id int);
CREATE TABLE
db1=> \c db1 postgres
You are now connected to database "db1" as user "postgres".
db1=# CREATE TABLE t(id int);
CREATE TABLE
----- 数据库的OWNER不能直接删数据库中的对象
postgres=> \c db1 r1
You are now connected to database "db1" as user "r1".
db1=> DROP TABLE r2.t ;
ERROR: permission denied for schema r2
db1=> DROP TABLE public.t ;
ERROR: must be owner of relation t
db1=> DROP SCHEMA r2;
ERROR: must be owner of schema r2
db1=> DROP SCHEMA public;
ERROR: must be owner of schema public
----- 但是可以直接删库
postgres=> DROP DATABASE db1;
DROP DATABASE----- 添加创建数据库的权限
postgres=# ALTER USER r1 WITH CREATEDB;
----- 普通用户r1创建的数据库
postgres=# \c postgres r1
You are now connected to database "postgres" as user "r1".
postgres=> CREATE DATABASE db1;
CREATE DATABASE
postgres=> GRANT ALL ON DATABASE db1 TO r2;
GRANT
----- 其他用户在这个数据库中创建对象
postgres=> \c db1 r2
You are now connected to database "db1" as user "r2".
db1=> CREATE SCHEMA r2;
CREATE SCHEMA
db1=> CREATE TABLE r2.t(id int);
CREATE TABLE
db1=> \c db1 postgres
You are now connected to database "db1" as user "postgres".
db1=# CREATE TABLE t(id int);
CREATE TABLE
----- 数据库的OWNER不能直接删数据库中的对象
postgres=> \c db1 r1
You are now connected to database "db1" as user "r1".
db1=> DROP TABLE r2.t ;
ERROR: permission denied for schema r2
db1=> DROP TABLE public.t ;
ERROR: must be owner of relation t
db1=> DROP SCHEMA r2;
ERROR: must be owner of schema r2
db1=> DROP SCHEMA public;
ERROR: must be owner of schema public
----- 但是可以直接删库
postgres=> DROP DATABASE db1;
DROP DATABASE[!WARNING]
介于此，建议用户使用超级用户创建 schema 和 database，然后再把 schema 和 database 的读写权限给普通用户，这样就不怕被误删了，因为超级用户本来就有所有权限。
还有一种方法是创建事件触发器，当执行 DROP 命令时，只有 OWNER 和超级用户能删对应的对象
11.只读用户设计
1. 使用超级用户创建读写账号，创建数据库, 创建schema
sql
postgres=# CREATE ROLE appuser LOGIN;
CREATE ROLE
postgres=# CREATE DATABASE appdb;
CREATE DATABASE
----- 使用超级用户创建schema
postgres=# \c appdb postgres
You are now connected to database "appdb" as user "postgres".
appdb=# CREATE SCHEMA appuser;
CREATE SCHEMA
----- 赋权
appdb=# GRANT CONNECT ON DATABASE appdb TO appuser; -- 只赋予连接权限
GRANT
appdb=# GRANT ALL ON SCHEMA appuser TO appuser; -- 值赋予读和写权限
GRANTpostgres=# CREATE ROLE appuser LOGIN;
CREATE ROLE
postgres=# CREATE DATABASE appdb;
CREATE DATABASE
----- 使用超级用户创建schema
postgres=# \c appdb postgres
You are now connected to database "appdb" as user "postgres".
appdb=# CREATE SCHEMA appuser;
CREATE SCHEMA
----- 赋权
appdb=# GRANT CONNECT ON DATABASE appdb TO appuser; -- 只赋予连接权限
GRANT
appdb=# GRANT ALL ON SCHEMA appuser TO appuser; -- 值赋予读和写权限
GRANT2. 假设该读写账号已经创建了一些对象
text
postgres=# \c appdb appuser
appdb=> CREATE TABLE tbl1(id INT);
CREATE TABLE
appdb=> CREATE TABLE tbl2(id INT);
CREATE TABLE
appdb=> CREATE TABLE tbl3(id INT);
CREATE TABLEpostgres=# \c appdb appuser
appdb=> CREATE TABLE tbl1(id INT);
CREATE TABLE
appdb=> CREATE TABLE tbl2(id INT);
CREATE TABLE
appdb=> CREATE TABLE tbl3(id INT);
CREATE TABLE3. 创建只读影子账号
text
postgres=# CREATE ROLE readonly LOGIN;
CREATE ROLE
postgres=# \c appdb postgres
You are now connected to database "appdb" as user "postgres".
appdb=# GRANT CONNECT ON DATABASE appdb TO readonly;
GRANT
appdb=# GRANT USAGE ON SCHEMA appuser TO readonly;
GRANTpostgres=# CREATE ROLE readonly LOGIN;
CREATE ROLE
postgres=# \c appdb postgres
You are now connected to database "appdb" as user "postgres".
appdb=# GRANT CONNECT ON DATABASE appdb TO readonly;
GRANT
appdb=# GRANT USAGE ON SCHEMA appuser TO readonly;
GRANT4. 创建隐藏敏感信息的视图
假设tbl2是敏感信息表，需要加密后给只读用户看
text
postgres=# \c appdb appuser
appdb=> CREATE VIEW v AS SELECT md5(id::text) FROM tbl2;
CREATE VIEWpostgres=# \c appdb appuser
appdb=> CREATE VIEW v AS SELECT md5(id::text) FROM tbl2;
CREATE VIEW5. 修改已有权限
另外还提供了一种方法，但是一定要指定 schema，所以用户自己要注意，如果要对所有 schema 操作，需要把所有的 schema 都写进去
text
grant select on all tables in schema public,schema1,schema2,schema3 to ro;grant select on all tables in schema public,schema1,schema2,schema3 to ro;并且这种方法还有一个弊端，如果这些schema下面有其他用户创建的对象，也会被赋予，如果赋权的账号没有权限，则会报错
6.修改新建对象的默认权限
text
appdb=> alter default privileges for role appuser grant select on tables to ro;
ALTER DEFAULT PRIVILEGES
appdb=> \ddp+
Default access privileges
Owner | Schema | Type | Access privileges
----------+--------+-------+---------------------------
appuser | | table | appuser=arwdDxt/appuser +
| | | ro=r/appuserappdb=> alter default privileges for role appuser grant select on tables to ro;
ALTER DEFAULT PRIVILEGES
appdb=> \ddp+
Default access privileges
Owner | Schema | Type | Access privileges
----------+--------+-------+---------------------------
appuser | | table | appuser=arwdDxt/appuser +
| | | ro=r/appuser新增的敏感表，先创建视图，同时回收表的权限
text
appuser=> create table tbl4(id int);
CREATE TABLE
appuser=> create view v2 as select md5(id::text) from tbl4;
CREATE VIEW
appuser=> revoke select on tbl4 from ro;
REVOKEappuser=> create table tbl4(id int);
CREATE TABLE
appuser=> create view v2 as select md5(id::text) from tbl4;
CREATE VIEW
appuser=> revoke select on tbl4 from ro;
REVOKE7.创建只读用户案例
sql
1. 创建用户
CREATE USER user_name WITH ENCRYPTED PASSWORD 'password';
2. 设置只读事务
alter user user_name set default_transaction_read_only=on;
3. 赋予指定数据库权限
GRANT CONNECT ON DATABASE db_name to user_name;
4. 进入指定数据库
--在工具中进入数据库或命令行下：
\c db_name
5. 把当前库现有的所有在public这个schema下的表的使用权限赋给用户
GRANT USAGE ON SCHEMA public to user_name;
6. 默认把当前库之后新建在public这个schema下的表的使用权限赋给用户
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user_name;
7. 赋予用户public下的序列的查看权
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO user_name;
8. 赋予用户public下的表的select权
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_name;1. 创建用户
CREATE USER user_name WITH ENCRYPTED PASSWORD 'password';
2. 设置只读事务
alter user user_name set default_transaction_read_only=on;
3. 赋予指定数据库权限
GRANT CONNECT ON DATABASE db_name to user_name;
4. 进入指定数据库
--在工具中进入数据库或命令行下：
\c db_name
5. 把当前库现有的所有在public这个schema下的表的使用权限赋给用户
GRANT USAGE ON SCHEMA public to user_name;
6. 默认把当前库之后新建在public这个schema下的表的使用权限赋给用户
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user_name;
7. 赋予用户public下的序列的查看权
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO user_name;
8. 赋予用户public下的表的select权
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_name;回收权限
sql
1. 回收schema的usage权限
revoke USAGE ON SCHEMA public from user_name;
2. 回收public下所有表的查询权限：
revoke SELECT ON ALL TABLES IN SCHEMA public from user_name;
3. 回收public下所有序列的查询权限
revoke SELECT ON ALL SEQUENCES IN SCHEMA public from user_name;
4. 回收默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public revoke SELECT ON TABLES from user_name;
5. 关闭数据库连接权限
revoke CONNECT ON DATABASE db_name from user_name;
6. 关闭默认只读事务设置
alter user user_name set default_transaction_read_only=off;
7. 查看权限是否为空了
\ddp
8. 通过管理员删除user_name用户：
drop user user_name;1. 回收schema的usage权限
revoke USAGE ON SCHEMA public from user_name;
2. 回收public下所有表的查询权限：
revoke SELECT ON ALL TABLES IN SCHEMA public from user_name;
3. 回收public下所有序列的查询权限
revoke SELECT ON ALL SEQUENCES IN SCHEMA public from user_name;
4. 回收默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public revoke SELECT ON TABLES from user_name;
5. 关闭数据库连接权限
revoke CONNECT ON DATABASE db_name from user_name;
6. 关闭默认只读事务设置
alter user user_name set default_transaction_read_only=off;
7. 查看权限是否为空了
\ddp
8. 通过管理员删除user_name用户：
drop user user_name;12.案例查看表
查询表命名空间、表名称、对象类型、表大小、表行数、表注释等：
sql
SELECT
n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description",
c.reltuples as "rows"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
ORDER BY 1,2 limit 10;
Schema | Name | Type | Owner | Size | Description | rows
--------+--------------------+-------+----------+------------+-------------+--------------
public | adg | table | postgres | 0 bytes | | 0
public | emp1 | table | postgres | 8192 bytes | | 0
public | t1 | table | postgres | 8192 bytes | | 0
public | tbl_expression | table | postgres | 528 kB | | 10000
public | tbl_index | table | postgres | 498 MB | | 1e+07
public | tbl_partial_index | table | postgres | 517 MB | | 9.000101e+06
public | tbl_partial_index1 | table | postgres | 517 MB | | 9.000101e+06
public | tbl_unique_index | table | postgres | 8192 bytes | | 0
(8 rows)SELECT
n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner",
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as "Size",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description",
c.reltuples as "rows"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','s','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
ORDER BY 1,2 limit 10;
Schema | Name | Type | Owner | Size | Description | rows
--------+--------------------+-------+----------+------------+-------------+--------------
public | adg | table | postgres | 0 bytes | | 0
public | emp1 | table | postgres | 8192 bytes | | 0
public | t1 | table | postgres | 8192 bytes | | 0
public | tbl_expression | table | postgres | 528 kB | | 10000
public | tbl_index | table | postgres | 498 MB | | 1e+07
public | tbl_partial_index | table | postgres | 517 MB | | 9.000101e+06
public | tbl_partial_index1 | table | postgres | 517 MB | | 9.000101e+06
public | tbl_unique_index | table | postgres | 8192 bytes | | 0
(8 rows)查询数据库大小
-- 查询单个数据库大小
select pg_size_pretty(pg_database_size('postgres')) as size;
-- 查询所有数据库大小
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;-- 查询单个数据库大小
select pg_size_pretty(pg_database_size('postgres')) as size;
-- 查询所有数据库大小
select datname, pg_size_pretty (pg_database_size(datname)) AS size from pg_database;查询表大小
-- 查询单个表大小
select pg_size_pretty(pg_relation_size('mytab')) as size;
-- 查询所有表大小
select relname, pg_size_pretty(pg_relation_size(relid)) as size from pg_stat_user_tables;
-- 查询单个表的总大小，包括该表的索引大小
select pg_size_pretty(pg_total_relation_size('tab')) as size;
-- 查询所有表的总大小，包括其索引大小
select relname, pg_size_pretty(pg_total_relation_size(relid)) as size from pg_stat_user_tables;-- 查询单个表大小
select pg_size_pretty(pg_relation_size('mytab')) as size;
-- 查询所有表大小
select relname, pg_size_pretty(pg_relation_size(relid)) as size from pg_stat_user_tables;
-- 查询单个表的总大小，包括该表的索引大小
select pg_size_pretty(pg_total_relation_size('tab')) as size;
-- 查询所有表的总大小，包括其索引大小
select relname, pg_size_pretty(pg_total_relation_size(relid)) as size from pg_stat_user_tables;查询索引大小
-- 查询单个索引大小
select pg_size_pretty(pg_relation_size('myindex')) as size;-- 查询单个索引大小
select pg_size_pretty(pg_relation_size('myindex')) as size;查询表空间大小
-- 查询单个表空间大小
select pg_size_pretty(pg_tablespace_size('pg_default')) as size;
-- 查询所有表空间大小
select spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size from pg_tablespace;
-- 或
select spcname, pg_size_pretty(pg_tablespace_size(oid)) as size from pg_tablespace;-- 查询单个表空间大小
select pg_size_pretty(pg_tablespace_size('pg_default')) as size;
-- 查询所有表空间大小
select spcname, pg_size_pretty(pg_tablespace_size(spcname)) as size from pg_tablespace;
-- 或
select spcname, pg_size_pretty(pg_tablespace_size(oid)) as size from pg_tablespace;13.只读账户
https://postgres.fun/20120817105800.html
14.读写
create user yidu_user with password 'aeb87d2548cd08' ;
create database AdvertiseDB owner yidu_user ;
grant all on database usercenter to yidu_user ;