Search Postgresql Archives

RE: Read only to schema

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello

 

From: Łukasz Jarych [mailto:jaryszek@xxxxxxxxx]
Sent: Freitag, 13. Juli 2018 07:00
To: pgsql-general@xxxxxxxxxxxxxx >> PG-General Mailing List <pgsql-general@xxxxxxxxxxxxxx>
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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux