Re: Read only to schema

Hi !

I have user :

[image: image.png]

and this user can not login to Database AccessLog.

I tried to use:

GRANT CONNECT
ON DATABASE " AccessLog"
TO "Luke";

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.

i tried to use:

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

I have error:

[image: image.png]

Please help,

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

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

Please help,
Jacek

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

> Hello Jacek
>
>
>
> *From:* Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com]
> *Sent:* Samstag, 14. Juli 2018 11:55
> *To:* clavadetscher(at)swisspug(dot)org; pgsql-general(at)postgresql(dot)org >>
> PG-General Mailing List <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> napisał(a):
>
> Hello
>
>
>
> *From:* Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com]
> *Sent:* Freitag, 13. Juli 2018 16:39
> *To:* pgsql-general(at)postgresql(dot)org >> PG-General Mailing List <
> 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=> SELECT SESSION_USER, CURRENT_USER;
>
> session_user | current_user
>
> --------------+--------------
>
> jaryszek | jaryszek
>
>
>
> jaryszek(at)db(dot)localhost=> CREATE TABLE public.test (a INTEGER);
>
> CREATE TABLE
>
> jaryszek(at)db(dot)localhost=> INSERT INTO public.test VALUES (1);
>
> INSERT 0 1
>
> 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=> SET ROLE readonly_user ;
>
> SET
>
> 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=> SELECT * FROM public.test;
>
> a
>
> ---
>
> 1
>
> (1 row)
>
>
>
> jaryszek(at)db(dot)localhost=> INSERT INTO public.test VALUES (2);
>
> ERROR: permission denied for relation test
>
>
>
> 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=> RESET ROLE;
>
> RESET
>
> 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> napisał(a):
>
> Maybe read-only view?
>
>
>
> Best,
>
> Jacek
>
>
>
> pt., 13 lip 2018 o 07:00 Łukasz Jarych <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
>
>