If you need to manage databases and permissions for a PostgreSQL server, you can use a WEB, a Desktop or a command line client. I used pgAdmin for WEB and Desktop access.
A PostgreSQL database have an owner. I got an error setting permissions for other users being connected as different user (with root privileges), if you get too – try to grand permissions as database owner!
A databases can contains multiple schemes, each of them contains tables.
Setting connection permissions
First of all you have to grant CONNECTION permissions to the database:
GRANT CONNECT ON DATABASE mydb TO xxx;
Then you have to grant permissions per schemas and tables.
Grant permissions only for existing tables in a schema
- Grant USAGE permissions per schema:
GRANT USAGE ON SCHEMA public TO xxx; - Grant SELECT permissions per table:
GRANT SELECT ON mytable TO xxx;
Grant default permissions for all tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO xxx;