Skip to content

Latest commit

 

History

History
62 lines (46 loc) · 2.6 KB

users.md

File metadata and controls

62 lines (46 loc) · 2.6 KB
Raw

PostgreSQL users

Check user privileges

To check tables owned by user, run:

SELECT * FROM pg_tables WHERE tableowner='username' ORDER BY tablename;

To check table grants, run:

SELECT * FROM information_schema.role_table_grants WHERE grantee='username' ORDER BY table_schema,table_name;

To check schemas with grants for specific user, run:

SELECT
  r.usename as grantor, e.usename as grantee, nspname, privilege_type, is_grantable
FROM pg_namespace, aclexplode(nspacl) AS a
  JOIN pg_user e ON a.grantee = e.usesysid
  JOIN pg_user r ON a.grantor = r.usesysid
WHERE e.usename = 'billing_readonly' ORDER BY nspname;

Drop user with assigned objects

REASSIGN OWNED BY <user> TO <other-user>;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TABLES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SEQUENCES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON FUNCTIONS FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ROUTINES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE GRANT OPTION FOR ALL PRIVILEGES ON TYPES FROM <user>;

REVOKE GRANT OPTION FOR ALL PRIVILEGES ON SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA <schema> FROM <user>;
REVOKE GRANT OPTION FOR ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA <schema> FROM <user>;

ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON TABLES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON SEQUENCES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON FUNCTIONS FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON ROUTINES FROM <user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> REVOKE ALL PRIVILEGES ON TYPES FROM <user>;

REVOKE ALL PRIVILEGES ON SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL PROCEDURES IN SCHEMA <schema> FROM <user>;
REVOKE ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA <schema> FROM <user>;

DROP ROLE <user>;