Re: Read only to schema
Jacek,
you appear to be confused on how security works in PostgreSQL.Please review the options in
and read the Description that follows on that page
As owner or superuser, you can
GRANT 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 Essentials
Chapter 3: Users and Permissions
from 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@swisspug.org> wrote:Hi
Please notice that in this mailing list it is not customary to top post.
From: Łukasz Jarych [mailto:jaryszek@gmail.com]
Sent: Freitag, 27. Juli 2018 12:19
To: Charles Clavadetscher <clavadetscher@swisspug.org>; pgsql-general@postgresql.org >> PG-General Mailing List <pgsql-general@postgresql.org>
Subject: Re: Read only to schema
Hi,
ok i hate this.
SELECT datname, datacl FROM pg_database where datname = 'AccessLog';
\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 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 :
Hmm this is a result from your second query:
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. 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). 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@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 <clavadetscher@swisspug.org> napisał(a):
An addition.
From: Charles Clavadetscher [mailto:clavadetscher@swisspug.org]
Sent: Donnerstag, 26. Juli 2018 16:57
To: 'Łukasz Jarych' <jaryszek@gmail.com>
Cc: pgsql-general@postgresql.org
Subject: RE: Read only to schema
Hello
From: Łukasz Jarych [mailto:jaryszek@gmail.com]
Sent: Mittwoch, 25. Juli 2018 13:50
To: Charles Clavadetscher <clavadetscher@swisspug.org>
Cc: pgsql-general@postgresql.org >> PG-General Mailing List <pgsql-general@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 <clavadetscher@swisspug.org> napisał(a):
Hello Jacek
From: Łukasz Jarych [mailto:jaryszek@gmail.com]
Sent: Samstag, 14. Juli 2018 11:55
To: clavadetscher@swisspug.org; pgsql-general@postgresql.org >> PG-General Mailing List <pgsql-general@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 <clavadetscher@swisspug.org> napisał(a):
Hello
From: Łukasz Jarych [mailto:jaryszek@gmail.com]
Sent: Freitag, 13. Juli 2018 16:39
To: pgsql-general@postgresql.org >> PG-General Mailing List <pgsql-general@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 <jaryszek@gmail.com> napisał(a):
Maybe read-only view?
Best,
Jacek
pt., 13 lip 2018 o 07:00 Łukasz Jarych <jaryszek@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 Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!
Вложения
В списке pgsql-general по дате отправления: