Granting user permissions for a PostgreSQL database

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

  1. Grant USAGE permissions per schema:
    GRANT USAGE ON SCHEMA public TO xxx;
  2. 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;