PostgreSQL中public schema的权限和安全

Revoke Create on public Schema from user - is not working in Postgresql

Ask Question

Asked 5 years, 11 months ago

Modified 5 years, 11 months ago

Viewed 9k times

https://6bd9ca2770bba42c354e886179ea6c97.safeframe.googlesyndication.com/safeframe/1-0-40/html/container.html

Report this ad

5

I am using postgresql -8.4.20 which is a back-end for JON server and login with username rhqadmin and connected to database rhq.

After connecting It is redirecting me to public schema by default. Now I don't want to allow this user rhqadmin to create tables in public Schema.

So I am revoking the Create privilege on public schema by using following command:-

Revoke Create ON SCHEMA public FROM rhqadmin;

Then it is giving me warning -

"no privileges could be revoked for "public".

When I login with super user postgres and from there I revoke 'CREATE' privilege on public Schema for user rhqadmin then it is not giving any warning and successfully revoked then I logged in with user rhqadmin and try to create table in public schema and again it is allowing me to Create table even after revoking the create privilege from super user.

Please tell me why revoke privilege is not working for public Schema or I am doing any mistake.

Regards,

Avinash

Your problem is that the CREATE privilege is not granted to rhqadmin, but to the special role PUBLIC to which everybody belongs by default.

Since privileges in PostgreSQL are additive, there is no way to explicitly forbid something to only a single user if it is allowed to everybody.

The solution is probably:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

No, so far I have tried this above solution but it is also not working for public schema . I mean it should not allow me to create tables in public schema. but still I can create tables.

Run \dn+ public in psql and add the result to the question, then I can figure out what is going on. Has your user any roles (groups) granted?

– Laurenz Albe

Mar 23, 2017 at 11:44

• 2

Thanks Laurenz(Sorry for the late reply) but Its working now. See I was doing a mistake that I am executing this Revoke command inside the postgres db with postgres user. so if i want to revoke for rhqadmin user which is connecting to rhq db,then I have to connect as postgres user with rhq db and now gave the command and its working. :-)

– Avi

Mar 28, 2017 at 6:45

用户需要新建的database中public不给别的用户访问及写入，那么需要切换到所在的database执行REVOKE CREATE ON SCHEMA public FROM PUBLIC;同时使用\dn+ 查看public上的权限。 owner限制不了往public写数据，不过可以删除public或者设置search_path。

同时可以刷新template1模板，以后新建的库都可以避免别的用户写入新库的public中。

这里的第三列list of schems access privileges的格式是：

postgres=# create database ab with template template1;

CREATE DATABASE

postgres=# \c ab

You are now connected to database "ab" as user "postgres".

ab=# \dn+

List of schemas

Name | Owner | Access privileges | Description

--------+----------+-------------------+------------------------

public | postgres | =UC/postgres | standard public schema

(1 row)

ab=# create table public.ab(a int);

CREATE TABLE

ab=# REVOKE ALL PRIVILEGES ON SCHEMA public FROM PUBLIC;

REVOKE

ab=# \dn+

List of schemas

Name | Owner | Access privileges | Description

--------+----------+-------------------+------------------------

public | postgres | | standard public schema

(1 row)

ab=# create table public.abc(a int);

CREATE TABLE

ab=# REVOKE ALL PRIVILEGES ON SCHEMA public FROM postgres;

REVOKE

ab=# \dn+

List of schemas

Name | Owner | Access privileges | Description

--------+----------+-------------------+------------------------

public | postgres | | standard public schema

(1 row)

ab=# create table public.abcd(a int);

CREATE TABLE

ab=# REVOKE ALL PRIVILEGES ON SCHEMA public FROM postgres;

REVOKE

ab=# REVOKE ALL PRIVILEGES ON SCHEMA public FROM public;

REVOKE

ab=# create table public.abcde(a int);

CREATE TABLE

--postgres用户super user，怎么控制都不行

[postgres@pg-192-134 ~]$ psql --username=xxljob

psql (14.7)

Type "help" for help.

xxljob=> \c ab

You are now connected to database "ab" as user "xxljob".

ab=> create table public.abcdef(a int);

ERROR: permission denied for schema public

LINE 1: create table public.abcdef(a int);

^

ab=> \dn+

List of schemas

Name | Owner | Access privileges | Description

--------+----------+-------------------+------------------------

public | postgres | | standard public schema

(1 row)

ab=> show search_path

ab-> ;

search_path

-----------------

"$user", public

(1 row)

ab=>

刷新template1的模板

abcd=# \c template1

You are now connected to database "template1" as user "postgres".

template1=# \dn+

List of schemas

Name | Owner | Access privileges | Description

--------+----------+-------------------+------------------------

public | postgres | | standard public schema

(1 row)

template1=# grant all on schema public to public;

GRANT

template1=# \dn+

List of schemas

Name | Owner | Access privileges | Description

--------+----------+-------------------+------------------------

public | postgres | =UC/postgres | standard public schema

(1 row)

template1=# create database abcdef with template template1;

CREATE DATABASE

template1=# \c abcdef

You are now connected to database "abcdef" as user "postgres".

abcdef=# \dn +

ERROR: invalid regular expression: quantifier operand invalid

abcdef=# \dn+

List of schemas

Name | Owner | Access privileges | Description

--------+----------+-------------------+------------------------

public | postgres | =UC/postgres | standard public schema

(1 row)

abcdef=#

--dn+ List of schemas 权限解读

在初始数据库创建时，新创建的数据库包含一个名为“public”的预定义schema。

查看schema和schema上的特权：

　　

这里的第三列list of schems access privileges的格式是：

即：

可能有多个这样的权限信息规范，由加号分隔列出，因为权限是另外加的。

对于schema，有两个单独被授予的权限：

·U表示usage，用户查看数据库中在该schema下的对象，比如表、视图时，需要usage权限；

·C表示create，表示用户可以在该schema下创建数据库对象。对于不同的数据库对象，有不同的权限。但是在schema级别，只有usage(U)和create(C)权限。

上面查询结果中，第一行表示postgres被postgres授予了UC权限；第二行，等号左边是空的，表示所有用户都被授予了UC权限。

如果用户场景不需要public schema，可以直接删除该schema；但是，有些扩展是默认使用public schema，可以考虑收回默认的权限分配。

取消在public schema上的create权限：

　　

取消在public schema上的usage权限：

　　

同时取消create、usage权限：

　　

甚至可以取消schema的owner的权限：

　　

public schema的权限是随着创建数据库初始化就完成的，之后创建schema就需要显式授权了：

　　

因为默认的public对任何用户都是授予了usage、create权限，从安全角度要想一劳永逸地解决权限问题，可以修改模板数据库template1，因为所有数据库都是从模板数据库template1创建而来：

　　

查看所有的schema：

权限

一、创建用户/角色

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

示例：

postgres=# CREATE ROLE test2 WITH LOGIN PASSWORD 'test2' VALID UNTIL '2020-06-30';

CREATE ROLE

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

postgres=# CREATE ROLE admin WITH CREATEDB CREATEROLE;

CREATE ROLE

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

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

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

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 '';----和上面的create role重复的名字是不可以的

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 ]

##单表授权：授权test账号可以访问schema为test的t1表

grant select,insert,update,delete on test.t1 to test;

##所有表授权：

grant select,insert,update,delete on all tables in schema test to test;

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )

[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }

ON [ TABLE ] table_name [, ...]

TO role_specification [, ...] [ WITH GRANT OPTION ]

##列授权，授权指定列(test schema下的t1表的name列)的更新权限给test用户

grant update (name) on test.t1 to test;

##指定列授不同权限，test schema下的t1表，查看更新name、id字段，插入name字段

grant select (name,id),update (name,id),insert(name) on test.t1 to test;

GRANT { { USAGE | SELECT | UPDATE }

[, ...] | ALL [ PRIVILEGES ] }

ON { SEQUENCE sequence_name [, ...]

| ALL SEQUENCES IN SCHEMA schema_name [, ...] }

TO role_specification [, ...] [ WITH GRANT OPTION ]

##序列（自增键）属性授权，指定test schema下的seq_id_seq 给test用户

grant select,update on sequence test.seq_id_seq to test;

##序列（自增键）属性授权，给用户test授权test schema下的所有序列

grant select,update on all sequences in schema test to test;

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }

ON DATABASE database_name [, ...]

TO role_specification [, ...] [ WITH GRANT OPTION ]

##连接数据库权限，授权test用户连接数据库testdb

grant connect on database test to testdb;

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权限，授权demo访问test schema权限

grant usage on schema test to demo;

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 ]

##把test用户的权限授予用户demo。

grant test to demo;

权限说明：

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：一次授予所有可用权限。

用户授权官方英文文档地址 https://www.postgresql.org/docs/12/sql-grant.html

用户授权官方中文文档地址 http://www.postgres.cn/docs/11/sql-grant.html

撤销权限

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 ]

##移除用户test在schema test上所有表的select权限

revoke select on all tables in schema test from test;

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 ]

##移除用户test在test schema的t1表的id列的查询权限

revoke select (id) on test.t1 from test;

注意：任何用户对public的schema都有all的权限，为了安全可以禁止用户对public schema 的create权限。

##移除所有用户（public），superuser除外，对指定DB下的public schema的create 权限。---

testdb=# revoke create on schema public from public;

撤销权限官方中文文档 http://www.postgres.cn/docs/11/sql-revoke.html

撤销权限官方引文文档 https://www.postgresql.org/docs/12/sql-revoke.html

修改用户属性

ALTER USER role_specification [ WITH ] option [ ... ]

where option can be:

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| INHERIT | NOINHERIT

| LOGIN | NOLOGIN

| REPLICATION | NOREPLICATION

| BYPASSRLS | NOBYPASSRLS

| CONNECTION LIMIT connlimit

| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL

| VALID UNTIL 'timestamp'

ALTER USER name RENAME TO new_name

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter

ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL

where role_specification can be:

role_name

| CURRENT_USER

| SESSION_USER

示例：

注意：option选项里的用户都可以通过alter role进行修改

修改用户为超级/非超级用户

alter role caocao with superuser/nosuperuser;

修改用户为可/不可登陆用户

alter role caocao with nologin/login;

修改用户名：

alter role caocao rename to youxing;

修改用户密码，移除密码用NULL

alter role youxing with password 'youxing';

修改用户参数，该用户登陆后的以该参数为准

alter role demo in database demo SET geqo to 0/default;

三. 控制访问文件 pg_hba.conf

local database user auth-method [auth-options]

host database user address auth-method [auth-options]

hostssl database user address auth-method [auth-options]

hostnossl database user address auth-method [auth-options]

host database user IP-address IP-mask auth-method [auth-options]

hostssl database user IP-address IP-mask auth-method [auth-options]

hostnossl database user IP-address IP-mask auth-method [auth-options]

local：匹配使用Unix域套接字的连接，如果没有此类型的记录，则不允许使用Unix域套接字连接。

host：匹配使用TCP/IP进行的连接，主机记录匹配SSL或非SSL连接，需要配置listen_addresses。

hostssl：匹配使用TCP/IP进行的连接，仅限于使用SSL加密进行连接，需要配置ssl参数。

hostnossl：匹配通过TCP/IP进行的连接，不使用SSL的连接。

database：匹配的数据库名称，all指定它匹配所有数据库。如果请求的数据库与请求的用户具有相同的名称则可以使用samerole值。复制（replication）不指定数据库，多个数据库可以用逗号分隔。

user：匹配的数据库用户名，值all指定它匹配所有用户。 可以通过用逗号分隔来提供多个用户名。

address：匹配的客户端计算机地址，可以包含主机名，IP地址范围。如：172.20.143.89/32、172.20.143.0/24、10.6.0.0/16、:: 1/128。 0.0.0.0/0表示所有IPv4地址，:: 0/0表示所有IPv6地址。要指定单个主机，请使用掩码长度32（对于IPv4）或128（对于IPv6）。all以匹配任何IP地址。

IP-address、IP-mask：这两个字段可用作IP地址/掩码长度，如：127.0.0.1 255.255.255.255。

auth-method：指定连接与此记录匹配时要使用的身份验证方法：trust、reject、scram-sha-256、md5、password、gss、sspi、ident、peer、ldap、radius、cert、pam、bsd。

trust：允许无条件连接，允许任何PostgreSQL用户身份登录，而无需密码或任何其他身份验证。

reject：拒绝任何条件连接，这对于从组中“过滤掉”某些主机非常有用。

scram-sha-256：执行SCRAM-SHA-256身份验证以验证用户的密码。

md5：执行SCRAM-SHA-256或MD5身份验证以验证用户的密码。

password：要提供未加密的密码以进行身份​​验证。由于密码是通过网络以明文形式发送的，因此不应在不受信任的网络上使用。

gss：使用GSSAPI对用户进行身份验证，这仅适用于TCP / IP连接。

sspi：使用SSPI对用户进行身份验证，这仅适用于Windows。

ident：通过联系客户端上的ident服务器获取客户端的操作系统用户名，并检查它是否与请求的数据库用户名匹配。 Ident身份验证只能用于TCP / IP连接。为本地连接指定时，将使用对等身份验证。

peer：从操作系统获取客户端的操作系统用户名，并检查它是否与请求的数据库用户名匹配。这仅适用于本地连接。

ldap：使用LDAP服务器进行身份验证。

radius：使用RADIUS服务器进行身份验证。

cert：使用SSL客户端证书进行身份验证。

pam：使用操作系统提供的可插入身份验证模块（PAM）服务进行身份验证。

bsd：使用操作系统提供的BSD身份验证服务进行身份验证。

auth-options：在auth-method字段之后，可以存在name = value形式的字段，用于指定认证方法的选项。

例子：

# TYPE DATABASE USER ADDRESS METHOD

local all all trust

--在本地允许任何用户无密码登录

local all all peer

--操作系统的登录用户和pg的用户是否一致，一致则可以登录

local all all ident

--操作系统的登录用户和pg的用户是否一致，一致则可以登录

host all all 192.168.163.0/24 md5

--指定客户端IP访问通过md5身份验证进行登录

host all all 192.168.163.132/32 password---不许加密

--指定客户端IP通过passwotd身份验证进行登录

host all all 192.168.54.1/32 reject

host all all 192.168.0.0/16 ident

host all all 127.0.0.1 255.255.255.255 trust

...

设置完之后可以通过查看表来查看hba：

demo=# select * from pg_hba_file_rules;

line_number | type | database | user_name | address | netmask | auth_method | options | error

-------------+-------+---------------+-----------+---------------+-----------------------------------------+-------------+---------+-------

87 | host | {all} | {all} | 192.168.163.0 | 255.255.255.0 | md5 | |

92 | local | {all} | {all} | | | peer | |

94 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |

96 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |

99 | local | {replication} | {all} | | | peer | |

100 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |

101 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |

修改完pg_hba.conf文件之后，需要重新加载配置，不用重启数据库：

postgres=# select pg_reload_conf();

pg_reload_conf

----------------

t

日常使用

现在按照一个正常项目上线的流程来创建一个应用账号为例，看看需要怎么操作。

比如一个项目demo上线：用管理账号来操作

创建数据库：

postgres=# create database demo;

CREATE DATABASE

创建账号：账号和数据库名字保持一致（search_path）

postgres=# create user demo with password 'demo';

CREATE ROLE

创建schema：不能用默认的public的schma

postgres=# \c demo

You are now connected to database "demo" as user "postgres".

demo=# create schema demo;

CREATE SCHEMA

授权：

#访问库

demo=# grant connect on database demo to demo;

GRANT

#访问schmea

demo=# grant usage on schema demo to demo;

GRANT

#访问表

demo=# grant select,insert,update,delete on all tables in schema demo to demo;

GRANT

#如果访问自增序列，需要授权

demo=# grant select,update on all sequences in schema demo to demo;

GRANT

注意：上面的授权只对历史的一些对象授权，后期增加的对象是没有权限的，需要给个默认权限

#默认表权限

demo=# ALTER DEFAULT PRIVILEGES IN SCHEMA demo GRANT select,insert,update,delete ON TABLES TO demo;

ALTER DEFAULT PRIVILEGES

#默认自增序列权限

demo=# ALTER DEFAULT PRIVILEGES IN SCHEMA demo GRANT select,update ON sequences TO demo;

ALTER DEFAULT PRIVILEGES

查看用户的权限

testdb=> select * from information_schema.table_privileges where grantee='test';

grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy

----------+---------+---------------+--------------+------------+----------------+--------------+----------------

test | test | testdb | test | t_test | INSERT | YES | NO

test | test | testdb | test | t_test | SELECT | YES | YES

test | test | testdb | test | t_test | UPDATE | YES | NO

test | test | testdb | test | t_test | DELETE | YES | NO

test | test | testdb | test | t_test | TRUNCATE | YES | NO

test | test | testdb | test | t_test | REFERENCES | YES | NO

test | test | testdb | test | t_test | TRIGGER | YES | NO

test | test | testdb | test | t2 | INSERT | YES | NO

test | test | testdb | test | t2 | SELECT | YES | YES

test | test | testdb | test | t2 | UPDATE | YES | NO

test | test | testdb | test | t2 | DELETE | YES | NO

test | test | testdb | test | t2 | TRUNCATE | YES | NO

test | test | testdb | test | t2 | REFERENCES | YES | NO

test | test | testdb | test | t2 | TRIGGER | YES | NO

test | test | testdb | test | t3 | INSERT | YES | NO

test | test | testdb | test | t3 | SELECT | YES | YES

test | test | testdb | test | t3 | UPDATE | YES | NO

test | test | testdb | test | t3 | DELETE | YES | NO

test | test | testdb | test | t3 | TRUNCATE | YES | NO

(19 rows)

SET ROLE

SET ROLE — 设置当前会话的当前用户标识符

语法

SET [ SESSION | LOCAL ] ROLE role_name

SET [ SESSION | LOCAL ] ROLE NONE

RESET ROLE

描述

这个命令把当前 SQL 会话的当前用户标识符设置为 role_name。角色名可以写成一个标识符或者一个字符串。在 SET ROLE之后，对 SQL 命令的权限检查时就好像该角色就是原先登录的角色一样。

当前会话用户必须是指定的角色role_name 的一个成员（如果会话用户是一个超级用户，则可以选择任何角色）。

SESSION和LOCAL修饰符发挥的作用和常规的SET命令一样。

NONE和RESET形式把当前用户标识符重置为当前会话用户标识符。这些形式可以由任何用户执行。

注解

使用这个命令可以增加特权或者限制特权。如果会话用户角色具有 INHERITS属性，则它会自动具有它能 SET ROLE到的所有角色的全部特权。在这种情况下 SET ROLE实际会删除所有直接分配给会话用户的特权以及分配给会话用户作为其成员的其他角色的特权，只留下所提及角色可用的特权。换句话说，如果会话用户没有 NOINHERITS属性，SET ROLE会删除直接分配给会话用户的特权而得到所提及角色可用的特权。

特别地，当一个超级用户选择SET ROLE到一个非超级用户角色时，它们会丢失其超级用户特权。

SET ROLE的效果堪比 SET SESSION AUTHORIZATION，但是涉及的特权检查完全不同。还有，SET SESSION AUTHORIZATION决定后来的SET ROLE命令可以使用哪些角色， 不过用 SET ROLE更改角色并不会改变后续 SET ROLE能够使用的角色集。

SET ROLE不会处理角色的ALTER ROLE 设置指定的会话变量。这只在登录期间发生。

SET ROLE不能在一个 SECURITY DEFINER函数中使用。

示例

查看当前会话的原用户，和当前用户

postgres=#SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--------------+--------------

postgres | postgres

修改当前会话的用户为TEST

SET ROLE 'test';

postgres=#SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--------------+--------------

postgres | test

重置为当前会话原来的用户

postgres=# RESET ROLE;

RESET

postgres=# SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--------------+--------------

postgres | postgres

DROP SCHEMA

DROP SCHEMA — 移除一个模式

语法

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

描述

DROP SCHEMA从数据库中移除模式。

一个模式只能由其拥有者或一个超级用户删除。注意即使拥有者不拥有该模式中的某些对象，也能删除该模式（以及所有含有的对象）。

参数

IF EXISTS

如果该模式不存在则不要抛出一个错误，而是发出一个提示。

name

一个模式的名称。

CASCADE

自动删除包含在该模式中的对象（表、函数等），然后删除所有依赖于那些对象的对象（见第 5.13 节）。

RESTRICT

如果该模式含有任何对象，则拒绝删除它。这是默认值。

注解

使用CASCADE选项可能会使这条命令移除除指定模式之外其他模式中的对象。

示例

要从数据库中移除模式test及其中所包含的对象：

postgres=# DROP SCHEMA test CASCADE;

DROP SCHEMA

DROP ROLE

DROP ROLE — 移除一个数据库角色

语法

DROP ROLE [ IF EXISTS ] name [, ...]

描述

DROP ROLE移除指定的角色。要删除一个超级用户角色，你必须自己就是一个超级用户。要删除一个非超级用户角色，你必须具有CREATEROLE特权。

如果一个角色仍然被集簇中任何数据库中引用，它就不能被移除。如果尝试移除将会抛出一个错误。在删除该角色前，你必须删除（或者重新授予所有权）它所拥有的所有对象并且收回该已经授予给该角色的在其他对象上的特权。REASSIGN OWNED和DROP OWNED 命令可以用于这个目的。

不过，没有必要移除涉及该角色的角色成员关系。 DROP ROLE会自动收回目标角色在其他角色中的成员关系，以及其他角色在目标角色中的成员关系。其他角色不会被删除也不会被影响。

参数

IF EXISTS

如果该角色不存在则不要抛出一个错误，而是发出一个提示。

name

要移除的角色的名称。

注解

PostgreSQL包括一个程序dropuser具有和这个命令完全相同的功能（事实上它会调用这个命令），但是该程序可以从 shell 运行。

示例

要删除一个角色：

postgres=# DROP ROLE demo;

DROP ROLE

DROP DATABASE

DROP DATABASE — 移除一个数据库

语法

DROP DATABASE [ IF EXISTS ] name

描述

DROP DATABASE移除一个数据库。它会移除该数据库的系统目录项并且删除包含数据的文件目录。它只能由数据库拥有者执行。还有，当你或者任何其他人已经连接到目标数据库时，它不能被执行（连接到postgres或者任何其他数据库来发出这个命令）。

DROP DATABASE不能被撤销。请小心使用！

参数

IF EXISTS

如果该数据库不存在则不要抛出一个错误，而是发出一个提示。

name

要移除的数据库的名称。

注解

DROP DATABASE不能在一个事务块内执行。

示例

postgres=# DROP DATABASE demo;

DROP DATABASE

dropdb

dropdb — 移除一个PostgreSQL数据库

语法

dropdb [connection-option...] [option...] dbname

描述

dropdb毁掉一个现有的PostgreSQL数据库。执行这个命令的用户必须是一个数据库超级用户或该数据库的拥有者。

dropdb是SQL命令DROP DATABASE的一个包装器。在通过这个工具和其他方法访问服务器来删除数据库之间没有实质性的区别。

选项

dropdb接受下列命令行参数：

dbname

指定要被移除的数据库的名字。

-e

--echo

回显dropdb生成并发送给服务器的命令。

-i

--interactive

在做任何破坏性的工作之前发出一个验证提示。

-V

--version

打印dropdb版本并退出。

--if-exists

如果数据库不存在也不抛出一个错误。在这种情况下会发出一个提醒。

-?

--help

显示有关dropdb命令行参数的帮助并退出。

dropdb也接受下列命令行参数作为连接参数：

-h host

--host=host

指定运行服务器的机器的主机名。如果该值以一个斜线开始，它被用作 Unix 域套接字的目录。

-p port

--port=port

指定服务器正在监听连接的 TCP 端口或本地 Unix 域套接字文件扩展。

-U username

--username=username

要作为哪个用户连接。

-w

--no-password

从不发出一个口令提示。如果服务器要求口令认证并且没有其他方式提供口令（例如一个.pgpass文件），那儿连接尝试将失败。这个选项对于批处理任务和脚本有用，因为在其中没有一个用户来输入口令。

-W

--password

强制dropdb在连接到一个数据库之前提示要求一个口令。

这个选项不是必不可少的，因为如果服务器要求口令认证，dropdb将自动提示要求一个口令。但是，dropdb将浪费一次连接尝试来发现服务器想要一个口令。在某些情况下值得用-W来避免额外的连接尝试。

–maintenance-db=dbname

指定要连接到来发现哪些其他数据库应该被删除的数据库名。如果没有指定，将使用postgres数据库。而如果它也不存在，将使用template1。

环境

PGHOST

PGPORT

PGUSER

默认连接参数

和大部分其他PostgreSQL工具相似，这个工具也使用libpq支持的环境变量。

示例

要在默认数据库服务器上毁掉数据库demo：

$ dropdb demo

要使用在主机eden、端口 5000 上的服务器中毁掉数据库demo，并带有验证和回显：

$ dropdb -p 5000 -h eden -i -e demo

Database "demo" will be permanently deleted.

Are you sure? (y/n) y

DROP DATABASE demo;

2.权限管理

1.需求

1.开展一个名为foresight的项目，需要建立数据库foresight

2.该项目很多部门同事协同完成，需要多个用户同事操作数据库

3.不同用户可以创建修改管理自己的表，但是不能修改别人的（只有创建的人能修改自己的表）

4.用户可以通过授权让其他用户对自己创建的表进行增删改查

2.实现过程

1.创建用户和数据库

1.创建数据库，移除数据库下的public模式

CREATE DATABASE foresight

ENCODING = 'UTF8'

TABLESPACE = pg_default

LC_COLLATE = 'zh_CN.UTF-8'

LC_CTYPE = 'zh_CN.UTF-8'

CONNECTION LIMIT = -1

template template0;

\c foresight ----切换到库

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

#第一个public代表schema

#第二个PUBLIC代表所有用户

2.创建开发部门用户-dev01--注意权限

create user dev01 encrypted password 'dev01' nosuperuser nocreatedb nocreaterole noreplication noinherit VALID UNTIL 'infinity';

3.创建测试部门用户-test01

create user test01 encrypted password 'test01' nosuperuser nocreatedb nocreaterole noreplication noinherit VALID UNTIL 'infinity';

2.为每个用户创建schema(连接到数据库下)

1.dev01用户

foresight=# \c foresight

您现在已经连接到数据库 "foresight",用户 "postgres".

foresight=# CREATE SCHEMA dev01 AUTHORIZATION dev01;

CREATE SCHEMA

2.test01用户

foresight=# \c foresight

您现在已经连接到数据库 "foresight",用户 "postgres".

CREATE SCHEMA test01 AUTHORIZATION test01;

3.用户schema设置

1.登录查看

psql -U dev01 -d foresight -h 127.0.0.1 -W

foresight=> SHOW search_path;

search_path

-----------------

"$user", public

2.设置schema顺序

#只在当前窗口生效

SET search_path TO dev01,test01,public;

#永久生效

ALTER ROLE dev01 SET search_path TO dev01,test01,public;

4.用户创建各自的表

1.dev01用户登录创建自己的表

foresight=> CREATE TABLE dev_tb (id int);

CREATE TABLE

foresight=> \dt dev_tb

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+--------+--------+--------

dev01 | dev_tb | 数据表 | dev01

(1 行记录)

2.test01用户登录创建自己的表

foresight=> CREATE TABLE test_tb (id int);

CREATE TABLE

foresight=> \dt test_tb

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+---------+--------+--------

test01 | test_tb | 数据表 | test01

(1 行记录)

3.此时权限说明

此时用户相互看不到自己创建的表

5.让测试用户可以查看开发用户的表

1.以管理postgres登录进行授权

postgres=# \c foresight

您现在已经连接到数据库 "foresight",用户 "postgres".

foresight=# grant usage on schema dev01 to test01;

GRANT

foresight=# grant select on all tables in schema dev01 to test01;

GRANT

foresight=# grant usage on all sequences in schema dev01 to test01;

GRANT

2.给test01用户加上schema搜素路径

ALTER ROLE test01 SET search_path TO "$user",dev01;

3.登录test01用户查看dev用户的表

psql -U test01 -d foresight -h 127.0.0.1 -W

foresight=> \dt

关联列表

架构模式 | 名称 | 类型 | 拥有者

----------+---------+--------+--------

dev01 | dev_tb | 数据表 | dev01

test01 | test_tb | 数据表 | test01

(2 行记录)

foresight=> select * from dev_tb;

id

----

(0 行记录)

6.让开发用户可以修改测试用户的表

postgres@ubuntu:~/mydb$ psql

psql (9.6.6)

输入 "help" 来获取帮助信息.

postgres=# \c foresight

您现在已经连接到数据库 "foresight",用户 "postgres".

foresight=# grant usage on schema test01 to dev01;

GRANT

foresight=# grant select,insert,update,delete on all tables in schema test01 to dev01;

GRANT

foresight=# grant usage on all sequences in schema test01 to dev01;

GRANT

foresight=# ALTER ROLE dev01 SET search_path TO "$user",test01;

ALTER ROLE

7.删除测试用户对开发表的查看权限

REVOKE select ON ALL TABLES IN SCHEMA dev01 FROM test01 ;

3.相关命令

1.查看一个表的授权信息

SELECT grantee, privilege_type

FROM information_schema.role_table_grants

WHERE table_name='test_tb';

grantee | privilege_type

---------+----------------

test01 | INSERT

test01 | SELECT

test01 | UPDATE

test01 | DELETE

test01 | TRUNCATE

test01 | REFERENCES

test01 | TRIGGER

dev01 | INSERT

dev01 | SELECT

dev01 | UPDATE

dev01 | DELETE文章来源地址https://www.toymoban.com/news/detail-453664.html