A new role cannot read any table. You give it access with privileges.
Privileges on a table
| Privilege | Lets the role |
|---|---|
SELECT | read rows |
INSERT | add rows |
UPDATE | change rows |
DELETE | remove rows |
The role that creates a table is its owner. The owner, and any superuser, has every privilege on it and can give privileges to others.
GRANT
sql
GRANT SELECT ON customers TO support;GRANTgiveSELECTthis privilegeON customerson this tableTO supportto this role
List several privileges, or several tables, with commas:
sql
GRANT SELECT, INSERT ON orders, order_items TO support;GRANT ALL PRIVILEGES ON t TO r gives every privilege. It is easy to type and almost always too much.
Test as the role
SET ROLE makes your session act as another role. A superuser can switch to any role:
sql
SET ROLE support;
SELECT current_user; -- support
SELECT * FROM employees; -- no privilege
RESET ROLE; -- back to postgrestext
ERROR: permission denied for table employees