pgadmin: getting "DETAIL: User does not have CONNECT privilege." error

Question

I’m using pgAdmin III on Mac Yosemite. I created a role, “discount”, and created a database “discount”. In the pgadmin tool, how do I give the user “discount” connect privileges (and table read/write privileges) to the database “discount”? Currently, when I try and login at a command line I get this error

davea$ psql -h localhost -d discount -U discount
Password for user discount: 
psql: FATAL:  permission denied for database "discount"
DETAIL:  User does not have CONNECT privilege.

Answer 1

Permission to current objects

To have read/write access to the tables, you need to use GRANT command in 3 levels:

1. DATABASE
2. SCHEMA
3. TABLES, FUNCTIONS, SEQUENCES, etc.

First, you need CONNECT privilege on the database:

GRANT CONNECT ON DATABASE <dbname> TO <username>;

Second, you need USAGE privilege on the schema inside the database (you must connect to the database before running it):

GRANT USAGE ON SCHEMA <schemaname> TO <username>;

At last, you can give the privilege on the tables, suppose you want common DML and SELECT on all tables in the schema, and other permissions:

GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA <schemaname> TO <username>;
-- and the sequences, for INSERT to work
GRANT USAGE ON ALL SEQUENCES IN SCHEMA <schemaname> TO <username>;
-- and the functions
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA <schemaname> TO <username>;

Default permission for objects created in the future

You must now notice something. Each database, and each schema, and each object (table, function, etc.) have an owner. The owner of is the user that will manage and run DDL commands on it. Generally you should run all the above commands while connected as the user which owns everything, because this user already has all permissions (you could use a SUPERUSER too, but I recommend keeping it only for DBA tasks).

The above GRANT ... ON ALL ... IN SCHEMA commands will give permissions to the objects already present in the database, but won't apply to new objects created. In order to do that, you can use ALTER DEFAULT PRIVILEGES (I'll call it ADP) command. As before, you should run that while connected as the owner, because you must keep in mind that ADP is applied only if the owner of the new object matches with the one used here (or set in FOR ROLE clause):

ALTER DEFAULT PRIVILEGES IN SCHEMA <schemaname>
    GRANT SELECT,INSERT,UPDATE,DELETE ON TABLES TO <username>;
-- and the sequences, for INSERT to work
ALTER DEFAULT PRIVILEGES IN SCHEMA <schemaname>
    GRANT USAGE ON SEQUENCES TO <username>;
-- and the functions
ALTER DEFAULT PRIVILEGES IN SCHEMA <schemaname>
    GRANT EXECUTE ON FUNCTIONS TO <username>;

You can also skip IN SCHEMA <schemaname> from above and have it applied for any schema you have or create in the future. But again, be careful, even if you do not provide FOR ROLE <rolename> that means it will apply to the current user connected, so only objects created that is owned by that <rolename> will consider the ADP command.

For a good management of permissions, I highly recommend you keep an eye on which user owns the objects. In most cases, I also recommend you keep only one owner for everything inside the database (unless you are an advanced user and know what you are doing), that way permission management is easier.