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;*

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 Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>