# PostgreSQL Read-Only User

[TOC]

###### tags: `postgresql`

---

## `public` schema and `PUBLIC` role

When a new database is created, PostgreSQL by default creates a schema named **`public`** and grants access on this schema to a backend role named **`PUBLIC`**. All new users and roles are by default granted this public role, and therefore can create objects in the public schema.

### Revoke the default `CREATE` permission on the `public` schema from the `PUBLIC` role

Make sure you are **the owner of the public schema** or are part of a role that allows you to run this SQL statement.

REVOKE CREATE
ON SCHEMA public
FROM PUBLIC;

### Revokes the `PUBLIC` role’s ability to connect to the database

REVOKE ALL
ON DATABASE mydatabase
FROM PUBLIC;

## Creating read-only role

CREATE ROLE readonly;

### Grant the `readonly` role permission to connect to your target database

GRANT CONNECT
ON DATABASE mydatabase
TO readonly;

### Grant the `readonly` role USAGE access to your schema

GRANT USAGE
ON SCHEMA public
TO readonly;

Without this step, the `readonly` role **cannot perform any action** on the objects in this schema, **even if the permissions were granted** for those objects.

### Grant the `readonly` role access to run SELECT on all the tables and views in the schema

GRANT SELECT
ON ALL TABLES
IN SCHEMA public
TO readonly;

### Ensure that new tables and views will be accessible by the `readonly` role

ALTER DEFAULT PRIVILEGES
IN SCHEMA public
GRANT SELECT
ON TABLES
TO readonly;

### Creating read-only user

CREATE USER someone WITH
LOGIN
PASSWORD 'secret';

GRANT readonly
TO someone;

## Creating read/write role

CREATE ROLE readwrite;

### Grant the `readwrite` role permission to connect to your target database

GRANT CONNECT
ON DATABASE mydatabase
TO readwrite;

### Grant the `readwrite` role USAGE access to your schema

GRANT USAGE
ON SCHEMA public
TO readwrite;

The following instead if you want to allow this role to create new objects like tables in this schema:

GRANT USAGE,CREATE
ON SCHEMA public
TO readwrite;

### Grant the `readwrite` role access to run SELECT on all the tables and views in the schema

GRANT INSERT,SELECT,UPDATE,DELETE
ON ALL TABLES
IN SCHEMA public
TO readwrite;

### Ensure that new tables and views will be accessible by the `readwrite` role

ALTER DEFAULT PRIVILEGES
IN SCHEMA public
GRANT INSERT,SELECT,UPDATE,DELETE
ON TABLES
TO readwrite;

### Grant the `readwrite` role permission to all sequences

GRANT USAGE
ON ALL SEQUENCES
IN SCHEMA public
TO readwrite;

### Ensure that future sequences will be accessible by the `readwrite` role

ALTER DEFAULT PRIVILEGES
IN SCHEMA public
GRANT USAGE
ON SEQUENCES
TO readwrite;

### Creating read/write user

CREATE USER someoneelse WITH
LOGIN
PASSWORD 'secret';

GRANT readwrite
TO someoneelse;

## Reference

- [AWS Database Blog » **Managing PostgreSQL users and roles**](https://aws.amazon.com/tw/blogs/database/managing-postgresql-users-and-roles/) by **Yaser Raja**