PostgreSQL Permissions
Concepts
Schema: A namespace that contains named database objects such as tables, views, indexes, data types, functions, stored procedures, and operators.
Role: A role is an entity that can own database objects and have database privileges. Depending on its use, a role can be considered a "user," "group," or both.
User: A user is a role with the ability to log in (the role has the
LOGINattribute).
Minimal Configuration
To perform these operations, you must log in to your PostgreSQL database as an administrator.
The following will create a role we will use to assign permissions but not log in. It then creates a separate user who can log in and grant them access to all the tables in the target schema (this can be limited to a single table or even a single column within a table).
Create a Role
Allow Connection to a Database
Allow Role to use Schema
Grant Access to One or More Tables
All Tables in Schema:
One Table in Schema:
Specific Columns in Schema:
Create User and Assign them to Role
Limiting View to System Tables
In PostgreSQL, system tables and schemas are given the default PUBLIC roles when the database is first created. These tables are view-only tables used by Postgres directly and client-side tooling for running queries, accessing object metadata, query planning, auto-complete, etc. Limiting access to these tables is not recommended, and it is generally not considered a security risk to allow users to read access to them. Additionally, since they are views they only represent the set of permissions that the user actually possesses. IE They can only see the metadata for the tables they have permission to read from.
Last updated