1

I understand public is not a typical role in postgres:

dav-gis=# \duS public List of roles Role name | Attributes | Member of -----------+------------+-----------

But why can I revoke all privileges on a schema from public, but not assign another role to public? E.g.,

db=# REVOKE ALL PRIVILEGES ON SCHEMA "eia" FROM "public";
REVOKE
db=# GRANT "eia-ro" TO "public";
ERROR:  role "public" does not exist

I'd like to only give public users read access through the read-only role, but perhaps this is just out of sync with how public access works in Postgres.

2

As @Evan Carroll said, there is no "public" role. The manual has this to say:

The key word PUBLIC indicates that the privileges are to be granted to all roles, including those that might be created later. PUBLIC can be thought of as an implicitly defined group that always includes all roles. Any particular role will have the sum of privileges granted directly to it, privileges granted to any role it is presently a member of, and privileges granted to PUBLIC.

...

Unlike the case with privileges, membership in a role cannot be granted to PUBLIC.

  • that helps "implicitly defined group that always includes all roles" – Dylan Hettinger Jul 5 '17 at 19:36
0

There is no role, "public". There is a schema public. The default role of the superuser is postgres. You don't want to revoke privileges to it or mess with it. Keep it locked in PostgreSQL. Keep it as trust with the operating system. Then in order to use it, you have to have it configured with sudo or have root access to the box (in which case, if compromised, you have far more dire problems).

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.