In Greenplum, user roles and permissions play a crucial role in controlling access to the database and managing security. Here is a guide on Greenplum user roles and permissions:
1. User Roles:
- Definition:
- A role is a named collection of privileges that can be assigned to users or other roles.
- Predefined Roles:
- Greenplum comes with predefined roles such as superuser, createuser, and readonly.
2. Creating User Roles:
- Syntax:
l
CREATE ROLE role_name [ [ WITH ] option [ ... ] ]
- Example:
l
CREATE ROLE analyst_user LOGIN PASSWORD 'password';
3. Granting Privileges:
- Syntax:
l
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ... } [ ( column_name [, ...] ) ]
| ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
- Example:
l
GRANT SELECT, INSERT, UPDATE ON sales TO analyst_user;
4. Revoking Privileges:
- Syntax:
l
REVOKE { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | ... } [ ( column_name [, ...] ) ]
| ALL [ PRIVILEGES ] }
ON [ TABLE ] table_name [, ...]
FROM { role_name | PUBLIC } [, ...] [ CASCADE | RESTRICT ]
- Example:
l
REVOKE SELECT, INSERT, UPDATE ON sales FROM analyst_user;
5. Granting Membership in Roles:
- Syntax:
l
GRANT role_name [, ...]
TO role_specification [, ...] [, ...] [ WITH ADMIN OPTION ]
- Example:
l
GRANT analyst_role TO manager_role;
6. Revoking Membership in Roles:
- Syntax:
l
REVOKE role_name [, ...]
FROM role_specification [, ...] [, ...] [ CASCADE | RESTRICT ]
- Example:
l
REVOKE analyst_role FROM manager_role;
7. Superuser Role:
- Definition:
- The superuser role has all privileges and can perform any action in the database.
- Use Case:
- Typically reserved for database administrators.
8. Database Ownership:
- Syntax:
l
ALTER DATABASE name OWNER TO new_owner
- Example:
l
ALTER DATABASE mydb OWNER TO analyst_user;
9. Schema Ownership:
- Syntax:
l
ALTER SCHEMA name OWNER TO new_owner
- Example:
l
ALTER SCHEMA public OWNER TO analyst_user;
10. Role Attributes:
- LOGIN Attribute:
- Determines if a role is allowed to log in.
l
CREATE ROLE readonly_user LOGIN PASSWORD 'password';
- SUPERUSER Attribute:
- Grants superuser privileges to a role.
l
CREATE ROLE admin_user SUPERUSER PASSWORD 'password';
11. Role Options:
- INHERIT Attribute:
- Determines if a role inherits privileges of roles it is a member of.
l
CREATE ROLE employee_user INHERIT;
- CREATEDB Attribute:
- Allows a role to create databases.
l
CREATE ROLE db_creator CREATEDB PASSWORD 'password';
12. Default Roles:
- PUBLIC Role:
- A default role that every user is a member of by default.
l
GRANT USAGE ON SCHEMA public TO PUBLIC;
13. Viewing Roles:
- \du Command:
- Use the `\du` command in the Greenplum psql console to view a list of roles.
l
\du
14. Privilege Management Best Practices:
- Principle of Least Privilege:
- Assign only the necessary privileges to roles and users.
- Regular Review:
- Periodically review and adjust role memberships and privileges.
- Documentation:
- Maintain comprehensive documentation of roles, memberships, and associated privileges.
15. Role Hierarchy:
- Nested Roles:
- Create role hierarchies by making one role a member of another.
l
CREATE ROLE manager_role;
GRANT analyst_role TO manager_role;
16. Role Membership Checks:
- HAS_ROLE Function:
- Use the `pg_has_role` function to check if a user has a specific role.
l
SELECT pg_has_role('analyst_user', 'analyst_role', 'MEMBER');
17. Audit Logging:
- Enable Audit Logging:
- Enable audit logging to track role and privilege changes.
l
ALTER DATABASE mydb SET audit_role = on;
18. Revoking Connect Privileges:
- Restricting Login:
- Use the `NOLOGIN` attribute to revoke login privileges.
l
ALTER ROLE suspended
No comments:
Post a Comment