RE: Read only to schema

Hello

From: Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com]
Sent: Freitag, 13. Juli 2018 07:00
To: pgsql-general(at)postgresql(dot)org >> PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Read only to schema

Hi Guys,

Yesterday i tried all day to figure out system to read only schemas.

I want to :

Here is a try. I did not test it all, but it should go in this direction.

1. Create user who can login (user: jaryszek)

CREATE ROLE jaryszek LOGIN;

\password jaryszek

2. Create role who can read only data (only watching tables) (role: readonly)

Suppose you have a schema xyz

GRANT USAGE ON SCHEMA xyz;

GRANT SELECT ON ALL TABLES IN SCHEMA xyz TO jaryzsek;

3, Create role who can read all data (inserting, deleting, altering, dropping) (role: readall)

CREATE ROLE candoall LOGIN;

\password candoall

GRANT USAGE, CREATE ON SCHEMA xyz;

GRANT SELECT, INSERT, UPDATE DELETE ON ALL TABLES IN SCHEMA xyz TO candoall;

For schema:

USAGE: allows users to see or modify contents of tables in the schema

CREATE: allows users to create new objects in the schema

For tables:

SELECT without other privileges: user can only read from tables

INSERT, UPDATE, DELETE: Well, that shoud be obvious

If you have other objects (sequences, functions, etc) you may need to add other privileges.

What sqls should i use for this?

What grants should i add?

And now i am logged as jaryszek

I want to grant myself role read only to schema public (when owner is postgres).

Schema public has a default privilege setting that grants basically everything to everybody (public). So you must first revoke all those privileges from publc.

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO jaryszek;

You may want to change the default privilege settings for schema public. It would help you to read about which default privileges are set and how to change them:

https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html

Regards

Charles

I want to review tables as views only,

After work i want to grant myself role readall to schema public.

It is possible?

Or possible workaround ?

Best,

Jacek