Thank you Charles once again ! And best wishes, Jacek
sob., 28 lip 2018 o 15:58 Charles Clavadetscher <clavadetsc...@swisspug.org> napisał(a): > Hello Jakek > > > > *From:* Łukasz Jarych [mailto:jarys...@gmail.com] > *Sent:* Samstag, 28. Juli 2018 14:13 > *To:* Melvin Davidson <melvin6...@gmail.com>; pgsql-gene...@postgresql.org > >> PG-General Mailing List <pgsql-gene...@postgresql.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 <melvin6...@gmail.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 < > clavadetsc...@swisspug.org> wrote: > > Hi > > > > Please notice that in this mailing list it is not customary to top post. > > > > *From:* Łukasz Jarych [mailto:jarys...@gmail.com] > *Sent:* Freitag, 27. Juli 2018 12:19 > *To:* Charles Clavadetscher <clavadetsc...@swisspug.org>; > pgsql-gene...@postgresql.org >> PG-General Mailing List < > pgsql-gene...@postgresql.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 <jarys...@gmail.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 < > clavadetsc...@swisspug.org> napisał(a): > > An addition. > > > > *From:* Charles Clavadetscher [mailto:clavadetsc...@swisspug.org] > *Sent:* Donnerstag, 26. Juli 2018 16:57 > *To:* 'Łukasz Jarych' <jarys...@gmail.com> > *Cc:* pgsql-gene...@postgresql.org > *Subject:* RE: Read only to schema > > > > Hello > > > > *From:* Łukasz Jarych [mailto:jarys...@gmail.com <jarys...@gmail.com>] > *Sent:* Mittwoch, 25. Juli 2018 13:50 > *To:* Charles Clavadetscher <clavadetsc...@swisspug.org> > *Cc:* pgsql-gene...@postgresql.org >> PG-General Mailing List < > pgsql-gene...@postgresql.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 < > clavadetsc...@swisspug.org> napisał(a): > > Hello Jacek > > > > *From:* Łukasz Jarych [mailto:jarys...@gmail.com] > *Sent:* Samstag, 14. Juli 2018 11:55 > *To:* clavadetsc...@swisspug.org; pgsql-gene...@postgresql.org >> > PG-General Mailing List <pgsql-gene...@postgresql.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 < > clavadetsc...@swisspug.org> napisał(a): > > Hello > > > > *From:* Łukasz Jarych [mailto:jarys...@gmail.com] > *Sent:* Freitag, 13. Juli 2018 16:39 > *To:* pgsql-gene...@postgresql.org >> PG-General Mailing List < > pgsql-gene...@postgresql.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@db.localhost=> SELECT SESSION_USER, CURRENT_USER; > > session_user | current_user > > --------------+-------------- > > jaryszek | jaryszek > > > > jaryszek@db.localhost=> CREATE TABLE public.test (a INTEGER); > > CREATE TABLE > > jaryszek@db.localhost=> INSERT INTO public.test VALUES (1); > > INSERT 0 1 > > jaryszek@db.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@db.localhost=> SET ROLE readonly_user ; > > SET > > jaryszek@db.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@db.localhost=> SELECT * FROM public.test; > > a > > --- > > 1 > > (1 row) > > > > jaryszek@db.localhost=> INSERT INTO public.test VALUES (2); > > ERROR: permission denied for relation test > > > > jaryszek@db.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@db.localhost=> RESET ROLE; > > RESET > > jaryszek@db.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 <jarys...@gmail.com> napisał(a): > > Maybe read-only view? > > > > Best, > > Jacek > > > > pt., 13 lip 2018 o 07:00 Łukasz Jarych <jarys...@gmail.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! > >
