PostgreSQL‎ > ‎

privileges

To grant privileges on all objects in a schema, follow this example:

GRANT USAGE ON SCHEMA foo TO foo_read_role;

-- Grant access on current tables in this schema
GRANT SELECT ON ALL TABLES IN SCHEMA foo TO foo_read_role;

-- Grant access on future tables created in this schema
ALTER DEFAULT PRIVILEGES IN SCHEMA foo
  GRANT SELECT ON TABLES TO foo_read_role;

This can be done for sequences, etc.

View all users that are a member of a given role (eg it_developer)

SELECT r.rolname
FROM pg_roles r
WHERE r.oid IN (
        SELECT m.member
        FROM pg_auth_members m
        JOIN pg_roles b ON (m.roleid = b.oid)
        WHERE b.rolname='it_developer'
);

Comments