PostgreSQL实践

发表于 2020-12-18 更新于 2022-06-10 阅读次数：本文字数： 7.6k 阅读时长 ≈ 7 分钟

安装

yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
yum install -y postgresql13-server
/usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13

初始化配置

#修改监听地址
vim /var/lib/pgsql/13/data/postgresql.conf
listen_addresses = '*' 

#修改PostgreSQL数据库默认用户postgres的密码
sudo -u postgres psql
ALTER USER postgres WITH PASSWORD 'postgres';

#修改pg_hba.conf文件，放行所有用户
host    all             all             0.0.0.0/0                  md5        
#其中0.0.0.0/0表示运行任意ip地址访问。

使用

控制台命令

\q: 退出
\h：查看SQL命令的解释，比如\h select。
\?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

创建用户/角色

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                    :被忽略，但是为向后兼容性而存在。

示例：

创建不需要密码登陆的用户qh

1 2	postgres=# CREATE ROLE qh LOGIN; CREATE ROLE

创建需要密码登陆的用户qh1

1 2	postgres=# CREATE USER qh1 WITH PASSWORD 'qh1'; CREATE ROLE

注意：CREATE USER和CREATE ROLE的区别在于，CREATE USER指令创建的用户默认是有登录权限的，而CREATE ROLE没有。

创建有时间限制的用户qh2

1 2	postgres=# CREATE ROLE qh2 WITH LOGIN PASSWORD 'qh2' VALID UNTIL '2020-05-30'; CREATE ROLE

创建有创建数据库和管理角色权限的用户admin

1 2	postgres=# CREATE ROLE admin WITH CREATEDB CREATEROLE; CREATE ROLE

注意：拥有创建数据库，角色的用户，也可以删除和修改这些对象。

创建具有超级权限的用户：admin

1 2	postgres=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin'; CREATE ROLE

创建复制账号：repl

1 2	postgres=# CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl'; CREATE ROLE

其他

#创建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;

授权，定义访问权限

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;

查看权限(需在当前数据库内查询)

https://www.wenjiangs.com/doc/0k5gdvlu

1、查看某用户的表权限

1	select * from information_schema.table_privileges where grantee=``'user_name'``;

2、查看usage权限表

1	select * from information_schema.usage_privileges where grantee=``'user_name'``;

3、查看存储过程函数相关权限表

1	select * from information_schema.routine_privileges where grantee=``'user_name'``;

table_privileges：

名字	数据类型	描述
`grantor`	`sql_identifier`	发出该权限授权的用户名
`grantee`	`sql_identifier`	得到该权限的用户或者组的名字
`table_catalog`	`sql_identifier`	包含该表的数据库的名字（总是当前数据库）
`table_schema`	`sql_identifier`	包含该表的模式名字
`table_name`	`sql_identifier`	表的名字
`privilege_type`	`character_data`	权限类型： `SELECT`， `DELETE`， `INSERT`， `UPDATE`， `REFERENCES`， `RULE`，或者 `TRIGGER`
`is_grantable`	`character_data`	如果权限可以赋与别人，则为 `YES`，否则为 `NO`
`with_hierarchy`	`character_data`	应用于一个PostgreSQL 里没有的特性

请注意 grantee 并不区分用户和组。如果你有同名的用户和组，那么，很不行我们没办法区分它们。将来的 PostgreSQL 版本可能会禁止用户和组同名。

usage_privileges：

名字	数据类型	描述
`grantor`	`sql_identifier`	目前设置为该对象的所有者的名字
`grantee`	`sql_identifier`	目前总是 `PUBLIC`
`object_catalog`	`sql_identifier`	包含该对象的数据库的名字（总是当前数据库）
`object_schema`	`sql_identifier`	包含该对象的模式的名字
`object_name`	`sql_identifier`	对象的名字
`object_type`	`character_data`	目前总是 `DOMAIN`
`privilege_type`	`character_data`	总是 `USAGE`
`is_grantable`	`character_data`	目前总是 `NO`