在PostgreSQL中创建具有不同特权的用户,并列出这些用户及其特权。

huangapple go评论72阅读模式
英文:

Creating users with different privileges and list these users with their privileges in PostgreSQL

问题

我想为数据库创建具有不同权限的用户,例如读取、写入和管理员权限。
我使用授权创建了用户,但当我使用\du时,它没有显示我可以用来查找用户权限的任何内容。
我还使用了以下命令,但它们也没有显示我可以用来查找用户权限的任何内容。

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls
FROM pg_roles
WHERE rolname = 'your_username';

SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'your_schema' AND table_name = 'your_table';

我还使用了预定义角色像pg_read_all_data,但它会影响所有数据库,而我想它只影响特定的数据库。

我应该怎么做?

英文:

I want to create users with different priviledges for example read and write and admin for just a database.
I create users with grants but when I use \du it doesn't show me anything that I can use to find users privileges.
I also used these commands but they don't show me anything that I can use to find users privileges.

SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolreplication, rolbypassrls
FROM pg_roles
WHERE rolname = 'your_username';

SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'your_schema' AND table_name = 'your_table';

I also use predefined roles like pg_read_all_data but It will be affect on all databases but I want it affects on a specific database.

What should I do?

答案1

得分: 1

我使用以下语句创建用户:

这将阻止默认用户创建表:

REVOKE CREATE ON SCHEMA public FROM public;

只读:

CREATE USER read WITH PASSWORD 'Read@123'; GRANT USAGE ON SCHEMA public TO read; GRANT SELECT ON ALL TABLES IN SCHEMA public TO read; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read;

读写:

CREATE USER write WITH PASSWORD 'Write@123'; GRANT USAGE ON SCHEMA public TO write; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO write; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO write;

管理员:

CREATE USER admin WITH PASSWORD 'Admin@123'; GRANT USAGE, CREATE ON SCHEMA public TO admin; GRANT ALL ON ALL TABLES IN SCHEMA public TO admin; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO admin;

然后我使用以下查询查找权限:

select grantee, privilege_type from information_schema.role_table_grants;

SELECT * FROM information_schema.table_privileges;

SELECT "grantee", array_to_string(array_agg(distinct "privilege_type"),',') AS privilege_type FROM information_schema.role_table_grants where grantee not in ('postgres','PUBLIC') GROUP BY "grantee";

\z

SELECT 
  nspname,         -- schema name
  defaclobjtype,   -- object type
  defaclacl        -- default access privileges
FROM pg_default_acl a JOIN pg_namespace b ON a.defaclnamespace=b.oid;

结果应该如下所示:

在PostgreSQL中创建具有不同特权的用户,并列出这些用户及其特权。

英文:

I create users with these statements:

This will prevent default users from creating tables:

REVOKE CREATE ON SCHEMA public FROM public;

Read Only:

CREATE USER read WITH PASSWORD 'Read@123';GRANT USAGE ON SCHEMA public TO read;GRANT SELECT ON ALL TABLES IN SCHEMA public TO read;ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read;

Read & Write:

CREATE USER write WITH PASSWORD 'Write@123'; GRANT USAGE ON SCHEMA public TO write; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO write; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO write;

Admin:

CREATE USER admin WITH PASSWORD 'Admin@123';GRANT USAGE,CREATE ON SCHEMA public TO admin;GRANT ALL ON ALL TABLES IN SCHEMA public TO admin;ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO admin;

Then I use this queries to find previleges:

select grantee, privilege_type from information_schema.role_table_grants;

SELECT * FROM information_schema. table_privileges;

SELECT "grantee",
array_to_string(array_agg(distinct "privilege_type"),',') AS privilege_type
FROM information_schema.role_table_grants
where grantee not in ('postgres','PUBLIC')
GROUP BY "grantee";

\z

SELECT 
  nspname,         -- schema name
  defaclobjtype,   -- object type
  defaclacl        -- default access privileges
FROM pg_default_acl a JOIN pg_namespace b ON a.defaclnamespace=b.oid;

Results should be like this:

在PostgreSQL中创建具有不同特权的用户,并列出这些用户及其特权。

huangapple
  • 本文由 发表于 2023年4月17日 17:47:55
  • 转载请务必保留本文链接:https://go.coder-hub.com/76033795.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定