A "public" role is a special one - it just means everyone.
To limit access from some user you need to revoke privileges from role "public", as you did, and then add privileges to some different role. Then add all roles which you need to add access for to this different role.
For example:
-- create a user (role with login privilege)
create role myapp login;
-- set the myapp user's password
\password myapp
-- create the database
create database myapp owner myapp;
-- remove default connection privileges from everyone
revoke connect on database myapp from public;
-- connect to database myapp
\c myapp
-- remove default priviledges from default public schema
revoke all on schema public from public;
-- set the public schema owner, so myapp user can do anything there
alter schema public owner to myapp;
Then if you need another user to access the data, but read only.
-- create a special role
create role myapp_reader;
-- allow the role to connect to the database
grant connect on database myapp to myapp_reader;
-- allow the role to read the tables
grant select on all tables in schema public to myapp_reader;
-- create user in this role
create user myapp_monitor in role myapp_reader;
-- set the password for this user
\password myapp_monitor
This is a little more complicated than it should. It is this way for compatibility with old versions.
