PostgreSQL 9.1 – Fixing ‘Role Public Does Not Exist’ Error

permissionspostgresqlpostgresql-9.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.

Best Answer

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.

Related Solutions

PostgreSQL Permissions – When Privileges Are Listed in \l

The backslash commands in psql are shortcuts for a query or queries that look through the system catalogs. The \l command looks at information in pg_catalog.pg_database, specifically, this query:

SELECT d.datname as "Name",
   pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
   pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
   d.datcollate as "Collate",
   d.datctype as "Ctype",
   pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d
ORDER BY 1;

You can make psql show what it is using for the backslash commands by passing the -E flag to it when you invoke it on the command line.

If the permissions on a database or other object are the defaults that PostgreSQL creates them with, the *acl column will be NULL. If you change the defaults, as you have, the ACL column will be populated with information related to the GRANT and/or REVOKE statements you have ran.

You can see the permissions/ACLs specifically via either \z or \dp

If you read further here:

http://www.postgresql.org/docs/9.4/static/sql-grant.html

If you scroll down, (or search for the word psql), you can look at the table that shows you how to interpret the ACLs that you see with \l or in an ACL column.

For example:

=Tc/vagrant

means that PUBLIC (the implicit role that contains all roles) has permissions to create temporary tables T and connect c, because the ACL line =xxxxx denotes permissions applied to PUBLIC, while rolname=xxxx applies to that specific role.

This presentation from Dalibo should also help clarify this further: Managing Rights in PostgreSQL

Hope that helps. =)

PostgreSQL Schema – ‘Role Does Not Exist’ Error When Granting Usage

Your problem is that the role you are after has capital letters in its name. Mixing cases is usually a bad idea in PostgreSQL, because you need to double-quote such names.

So your grant should look like

GRANT ALL ON SCHEMA public TO "MyRole";

I'd suggest renaming that role to "myrole", to avoid such problems later.

Best Answer

Related Solutions

PostgreSQL Permissions – When Privileges Are Listed in \l

PostgreSQL Schema – ‘Role Does Not Exist’ Error When Granting Usage

Related Question