RE: Read only to schema

An addition.

From: Charles Clavadetscher [mailto:clavadetscher(at)swisspug(dot)org]
Sent: Donnerstag, 26. Juli 2018 16:57
To: 'Łukasz Jarych' <jaryszek(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: RE: Read only to schema

Hello

From: Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com]
Sent: Mittwoch, 25. Juli 2018 13:50
To: Charles Clavadetscher <clavadetscher(at)swisspug(dot)org <mailto:clavadetscher(at)swisspug(dot)org> >
Cc: pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org> >> PG-General Mailing List <pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org> >
Subject: Re: Read only to schema

Hi !

I have user :

and this user can not login to Database AccessLog.

I tried to use:

GRANT CONNECT

ON DATABASE " AccessLog"

TO "Luke";

I see a typo, i.e. a space between the first double quote and the name of the database.

Didn’t you get an error at this point?

Please provide the result of

SELECT datname, datacl FROM pg_database where datname = 'AccessLog';

GRANT CREATE ON SCHEMA PUBLIC TO "Luke";

GRANT USAGE

ON SCHEMA public

TO "Luke"

So expecting result : can modyfy DDL and DML in whole database but user it is not SUPERUSER.

Well it depends. In schema public yes, but not in other schemas of the database.

If somebody else created, let’s say, a table, user Luke would not have privileges on it, unless you changed the default privileges for schema public and that user.

What does this return?

SELECT nspname, nspacl FROM pg_namespace WHERE nspname = 'public';

i tried to use:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "Luke";

I have error:

Mmh... What do you get with this?

SELECT c.relname,

ns.nspname,

c.relacl

FROM pg_catalog.pg_class c,

pg_catalog.pg_namespace ns

WHERE ns.oid = c.relnamespace

AND c.relname = 't_bladeservers';

Maybe with that information we can try to figure out where the problem is.

Please help,

How to create USER with all privilages (but no superuser) at once?

[Addition]

You could make Luke the owner of the database.

[End of addition]

And how to delete User - drop role "Luke" is saying that there are dependiences for this user...

Yes and it says on which objects. You must first revoke those privileges from the user. e.g.

REVOKE ALL ON TABLES IN SCHEMA PUBLIC FROM "Luke";

REVOKE ALL ON SCHEMA PUBLIC FROM "Luke";

etc.

Regards

Charles

Please help,

Jacek

sob., 14 lip 2018 o 12:31 Charles Clavadetscher <clavadetscher(at)swisspug(dot)org <mailto:clavadetscher(at)swisspug(dot)org> > napisał(a):

Hello Jacek

From: Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com <mailto:jaryszek(at)gmail(dot)com> ]
Sent: Samstag, 14. Juli 2018 11:55
To: clavadetscher(at)swisspug(dot)org <mailto:clavadetscher(at)swisspug(dot)org> ; pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org> >> PG-General Mailing List <pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org> >
Subject: Re: Read only to schema

Thank you very much Charles!

Awesome knowledge, thank you!

I will test it and let you know if it is working like a charm (i am on vacation now and without access to postgresql).

I am wondering with one thing:

GRANT CREATE ON SCHEMA PUBLIC TO jaryszek;

This will allow to create, drop, isnert and delete? All ddl and dml commands?

Or should i use GRANT ALL ON SCHEMA PUBLIC TO jaryszek ?

No. There are 2 privileges that you can grant on a schema:

USAGE: Allows to use objects in that schema.

CREATE: Allows to create and destroy objects in that schema.

None of those says anything about which privileges users have within the schema. You need to define additionally privileges (e.g. select, insert, update, delete) on the objects within the schema.

So in order to get access to a table public.test a user must have:

USAGE on schema public AND SELECT (or whatever) on the table itself. If any of those is missing the user will not be able to access the table.

GRANT ALL is generally a bad idea, althought on schemas there is not much you can do wrong.

It doesn’t hurt if you add USAGE (which would be included in ALL along CREATE) for user jaryszek, but it is not necessary, because public (= any user) was only revoked CREATE (s. example in last mail). That means public still has USAGE on schema public and obviously you are on one of “any user”.

I hope I could explain that somehow.

If you still have questions just get back on the list.

Bye

Charles

Best,

Jacek

sob., 14 lip 2018 o 08:23 Charles Clavadetscher <clavadetscher(at)swisspug(dot)org <mailto:clavadetscher(at)swisspug(dot)org> > napisał(a):

Hello

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

I found something like this:

CREATE ROLE readonly_user

WITH LOGIN

ENCRYPTED PASSWORD '1234'

ALTER ROLE readonly_user

SET search_path to

public

GRANT CONNECT

ON DATABASE "TestDb"

TO readonly_user;

GRANT USAGE

ON SCHEMA public

TO readonly_user;

GRANT USAGE

ON ALL SEQUENCES -- Alternatively: ON SEQUENCE seq1, seq2, seq3 ...

IN SCHEMA public

TO readonly_user;

GRANT SELECT

ON ALL TABLES -- Alternatively: ON TABLE table1, view1, table2 ...

IN SCHEMA public

TO readonly_user;

Question is how to give this user opposite access? I mean give him access to all functionalities like inserting, deleting, creating tables and staff like this.

I mean i want to assign user "jaryszek" to this read_only role and after changing schema i want to give user "jaryszek" all credentials.

Best,

Jacek

You can change your readonly_user to NOINHERIT and GRANT the role to jaryszek.

When you then want to act as readonly_user you set the role explicitly.

Here basically:

Revoke create from public, so that only granted users will be able to create or drop objects.

REVOKE CREATE ON SCHEMA PUBLIC FROM public;

Create the role as group (nologin) and without implicit inheritance of privileges.

CREATE ROLE readonly_user NOINHERIT NOLOGIN;

Your normal user should be able to create tables.

GRANT CREATE ON SCHEMA PUBLIC TO jaryszek;

Add your user to the readonly_user group.

GRANT readonly_user TO jaryszek;

Now when you log in as jaryszek you can create table add data, etc.

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--------------+--------------

jaryszek | jaryszek

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => CREATE TABLE public.test (a INTEGER);

CREATE TABLE

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => INSERT INTO public.test VALUES (1);

INSERT 0 1

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => SELECT * FROM public.test;

a

---

1

(1 row)

Now let’s set up the permissions of readonly_user.

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO readonly_user;

When you want to act as readonly_user you set explicitly that role.

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => SET ROLE readonly_user ;

SET

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--------------+---------------

jaryszek | readonly_user

(1 row)

After this all privileges will be checked against readonly_user. That means:

You can read from tables, but you cannot modify data or change/create tables.

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => SELECT * FROM public.test;

a

---

1

(1 row)

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => INSERT INTO public.test VALUES (2);

ERROR: permission denied for relation test

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => CREATE TABLE public.test2 (a INTEGER);

ERROR: permission denied for schema public

LINE 1: CREATE TABLE public.test2 (a INTEGER);

When you want to get back to your normal role then use

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => RESET ROLE;

RESET

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => INSERT INTO public.test VALUES (2);

INSERT 0 1

The idea is to put all permissions in (group) roles and then impersonate the role that you need setting it explicitly.

I hope this helps.

Bye

Charles

pt., 13 lip 2018 o 12:58 Łukasz Jarych <jaryszek(at)gmail(dot)com <mailto:jaryszek(at)gmail(dot)com> > napisał(a):

Maybe read-only view?

Best,

Jacek

pt., 13 lip 2018 o 07:00 Łukasz Jarych <jaryszek(at)gmail(dot)com <mailto:jaryszek(at)gmail(dot)com> > napisał(a):

Hi Guys,

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

I want to :

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

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

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

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).

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