英文:
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;
结果应该如下所示:
英文:
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论