【PostgreSQL】系列之 一 用户创建和授权（三）

最新推荐文章于 2024-08-03 11:23:51 发布
最新推荐文章于 2024-08-03 11:23:51 发布
阅读量1.3w 收藏 34
点赞数 13
分类专栏： 数据库 文章标签： postgresql 数据库 schema grant 授权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_35995514/article/details/132069584
版权

🍁 博主 "开着拖拉机回家"带您 Go to New World.✨🍁

🦄 个人主页——🎐开着拖拉机回家_Linux,Java基础学习,大数据运维-CSDN博客 🎐✨🍁

🪁🍁 希望本文能够给您带来一定的帮助🌸文章粗浅，敬请批评指正！🍁🐥

目录

🍁 博主 "开着拖拉机回家"带您 Go to New World.✨🍁

一、PostgreSQL权限概述

1.1 初始角色

1.2 权限 - PRIVILEGES

1.3 权限的组成

1.4 特殊的权限

1、owner拥有特权

2、PUBLIC

1.5 授权 - GRANT

二、Schema创建和授权实操

三、重新为用户授权

四、查询schema是否存在

五、客户端鉴权

六、postgresql 执行sql文件

一、PostgreSQL权限概述

1.1 初始角色

数据库刚创建时，会自动创建一个超级用户的角色：postgres。任何操作都是从该用户开始的。

PostgreSQL权限管理详解_pgsql权限管理_zou8944的博客-CSDN博客(摘录博客)

1.2 权限 - PRIVILEGES

角色有了，决定角色能够执行哪些数据库操作，则需要权限进行管理。PG将权限分为了两部分。这里所说的权限，主要指第二部分

  • 能否登录、创建database、创建role，这类重要权限，在创建role时指定；在pg_roles表中可查看
  • 对数据库对象的各类操作的权限，通过GRANT手动授予；在information_schema.xxx_privileges表中可查看，比如information_schema.table_privileges

1.3 权限的组成

一个完整的权限描述：角色A对表table1具有SELECT权限，有三部分组成

  • 角色：A
  • 授权目标：table1
  • 权限：SELECT

1.4 特殊的权限

1、owner拥有特权

当一个数据库对象被创建后，它会被自动分配一个owner，一般来说是执行创建语句的那个角色。大多数情况下，owner及其成员能够对该对象做任何事，如果其他人想要操作它，则需要GRANT授权。

可修改owner，超级用户、对象原本的owner、owner的成员都能够调整对象的owner

alter table table_name owner to new_owner;

2、PUBLIC

PUBLIC并不是一个真正的角色，确切地说，它应该算一个关键字，当授权的目标是它时，表示：授予系统中的所有角色，包括今后定义的角色。

PUBLIC默认是拥有以下权限

  • 本地登录
  • 对public的USAGE权限
  • 对public的CREATE权限

字面意思理解，相当于将该权限公开。

1.5 授权 - GRANT

-- 授予权限
GRANT {权限} ON {授权目标} TO {被授权角色} [WITH GRANT OPTION]
-- 授予角色
GRATE {角色} TO {被授权角色} [WITH ADMIN OPTION] [GRANTED BY 角色]

ALL PRELEGES：它是针对一个授权目标的所有权限的总和。

WITH ADMIN OPTION：授权传递，被授权的角色，可以传递授权

#  授予用户 zhangsan 数据库 kangkang 的所有权限
grant all privileges on database kangkang to zhangsan;
#  授权当前database 的指定kangll_schema的所有表的只读权限给zhangsan 角色
grant select on all tables in schema kangll_schema to zhangsan;

如下查询结果表示：kangll_test 数据中 public 下 stu 表，kangll 用户拥有所有权限， zhang 用户拥有只读权限，都是 kangll 用户授予的。

我们换个数据库查询下，可以看到 Access privileges为空，表明：其owner对该表有完整的权限

二、Schema创建和授权实操

创建数据库 kangkang， 我使用 postgres用户 完成

CREATE DATABASE kangkang;

创建名为 zhangsan 的用户，请运行以下命令：

CREATE USER zhangsan WITH PASSWORD '123456';

-- 修改密码
alter user zhangsan with password 'password';

授予zhangsan用户 kangkang 数据库的所有权限

grant all privileges on database kangkang to zhangsan;

现在 使用zhangsan 用户登录数据库， 创建名为 kangll_schema 的 schema

CREATE SCHEMA kangll_schema;

## 删除可以执行如下命令
DROP SCHEMA kangll_schema;

执行结果：

授予名为 zhangsan 用户对名为 kangll_schema 的 schema 下表的所有操作权限

GRANT USAGE ON SCHEMA kangll_schema to zhangsan;
grant all privileges on all tables in schema kangll_schema to zhangsan;
grant all privileges on all sequences in schema kangll_schema to zhangsan;
grant select,insert,update,delete on all tables in schema kangll_schema to zhangsan;

授权完成

kangkang数据库中创建 stu 表

CREATE TABLE kangll_schema.stu(
  stu_id BIGINT NOT NULL,
  stu_name VARCHAR(255) NOT NULL);

# 插入数据
INSERT INTO kangll_schema.stu VALUES(1, 'kangll');

# 查询
SELECT * FROM kangll_schema.stu;

执行过程：

可以看到 zhangsan 用户对 stu 表拥有所有权限， 也可以看到 Access privileges为空

撤销 权限后 重新分配 只读权限， zhangsan 用户拥有只读权限，且权限是 zhangsan 用户授予的 ，可以看到 Access privileges 不为空 ，为zhangsan 用户的 “r” 权限。

三、重新为用户授权

-- 取消权限
REVOKE [GRANT OPTION FOR] {权限} ON {授权目标} FROM {被授权人} [CASCADE | RESTRICT]
-- 取消角色
REVOKE [ADMIN OPTION FOR] {角色} FROM {被授权角色} [CASCADE | RESTRICT]

示例：

-- 切换到doki_database下
\c kangkang
-- 收回用户在 kangll_schema 下所有表的所有权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA kangll_schema FROM zhangsan;

-- 为zhangsan 赋予kangkang 数据库下的查询权限
GRANT select ON all TABLES IN SCHEMA kangll_schema to zhangsan;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA kangll_schema TO zhangsan;

执行过程：

四、查询schema是否存在

查询 PostgreSQL 数据库中是否存在某个特定的 schema，可以使用以下 SQL 命令：

SELECT * FROM information_schema.schemata WHERE schema_name = 'kangll_schema';

查询可以看到 schema_name 和 对应schema的 所属者，如果查询结果为空则对应的schema 不存在。

五、客户端鉴权

角色创建后好，还需要确认pg_hba.conf 的配置， 如果没有配置并不能直接通过网络连接到PG服务端。一般我们都是在安装完数据库就需要配置。

vim  /var/lib/pgsql/15/data/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256
host    all             all             0.0.0.0/0               md5

配置文件说明：

TYPE

  • local：本地连接
  • host：使用TCP/IP连接

DATABASE： 许连接的database

USER: 允许连接的角色

ADDRESS: 允许的客户端地址，0.0.0.0/0表示允许所以客户端连接

METHOD: 鉴权方式

  • trust：无条件允许连接
  • reject：无条件拒绝
  • password：要求客户端提供未加密的密码进行身份验证，密码在网络上以明文形式传输，如果使用SSL，可以加密传输
  • scram-sha-256：执行 SCRAM-SHA-256 身份验证，这是一种质询响应机制，这是目前最安全的方式，不过有的数据库客户端可能不支持。
  • md5：也是一种质询响应机制
  • peer：获取客户端操作系统的用户名，如果和请求连接的用户名一样。这只有在本地连接时才有用。
  • xxx：都是其它的鉴权方式，官网参考： PostgreSQL: Documentation: 15: 21.1. The pg_hba.conf File

六、postgresql 执行sql文件

# 切换到postgres用户
sudo -i -u postgres
psql -d testdb -U postgres -f  /opt/PostgreSQL/Ambari-DDL-Postgres-CREATE.sql

或者
sudo -u postgres psql -d kangkang  -U  postgres    -f  /opt/PostgreSQL/Ambari-DDL-Postgres-CREATE.sql

参考原文链接：PostgreSQL权限管理详解_pgsql权限管理_zou8944的博客-CSDN博客

阿龙先生啊
关注
专栏目录
PostgreSQL数据库从入门到精通系列之十七：创建账号、赋予账号数据库权限、赋予账号表权限、撤销账号权限、创建角色、添加用户到角色、赋予角色表的owner
zhengzaifeidelushang的博客
08-03 115
注意：如果您只想撤销 SELECT 权限，可以使用 REVOKE SELECT ON ALL SEQUENCES IN SCHEMA public FROM debeziumcdc;这个命令将会从 public 架构下的所有序列中撤销 debeziumcdc 账号的 USAGE 和 SELECT 权限。请确保您具有足够的权限来执行 REVOKE 命令。要撤销在 PostgreSQL 14 中使用的 GRANT 命令授予的账号权限，可以使用 REVOKE 命令。
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL
热门推荐
dvlinker的技术专栏
10-08 4万+
一文带你了解三大开源关系型数据库：SQLite、MySQL和PostgreSQL。
pg数据库创建用户给用户所有权限
weixin_47036600的博客
04-07 1090
2、给用户这个实例下所有权限。
PostgreSQL sql语句：创建用户，创建数据库，用户授权
09-15
PostgreSQL sql语句：创建用户，创建数据库，用户授权
postgresql 创建账号，并授予查询权限
TOOZOOY的博客
07-28 629
【代码】postgresql 创建账号，并授予查询权限。
Postgresql创建用户与数据库并赋予权限
春风化雨
05-18 1万+
3、给iuser用户，创建数据库叫work_base。4.3、将work_base的所有权限赋予iuser。4、授予iuser当前work_base的全部权限。2、添加名为iuser的用户，并设置密码。1、使用postgres登录pgsql。4.1、 先退出postgre数据库。4.2、登录work_base数据库。
pgsql创建账户并授权
liu289747235的专栏
05-28 1577
用户对所有数据库和表的所有权限。请确保仅向需要这些权限的用户授予此类权限。pgsql权限grant 所有权限给 venuspostgres。完成pg_hba.conf配置。
PostgreSQL创建用户与授权操作
做GIS梦的人
05-18 1万+
创建只读用户 创建用户 -- 创建名为readonly，密码为123456的用户 CREATE USER readonly WITH ENCRYPTED PASSWORD '123456'; 设置回话与事务模式只读 -- 后续登录的会话或者当前事务设置为只读模式 ALTER USER readonly SET default_transaction_read_only = ON; 允许用户用于登录 ALTER USER readonly WITH login; 将Schema授权给用户
PostgreSQL 创建用户并赋予权限
qq_45143653的博客
06-29 1万+
1、创建一个test用户 2、创建COMPANY表 3、设置权限 3.1、postgresql权限说明 3.2、设置权限语法 3.3、设置用户scheam的使用权限 3.4、对表授权 3.4.1、设置用户对表的所有权限 3.5撤销权限 3.5.1、撤销用户对COMPANY表的所有权限 3.5.2、撤销用户scheam的使用权限 4、注意 ———————————————— 参考：PostgreSQ......
PostgreSQL 用户及授权管理 04：授予及回收权限
cc20100608的专栏
05-07 2574
在本小节中，我们重点介绍了 GRANT 及 REVOKE 的使用及一些具体实例。在下面的一小节中我们再介绍 RLS 授权及如何验证。
postgreSQL启动服务至创建用户(篇一)
5G工业互联网
10-27 1482
首先：pg_ctl start 或 net start PostgreSQL 然后：psql -U postgres：开启服务 然后：一直回车直到显示postgres-# 然后\l 命令查看当前表空间下的情况：如下 postgres=# \l 数据库列表 名称 | 拥有者 | 字元编码 ...
Debezium系列之：PostgreSQL数据库赋予账号数据采集权限的详细步骤
最新发布
zhengzaifeidelushang的博客
08-03 87
Debezium系列之：PostgreSQL数据库赋予账号数据采集权限的详细步骤
postgresql用户与权限管理
weixin_30895723的博客
08-20 5827
pg使用角色的概念管理数据库访问权限，角色是一系列相关权限的集合。为了管理方便，通常把一系列先关的权限赋予给一个角色，如果哪个用户需要这些权限，就把这些角色赋予给响应的用户。 由于用户也拥有一系列的相关权限，为了简化管理，在PG中，角色与用户是没有区别的，一个用户也是一个角色，我们可以把一个用户的权限赋值给另一个用户。 用户和角色在整个数据库实例中是全局的，在同一个实例的不同数据库中，看到的用户...
PostgreSql数据库创建用户并授权
水滴的专栏
08-31 1万+
postgreSql创建用户并授权
PostgreSQL 创建用户
Ms.Fox的专栏
09-17 276
create user test01_user with password 'Test01@123'; create database test01 owner test01_user; grant all privileges on database test01 to test01_user;
PostgreSQL用户和角色的创建和管理
互联网知识分享
10-01 4050
中，用户和角色是用于对数据库进行权限管理的重要概念。用户是数据库系统中的实体，可以登录数据库并执行操作，而角色是一组权限的集合，可以被授予给多个用户。CREATEROLE/NO CREATEROLE：指定用户是否有创建角色的权限。CREATEDB/NO CREATEDB：指定用户是否有创建数据库的权限。INHERIT/NOINHERIT：指定用户是否能够继承其所属角色的权限。语句授予和撤销权限，可以灵活地管理数据库的访问和权限。语句授予用户和角色权限，并撤销已授予的权限。语句修改用户和角色的属性，使用。
postgresql 创建用户、授权、基本操作总结
lzl_anonym的博客
12-30 1733
-- 超级用户 postgres -- 创建用户 CREATE USER lzlWITH PASSWORD '123456'; -- 将数据库 public 权限授权于 lzl GRANT ALL PRIVILEGES ON DATABASE db_1 TO lzl; -- 【单独授权一张表】 授予用户查看授权数据库里面的某张表的权限(此命令需要进入被授权的数据库操作) GRANT ALL PRIVILEGES ON TABLE test1 TO lzl; -- 【授权一个数据库的所..
postgresql创建用户并授权
keyboard专栏
06-28 6287
1、使用最大的权限登录 postgres 一般是最大的管理员权限 2、查看已有的用户 如果在padmin中就执行 select user 如果是 liunx命令行，就执行 psql -U ‘sa’ 2、创建新的用户 CREATE USER sa WITH PASSWORD ‘123456’; 3、授权用户数据库权限 GRANT ALL PRIVILEGES ON DATABASE billing TO sa; 4、授权用户数据库表的权限 GRANT ALL PRIVILEGES ON ALL TABLES
PostgreSql数据库-创建用户、数据库、schema
tttzzzqqq2018的博客
08-31 1109
【代码】PostgreSql数据库-创建用户、数据库、schema。
postgresql创建新用户
09-10
要在 PostgreSQL 中创建新用户，你可以按照以下步骤进行操作： 1. 以超级用户身份登录到 PostgreSQL 数据库。你可以在终端中输入以下命令： ``` sudo -u postgres psql ``` 2. 创建新用户。你可以使用以下命令创建一个名为 "newuser" 的新用户： ``` CREATE USER newuser WITH PASSWORD 'password'; ``` 将 "newuser" 替换为你想要的用户名，将 'password' 替换为你想要设置的密码。 3. 授予用户访问数据库的权限。你可以使用以下命令授予用户访问特定数据库的权限： ``` GRANT ALL PRIVILEGES ON database_name TO newuser; ``` 将 "database_name" 替换为你想要授予访问权限的数据库名称。 4. 完成后，可以使用以下命令退出 PostgreSQL： ``` \q ``` 这样，你就成功创建了一个新的 PostgreSQL 用户并为其分配了数据库访问权限。请注意，这只是基本的示例，请根据你自己的需求进行适当的调整。
评论 10
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

阿龙先生啊

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值