PostgreSQL中用户对表的访问权限控制

概述参考资料：http://vhttps://www.postgresql.org/docs/9.5/static/sql-revoke.html   E:\Program Files\HighGo DataBase\bin>psql -E -U highgo -d highgo highgo=# create database lyy; highgo=# create user yy passwor

参考资料：http://vhttps://www.postgresql.org/docs/9.5/static/sql-revoke.html

E:\Program files\HighGo DataBase\bin>psql -E -U highgo -d highgo
highgo=# create database lyy;
highgo=# create user yy password 'yy';

highgo=# \c lyy
You are Now connected to database "lyy" as user "highgo".
lyy=# create table test1(ID int);
CREATE table
lyy=# create table test2(ID int);
CREATE table
--对当前库中所有表去掉public的所有访问权限，为了确保除了所有者之外的洽谈用户不能 *** 作这些表。
lyy=# revoke all on test1 from public;
REVOKE
lyy=# revoke all on test2 from public;
REVOKE
--去掉对pg_class的访问权限，为了确保yy用户不能看到所有表名的列表。
lyy=# revoke all on pg_class from public;
REVOKE
lyy=# revoke all on pg_class from yy;
REVOKE
--添加yy用户对test1表的所属关系，确保yy用户对test1表有权限 *** 作
lyy=# ALTER table test1 OWNER TO yy;
lyy=# \q

--此时用户yy连接lyyku会报错说没有connect权限，那么就授予用户yy对数据库lyy的访问权限
E:\Program files\HighGo DataBase\bin>psql -E -U yy -d lyy
Password for user yy:
psql: FATAL: permission denIEd for database "lyy"
DETAIL: User does not have CONNECT privilege.

E:\Program files\HighGo DataBase\bin>psql -E -U highgo
Password for user highgo:
psql (2.0.2)
Type "help" for help.

highgo=# grant connect on database lyy to yy;
GRANT
highgo=# \q

--此时用户yy连接lyy库后，可以对自己拥有的test表 *** 作，但是对于其他表不能 *** 作，也不能查看所有表的表名列表。
E:\Program files\HighGo DataBase\bin>psql -E -U yy -d lyy
Password for user yy:
psql (2.0.2)
Type "help" for help.

lyy=> select * from test1;
ID
----
(0 rows)

lyy=> select * from test2;
ERROR: permission denIEd for relation test2

lyy=> \d --查看所有表名的列表
********* query **********
SELECT n.nspname as "Schema",
c.relname as "name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'vIEw' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.pg_get_userbyID(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
left JOIN pg_catalog.pg_namespace n ON n.oID = c.relnamespace
WHERE c.relkind IN ('r','v','S','f','')
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oID)
ORDER BY 1,2;
**************************

ERROR: permission denIEd for relation pg_class lyy=>

总结

以上是内存溢出为你收集整理的PostgreSQL中用户对表的访问权限控制全部内容，希望文章能够帮你解决PostgreSQL中用户对表的访问权限控制所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。