BasicOperations
构建用户
使用\help可以查看命令
❯ psql
psql (18.1)
输入 "help" 来获取帮助信息.postgres=# \help
可用的说明：ABORT CLOSE CREATE VIEW DROP USER MAPPINGALTER AGGREGATE CLUSTER DEALLOCATE DROP VIEWALTER COLLATION COMMENT DECLARE ENDALTER CONVERSION COMMIT DELETE EXECUTEALTER DATABASE COMMIT PREPARED DISCARD EXPLAINALTER DEFAULT PRIVILEGES COPY DO FETCHALTER DOMAIN CREATE ACCESS METHOD DROP ACCESS METHOD GRANTALTER EVENT TRIGGER CREATE AGGREGATE DROP AGGREGATE IMPORT FOREIGN SCHEMAALTER EXTENSION CREATE CAST DROP CAST INSERTALTER FOREIGN DATA WRAPPER CREATE COLLATION DROP COLLATION LISTENALTER FOREIGN TABLE CREATE CONVERSION DROP CONVERSION LOADALTER FUNCTION CREATE DATABASE DROP DATABASE LOCKALTER GROUP CREATE DOMAIN DROP DOMAIN MERGEALTER INDEX CREATE EVENT TRIGGER DROP EVENT TRIGGER MOVEALTER LANGUAGE CREATE EXTENSION DROP EXTENSION NOTIFYALTER LARGE OBJECT CREATE FOREIGN DATA WRAPPER DROP FOREIGN DATA WRAPPER PREPAREALTER MATERIALIZED VIEW CREATE FOREIGN TABLE DROP FOREIGN TABLE PREPARE TRANSACTIONALTER OPERATOR CREATE FUNCTION DROP FUNCTION REASSIGN OWNEDALTER OPERATOR CLASS CREATE GROUP DROP GROUP REFRESH MATERIALIZED VIEWALTER OPERATOR FAMILY CREATE INDEX DROP INDEX REINDEXALTER POLICY CREATE LANGUAGE DROP LANGUAGE RELEASE SAVEPOINTALTER PROCEDURE CREATE MATERIALIZED VIEW DROP MATERIALIZED VIEW RESETALTER PUBLICATION CREATE OPERATOR DROP OPERATOR REVOKEALTER ROLE CREATE OPERATOR CLASS DROP OPERATOR CLASS ROLLBACKALTER ROUTINE CREATE OPERATOR FAMILY DROP OPERATOR FAMILY ROLLBACK PREPAREDALTER RULE CREATE POLICY DROP OWNED ROLLBACK TO SAVEPOINTALTER SCHEMA CREATE PROCEDURE DROP POLICY SAVEPOINTALTER SEQUENCE CREATE PUBLICATION DROP PROCEDURE SECURITY LABELALTER SERVER CREATE ROLE DROP PUBLICATION SELECTALTER STATISTICS CREATE RULE DROP ROLE SELECT INTOALTER SUBSCRIPTION CREATE SCHEMA DROP ROUTINE SETALTER SYSTEM CREATE SEQUENCE DROP RULE SET CONSTRAINTSALTER TABLE CREATE SERVER DROP SCHEMA SET ROLEALTER TABLESPACE CREATE STATISTICS DROP SEQUENCE SET SESSION AUTHORIZATIONALTER TEXT SEARCH CONFIGURATION CREATE SUBSCRIPTION DROP SERVER SET TRANSACTIONALTER TEXT SEARCH DICTIONARY CREATE TABLE DROP STATISTICS SHOWALTER 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 uidURL: 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_USERURL: 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;
# 这时候提示权限问题，因为我们没给删除权限。