1

I'm trying to achieve a simple architecture:

  1. An organization has many users
  2. A user belongs to many organizations
  3. A user can connect
  4. A user can join and leave an organization
  5. A user can read-only tables granted to organizations

First, securing permissions, as recommended in many places (https://ankane.org/postgres-users, https://marcyes.com/2016/0922-messing-with-postgresql-users-and-permissions/)

REVOKE ALL ON DATABASE stamped_development FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

Then, creating an organizations role with read permissions:

CREATE ROLE organizations;
GRANT USAGE ON SCHEMA public TO organizations;
GRANT SELECT ON ALL TABLES in SCHEMA public TO organizations;
ALTER DEFAULT PRIVILEGES FOR ROLE organizations IN SCHEMA public
  GRANT SELECT ON TABLES TO organizations;

Then, creating a users role with connect permissions:

CREATE ROLE users WITH LOGIN;
GRANT CONNECT ON DATABASE stamped_development TO users;

Create an organization and a user:

CREATE ROLE org_123 IN ROLE organizations;
CREATE ROLE user_123 WITH ENCRYPTED PASSWORD 'secret' IN ROLE users;

Joining and leaving organizations as a user:

GRANT org_123 TO user_123;
REVOKE org_123 FROM user_123;
\du+
                                               List of roles
   Role name   |                         Attributes                         |    Member of    | Description
---------------+------------------------------------------------------------+-----------------+-------------
 org_123       | Cannot login                                               | {organizations} |
 organizations | Cannot login                                               | {}              |
 user_123      | Cannot login                                               | {users,org_123} |
 users         |                                                            | {}              |

Problems:

  • user_123 doesn't seem to inherit LOGIN from users
  • Even when granting LOGIN to user_123 it seems like user_123 can't SELECT tables in the public schema

Any thoughts on what's missing?

Edited to reflect fixes suggested in the answers

  • 'it seems like user_123 can't SELECT tables in the public schema' - does this manifest in some kind of error? Does it apply to all tables in public, or only to the ones created after GRANT SELECT ...? – dezso Feb 19 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.