Locking down Public Group Role in PostgreSQL

Question

Based on information I've been reading about privileges, I've learned it was good to revoke privileges before assigning any privileges to roles. I had 1 user I was working with called appuser and I did the following:

REVOKE ALL PRIVILEGES ON SCHEMA PUBLIC FROM GROUP PUBLIC;

GRANT SELECT
  ON public.table1 TO appuser;

GRANT SELECT
  ON public.table1_id_seq TO appuser;

GRANT SELECT
  ON public.table2 TO appuser;

GRANT SELECT
  ON public.table2_id_seq TO appuser;

I did this in the hopes of removing any privileges to attached to any newly added users automatically part of the public role membership. Instead I got an error to the effect of appuser does not have permissions to the schema. However this is my misunderstanding with this error. 1. PgAdminIII doesn't show a public role under Group Roles for this schema 2. appuser does not appear to be a member of a group called public (because it doesn't exist in the interface). 3. appuser's privileges were explicitly granted, even if it were part of the public role

So...is there some sort of implicit group role called "Public" that is affecting this user's privileges? I don't understand what's going on. I've also attempted to find out where to display the group role membership from the pgAdminIII command line with no luck there either. By the way to fix it, I simply reversed the first command (i.e. GRANT ALL PRIVILEGES ON SCHEMA PUBLIC TO GROUP PUBLIC); However that basically undoes what I was originally trying to do (i.e. simply lock down the tables).

Any ideas?

Update: I have a hunch, I discovered what the issue may be. I don't think appuser has permissions to the schema. If I'm not mistaken schema permissions must be granted before access to any objects beneath the schema can happen.

Why haven't you granted permissions to appuser on the schema if the error said the role had no permissions for the schema? After all you revoked them for everyone and give them to no one. — Milen A. Radev
You need to show the exact commands you were executing and the exact errors you were getting, not narrative explanations of them. — Peter Eisentraut
@ Milen - I think you hit the nail on the head. What a forehead slapper. I'm going to test this a.m. — Guest Posting
@Peter - I was actually in the process of looking for the commands after I posted this. Since this is a migration process I inherited, I'm still learning where all the pieces are. I'll post an update after doing some more research. — Guest Posting

Chris Travers Chris Travers · Accepted Answer · 2013-12-23T02:30:19

Ok, so the answer here is kind of complicated as to why things behave the way they do and it is probably worth discussing the PostgreSQL security model to some extent.

PUBLIC is not a role or a group but rather a reserved word. The way the permissions work is that access granted to current inherited roles or PUBLIC apply. I have actually been bitten by this in the past as I build frameworks that wrap PostgreSQL roles.

In general, my view is that you want to do as follows:

Don't allow everyone to log into the db.

REVOKE CONNECT ON DATABASE mydb FROM public;

You may want to revoke access to schemas and tables from public too.
What I think you have done is something else, which is to revoke permissions from the schema (but not the tables in the schema!) and therefore reserve PUBLIC access for default permissions to tables in that schema. This may or may not be what you want. At any rate as you found out you need to add back permissions to the schema to get back to where you are.

Now I am not entirely sure what you are trying to do here. If you want to fully lock down your db, you need to do the same for all tables as well. Otherwise if you:

 CREATE USER foo;
 GRANT USAGE ON SCHEMA PUBLIC TO foo;

Then foo will have access to all the permissions that PUBLIC has on the tables.

Locking down Public Group Role in PostgreSQL

1 Answers