Re: Read only to schema

Hi,

ok i hate this.

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

[image: image.png]
\What does it mean?

I manage to run this commands:

create role "Łukasz" with password 'PasswordAccess'
> GRANT ALL PRIVILEGES ON DATABASE "AccessLog" to "Luke";
> GRANT ALL PRIVILEGES ON DATABASE "SeparateDb" to "Luke";
> grant usage on schema public to "Luke"

but when i want to open table i have :

[image: image.png]

Hmm this is a result from your second query:

[image: image.png]

as i can see there is no "Luke" there...

About creating owner of database?
Can i have more then one owner? i do not think so.

Thank you once more time for your respond and support.

Best,
Jacek

pt., 27 lip 2018 o 10:49 Łukasz Jarych <jaryszek(at)gmail(dot)com> napisał(a):

> Hi Charles.
> Thank you very much for your help !
>
> Thanks to your tips i found solution.
>
> When i am creating new user i am giving him :
>
> GRANT ALL PRIVILEGES ON DATABASE "database_name" to my_username;
> And when i am adding new schema i am granting select on and usage to
> read_user:
>
> GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO read_user;
>
> GRANT USAGE ON SCHEMA test TO read_user;
>
> And this is working like a charm!
>
> Best,
> Jacek
>
>
>
> czw., 26 lip 2018 o 17:12 Charles Clavadetscher <
> clavadetscher(at)swisspug(dot)org> napisał(a):
>
>> 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 <jaryszek(at)gmail(dot)com>]
>> *Sent:* Mittwoch, 25. Juli 2018 13:50
>> *To:* Charles Clavadetscher <clavadetscher(at)swisspug(dot)org>
>> *Cc:* pgsql-general(at)postgresql(dot)org >> PG-General Mailing List <
>> pgsql-general(at)postgresql(dot)org>
>> *Subject:* 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";
>>
>>
>>
>> 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:
>>
>>
>>
>> [image: image.png]
>>
>>
>>
>> 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> 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
>>
>>