02-PGBasicOperations
BasicOperations
构建用户
使用\help可以查看命令
❯ psql
psql (18.1)
输入 "help" 来获取帮助信息.
postgres=# \help
可用的说明：
ABORT CLOSE CREATE VIEW DROP USER MAPPING
ALTER AGGREGATE CLUSTER DEALLOCATE DROP VIEW
ALTER COLLATION COMMENT DECLARE END
ALTER CONVERSION COMMIT DELETE EXECUTE
ALTER DATABASE COMMIT PREPARED DISCARD EXPLAIN
ALTER DEFAULT PRIVILEGES COPY DO FETCH
ALTER DOMAIN CREATE ACCESS METHOD DROP ACCESS METHOD GRANT
ALTER EVENT TRIGGER CREATE AGGREGATE DROP AGGREGATE IMPORT FOREIGN SCHEMA
ALTER EXTENSION CREATE CAST DROP CAST INSERT
ALTER FOREIGN DATA WRAPPER CREATE COLLATION DROP COLLATION LISTEN
ALTER FOREIGN TABLE CREATE CONVERSION DROP CONVERSION LOAD
ALTER FUNCTION CREATE DATABASE DROP DATABASE LOCK
ALTER GROUP CREATE DOMAIN DROP DOMAIN MERGE
ALTER INDEX CREATE EVENT TRIGGER DROP EVENT TRIGGER MOVE
ALTER LANGUAGE CREATE EXTENSION DROP EXTENSION NOTIFY
ALTER LARGE OBJECT CREATE FOREIGN DATA WRAPPER DROP FOREIGN DATA WRAPPER PREPARE
ALTER MATERIALIZED VIEW CREATE FOREIGN TABLE DROP FOREIGN TABLE PREPARE TRANSACTION
ALTER OPERATOR CREATE FUNCTION DROP FUNCTION REASSIGN OWNED
ALTER OPERATOR CLASS CREATE GROUP DROP GROUP REFRESH MATERIALIZED VIEW
ALTER OPERATOR FAMILY CREATE INDEX DROP INDEX REINDEX
ALTER POLICY CREATE LANGUAGE DROP LANGUAGE RELEASE SAVEPOINT
ALTER PROCEDURE CREATE MATERIALIZED VIEW DROP MATERIALIZED VIEW RESET
ALTER PUBLICATION CREATE OPERATOR DROP OPERATOR REVOKE
ALTER ROLE CREATE OPERATOR CLASS DROP OPERATOR CLASS ROLLBACK
ALTER ROUTINE CREATE OPERATOR FAMILY DROP OPERATOR FAMILY ROLLBACK PREPARED
ALTER RULE CREATE POLICY DROP OWNED ROLLBACK TO SAVEPOINT
ALTER SCHEMA CREATE PROCEDURE DROP POLICY SAVEPOINT
ALTER SEQUENCE CREATE PUBLICATION DROP PROCEDURE SECURITY LABEL
ALTER SERVER CREATE ROLE DROP PUBLICATION SELECT
ALTER STATISTICS CREATE RULE DROP ROLE SELECT INTO
ALTER SUBSCRIPTION CREATE SCHEMA DROP ROUTINE SET
ALTER SYSTEM CREATE SEQUENCE DROP RULE SET CONSTRAINTS
ALTER TABLE CREATE SERVER DROP SCHEMA SET ROLE
ALTER TABLESPACE CREATE STATISTICS DROP SEQUENCE SET SESSION AUTHORIZATION
ALTER TEXT SEARCH CONFIGURATION CREATE SUBSCRIPTION DROP SERVER SET TRANSACTION
ALTER TEXT SEARCH DICTIONARY CREATE TABLE DROP STATISTICS SHOW
ALTER TEXT SEARCH PARSER CREATE TABLE AS DROP SUBSCRIPTION START TRANSACTION
当输入 create user之后，我们可以在此键入 \help create user继续查看帮助
postgres=# \help create user
命令： CREATE USER
描述： 定义一个新数据库角色
语法：
CREATE USER 名称 [ [ WITH ] 选项 [ ... ] ]
选项可以是
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT 连接限制
| [ ENCRYPTED ] PASSWORD '口令' | PASSWORD NULL
| VALID UNTIL '时间戳'
| IN ROLE 角色名称 [, ...]
| IN GROUP 角色名称 [, ...]
| ROLE 角色名称 [, ...]
| ADMIN 角色名称 [, ...]
| USER 角色名称 [, ...]
| SYSID uid
URL: https://www.postgresql.org/docs/18/sql-createuser.html
我们这里创建一个超级管理员
create user root with NOSUPERUSER PASSWORD 密码;
用root进行登陆：
psql -h 127.0.0.1 -U root -W 密码
# 我们会发现提示没有root数据库，也就是是只有用户不让进入，还要你创建对应的库，当然也可以选择已有的库
先创建一个root库
psql> create database root;
再进行尝试使用库，我们发现已经可以使用root库了。不过目前还是postgres用户。
\c root
切换用户：
\c root root
对于不清楚的有关服务级别的命令（与postgre/sql命令不同），可以键入 \?查询：
基本查看操作
\du查看用户
\dt查看表
权限操作
PGSQL的逻辑结构
一个数据库都多个schema,每个schema有自己相应的库表信息，权限粒度更加精细。
PGSQL权限分为多层：
server、cluster、tablesoace级别，这个级别一般基于pg_hba.conf
database级别，通过命令行操作，grant
namespaces、schema级别
对象级别，通过grant命令
权限控制我们可以基于grant命令操作:
\h grant
root=# \h grant
命令： GRANT
描述： 定义存取权限
语法：
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | MAINTAIN }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] 表名 [, ...]
| ALL TABLES IN SCHEMA 模式名称 [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( 列名称 [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( 列名称 [, ...] ) }
ON [ TABLE ] 表名 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE 序列名称 [, ...]
| ALL SEQUENCES IN SCHEMA 模式名称 [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE 数据库名称 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON DOMAIN 域_名称 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN DATA WRAPPER 外部数据封装器的名称 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON FOREIGN SERVER 服务器名称 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
ON { { FUNCTION | PROCEDURE | ROUTINE } 程序名称 [ ( [ [ 参数模式 ] [ 参数名称 ] 参数类型 [, ...] ] ) ] [, ...]
| ALL { FUNCTIONS | PROCEDURES | ROUTINES } IN SCHEMA 模式名称 [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON LANGUAGE 语言名称 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
ON LARGE OBJECT loid [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { SET | ALTER SYSTEM } [, ... ] | ALL [ PRIVILEGES ] }
ON PARAMETER 配置参数 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA 模式名称 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { CREATE | ALL [ PRIVILEGES ] }
ON TABLESPACE 表空间的名称 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
ON TYPE 类型名称 [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY role_specification ]
GRANT 角色名称 [, ...] TO role_specification [, ...]
[ WITH { ADMIN | INHERIT | SET } { OPTION | TRUE | FALSE } ]
[ GRANTED BY role_specification ]
这里role_specification可以是：
[ GROUP ] 角色名称
| PUBLIC
| CURRENT_ROLE
| CURRENT_USER
| SESSION_USER
URL: https://www.postgresql.org/docs/18/sql-grant.html
案例
创建用户
create user root nosuperuser;
alter user root with password 密码;
create database root;
创建表
# 由于作为schema的所有者和表的所有者可以执行所有权限，因此我们使用其他用户创建，赋予权限进行测试
-- 登陆使用 postgres用户
psql
-- 连接root数据库
\c root
-- 创建schema
create schema not_me;
-- 赋予root使用权限
grant usage on schema not_me to root;
-- 创建表
create table not_me.user (id int);
-- 赋予权限
grant select,update,insert on table not_me.user to root;
登陆root
psql -h 127.0.0.1 -p 5432 -U root -W
-- 插入
insert into not_me.user values(20);
# 插入成功
-- 更新
update not_me.user set id = 10 where id = 20;
-- 查询
select * from not_me.user;
-- 删除
delete from not_me.user where id = 20;
# 这时候提示权限问题，因为我们没给删除权限。
浙公网安备 33010602011771号