Re: Read only to schema

Thank you Charles once again !

And best wishes,
Jacek

sob., 28 lip 2018 o 15:58 Charles Clavadetscher <clavadetscher(at)swisspug(dot)org>
napisał(a):

> Hello Jakek
>
>
>
> *From:* Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com]
> *Sent:* Samstag, 28. Juli 2018 14:13
> *To:* Melvin Davidson <melvin6925(at)gmail(dot)com>; pgsql-general(at)postgresql(dot)org
> >> PG-General Mailing List <pgsql-general(at)postgresql(dot)org>
> *Subject:* Re: Read only to schema
>
>
>
> Hi Guys,
>
>
>
> thank you for such advances replies and help ! You are the best!
>
>
>
> I have found out that this command:
>
>
>
> *GRANT ALL PRIVILAGES ON ALL TABLES IN SCHEMA schema_name TO role_name;*
>
>
>
> Be aware that granting all privilege is not necessarily the best approach.
> You are granting e.g. a user the permission to create triggers on your
> table that can perform actions transparently or to truncate the table.
>
> If this is really what you want and need, well, go for it. Otherwise
> restrict it to the privileges the user really needs, e.g.
>
>
>
> GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ...
>
>
>
> Regards.
>
> Charles
>
>
>
> is working for me.
>
>
>
> I will read more and try to use it properly!
>
>
>
> Thank you very much,
>
> Best,
>
> Jacek
>
>
>
>
>
>
>
> pt., 27 lip 2018 o 17:55 Melvin Davidson <melvin6925(at)gmail(dot)com>
> napisał(a):
>
>
> *Jacek, you appear to be confused on how security works in PostgreSQL.*
>
> *Please review the options in *
>
>
>
> *https://www.postgresql.org/docs/10/static/sql-grant.html
> <https://www.postgresql.org/docs/10/static/sql-grant.html>*
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> *and read the Description that follows on that pageAs owner or superuser,
> you canGRANT CONNECT {CREATE} ON DATABASE;GRANT USAGE {CREATE} ON SCHEMA
> schema_name TO role_name;GRANT SELECT ON TABLE table_name TO
> role_name;GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO
> role_name;What you want is probably the last of these.If you are still
> confused, you should probably purchase or download PostgreSQL
> Administration EssentialsChapter 3: Users and Permissionsfrom
> https://www.packtpub.com/all?search=PostgreSQL+Administration+Essentials&availability_list%5BAvailable%5D=Available&offset=&rows=&sort=
> <https://www.packtpub.com/all?search=PostgreSQL+Administration+Essentials&availability_list%5BAvailable%5D=Available&offset=&rows=&sort=>*
>
>
>
> On Fri, Jul 27, 2018 at 11:31 AM, Charles Clavadetscher <
> clavadetscher(at)swisspug(dot)org> wrote:
>
> Hi
>
>
>
> Please notice that in this mailing list it is not customary to top post.
>
>
>
> *From:* Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com]
> *Sent:* Freitag, 27. Juli 2018 12:19
> *To:* Charles Clavadetscher <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
>
>
>
> Hi,
>
>
>
> ok i hate this.
>
>
>
> SELECT datname, datacl FROM pg_database where datname = 'AccessLog';
>
>
>
> [image: image.png]
>
> \What does it mean?
>
>
>
> The ACL (Access Control List) contains all the information about which
> role can do what on that object, in this case a database and which role
> granted the privileges.
>
>
>
> You can find detailed information in the documentation:
>
> https://www.postgresql.org/docs/10/static/sql-grant.html
>
>
>
> An ACL is an array of aclitem, which again is basically a key/value pair
> with an equal sign between the key and the value.
>
> In this context the key is the grantee, i.e. the role being granted a
> privilege.
>
> The value is the list of privileges (see list following the link above)
> granted to that role, followed by a slash (/) and the role that granted the
> privilege.
>
> The list of acronyms is a.o. in the link I gave above. Here for your
> convenience:
>
>
>
> rolename=xxxx -- privileges granted to a role
>
> =xxxx -- privileges granted to PUBLIC
>
>
>
> r -- SELECT ("read")
>
> w -- UPDATE ("write")
>
> a -- INSERT ("append")
>
> d -- DELETE
>
> D -- TRUNCATE
>
> x -- REFERENCES
>
> t -- TRIGGER
>
> X -- EXECUTE
>
> U -- USAGE
>
> C -- CREATE
>
> c -- CONNECT
>
> T -- TEMPORARY
>
> arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
>
> * -- grant option for preceding privilege
>
>
>
> /yyyy -- role that granted this privilege
>
>
>
> If the key is empty, it means that the privilege is granted to public,
> i.e. every role.
>
>
>
> Example:
>
>
>
> admin=CTc/postgres means that role postgres granted Connect (c), Create
> (C) and Temporary (T) to role admin.
>
>
>
> Notice that the granting role may differ from the one you are using, if
> you are acting as superuser:
>
>
>
> “If a superuser chooses to issue a GRANT or REVOKE command, the command is
> performed as though it were issued by the owner of the affected object. In
> particular, privileges granted via such a command will appear to have been
> granted by the object owner. (For role membership, the membership appears
> to have been granted by the containing role itself.)”
>
>
>
> If the ACL is empty (NULL) then the predefined default privileges apply.
> From the doc:
>
>
>
> “PostgreSQL grants default privileges on some types of objects to PUBLIC.
> No privileges are granted to PUBLIC by default on tables, table columns,
> sequences, foreign data wrappers, foreign servers, large objects, schemas,
> or tablespaces. For other types of objects, the default privileges granted
> to PUBLIC are as follows: CONNECT and TEMPORARY (create temporary tables)
> privileges for databases; EXECUTE privilege for functions; and USAGE
> privilege for languages and data types (including domains). The object
> owner can, of course, REVOKE both default and expressly granted privileges.
> (For maximum security, issue the REVOKE in the same transaction that
> creates the object; then there is no window in which another user can use
> the object.) Also, these initial default privilege settings can be changed
> using the ALTER DEFAULT PRIVILEGES
> <https://www.postgresql.org/docs/10/static/sql-alterdefaultprivileges.html>
> command.”
>
>
>
> I think that you should take some time to read these parts of the
> documentation. If you plan to use the role system extensively (which may
> make perfectly sense, depending on your requirement), it is worth the time.
>
>
>
> You can also have a look at these slides that I prepared a while ago for a
> course:
>
>
> http://www.artesano.ch/documents/04-publications/authorization_in_postgresql_slides_handout_pdfa.pdf
>
>
>
> 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...
>
>
>
> Unfortunately these pictures are too small to read.
>
> From your text I get that Luke was not granted privileges on the table. So
> that explains the error message.
>
>
>
> Besides, if my old eyes see it correcly, you have the same table in
> different schemas. In that case you should either schema qualify the schema
> when querying or make sure that the order of schema in the user’s
> search_path meets your requirements.
>
>
>
> https://www.postgresql.org/docs/10/static/runtime-config-client.html
>
>
>
> “search_path (string)
>
> This variable specifies the order in which schemas are searched when an
> object (table, data type, function, etc.) is referenced by a simple name
> with no schema specified. When there are objects of identical names in
> different schemas, the one found first in the search path is used. An
> object that is not in any of the schemas in the search path can only be
> referenced by specifying its containing schema with a qualified (dotted)
> name.
>
> The value for search_path must be a comma-separated list of schema names.
> Any name that is not an existing schema, or is a schema for which the user
> does not have USAGE permission, is silently ignored.
>
> If one of the list items is the special name $user, then the schema having
> the name returned by SESSION_USER is substituted, if there is such a schema
> and the user has USAGE permission for it. (If not, $user is ignored.)
>
> The system catalog schema, pg_catalog, is always searched, whether it is
> mentioned in the path or not. If it is mentioned in the path then it will
> be searched in the specified order. If pg_catalog is not in the path then
> it will be searched *before* searching any of the path items.
>
> Likewise, the current session's temporary-table schema, pg_temp_*nnn*, is
> always searched if it exists. It can be explicitly listed in the path by
> using the alias pg_temp. If it is not listed in the path then it is
> searched first (even before pg_catalog). However, the temporary schema is
> only searched for relation (table, view, sequence, etc) and data type
> names. It is never searched for function or operator names.
>
> When objects are created without specifying a particular target schema,
> they will be placed in the first valid schema named in search_path. An
> error is reported if the search path is empty.
>
> The default value for this parameter is "$user", public. This setting
> supports shared use of a database (where no users have private schemas, and
> all share use of public), private per-user schemas, and combinations of
> these. Other effects can be obtained by altering the default search path
> setting, either globally or per-user.
>
> For more information on schema handling, see Section 5.8
> <https://www.postgresql.org/docs/10/static/ddl-schemas.html>. In
> particular, the default configuration is suitable only when the database
> has a single user or a few mutually-trusting users.
>
> The current effective value of the search path can be examined via the SQL
> function current_schemas (see Section 9.25
> <https://www.postgresql.org/docs/10/static/functions-info.html>). This is
> not quite the same as examining the value of search_path, since
> current_schemas shows how the items appearing in search_path were resolved.”
>
>
>
> About creating owner of database?
>
> Can i have more then one owner? i do not think so.
>
>
>
> No, you can’t have more than one owner and it usually does not make sense.
> What you could do is grant the role of the database owner to your user. But
> be aware that this can have nasty side effects. It would be better not to
> do it.
>
>
>
> Thank you once more time for your respond and support.
>
>
>
> As I mentioned check the documentation and if you send query results,
> please send only the text instead of screenshots. It makes life lots
> easiear ;-)
>
> Regards
>
> Charles
>
>
>
> 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 :
>
>
>
>
>
> 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> 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
>
>
>
>
> --
>
>
>
> *Melvin DavidsonMaj. Database & Exploration SpecialistUniverse Exploration
> Command – UXC*
> Employment by invitation only!
>
>