pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSetting up a database with minimum access rights
3+ messages / 2 participants
[nested] [flat]
* Setting up a database with minimum access rights
@ 2020-04-26 16:58 Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Bo Victor Thomsen @ 2020-04-26 16:58 UTC (permalink / raw)
To: pgsql-admin
Dear all -
I have a problem with the setup of access rights based on schemas.
The objective is to have a schema with default access rights to tables
assigned to the schema. And to strip the "public" user group for all
rights.
So if you create a table in a certain schema ("data_schema") it will be
generated with the rights defined using a "ALTER DEFAULT ..." command to
the schema. But I can't get it to work...
Here is the pertinent commands used:
-- Remove all access rights for "public"
REVOKE ALL ON SCHEMA public, data_schema FROM PUBLIC;
REVOKE ALL ON DATABASE greenadm FROM PUBLIC;
-- Create necessary ressource role
CREATE ROLE greenadm_read;
-- Access to database "greenadm" for role "greenadm_read"
GRANT CONNECT, TEMP ON DATABASE greenadm TO greenadm_read;
-- Access to schema for the ressource role
GRANT USAGE ON SCHEMA public, data_schema TO greenadm_read;
-- Default read access rights for ressource role
ALTER DEFAULT PRIVILEGES IN SCHEMA public, data_schema GRANT SELECT ON
TABLES TO greenadm_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, data_schema GRANT SELECT ON
SEQUENCES TO greenadm_read;
ALTER DEFAULT PRIVILEGES IN SCHEMA public, data_schema GRANT EXECUTE ON
FUNCTIONS TO greenadm_read;
-- And a login user...
CREATE ROLE loki WITH LOGIN PASSWORD 'sneaky' VALID UNTIL '2021-01-01'
INHERIT;
GRANT greenadm_read TO loki;
After execution of the above commands, I create a table in schema
"data_schema" using the "postgres" superuser. But I can't access the
created table with user "loki"
However, if I execute these command after the table creation:
GRANT SELECT ON ALL TABLES IN SCHEMA public, data_schema TO
greenadm_read;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public, data_schema TO
greenadm_read;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public, data_schema TO
greenadm_read;
it works as expected with read acces to user "loki". it seem that the
problem is related to the "ALTER DEFAULT..." commands
I simply can't phantom what I'm doing wrong. (it's probably some newbie
error :-/ )
--
Med venlig hilsen / Kind regards
Bo Victor Thomsen
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Setting up a database with minimum access rights
@ 2020-04-26 17:37 Jeff Janes <jeff.janes@gmail.com>
parent: Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jeff Janes @ 2020-04-26 17:37 UTC (permalink / raw)
To: Bo Victor Thomsen <bo.victor.thomsen@gmail.com>; +Cc: pgsql-admin
On Sun, Apr 26, 2020 at 12:57 PM Bo Victor Thomsen <
bo.victor.thomsen@gmail.com> wrote:
...
> After execution of the above commands, I create a table in schema
> "data_schema" using the "postgres" superuser. But I can't access the
> created table with user "loki"
>
Were all the above steps executed as "postgres", or was just the CREATE
TABLE statement done as that user?
If it is all done as "postgres" superuser then it works for me. Your
example ALTER DEFAULT PRIVILEGES without specifying the FOR ROLE only
applies to objects created by the same user who executed the ALTER DEFAULT
PRIVILEGES.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Setting up a database with minimum access rights
@ 2020-04-26 19:36 Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Bo Victor Thomsen @ 2020-04-26 19:36 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-admin
Hi Jeff -
Spot on !! (Right hand reaching for the Postgres manual, left hand
searching for "Brown paper bag to pull over head" )
I was executing the script with user "postgres" and creating the table
with another user "greenadm_adm".
With the missing qualifier "FOR USER.." in the "ADD DEFAULT
PRIVILEGES.. " command, the command only works for tables created by
user "postgres".
Using the same user for both scripting and creating tables works.
I owe you a large cold beer (or whatever beverage you prefer) If we ever
meet in person :-)
--
Med venlig hilsen / Kind regards
Bo Victor Thomsen
Den 26-04-2020 kl. 19:37 skrev Jeff Janes:
> On Sun, Apr 26, 2020 at 12:57 PM Bo Victor Thomsen
> <bo.victor.thomsen@gmail.com <mailto:bo.victor.thomsen@gmail.com>> wrote:
>
> ...
>
> After execution of the above commands, I create a table in schema
> "data_schema" using the "postgres" superuser. But I can't access the
> created table with user "loki"
>
>
> Were all the above steps executed as "postgres", or was just the
> CREATE TABLE statement done as that user?
>
> If it is all done as "postgres" superuser then it works for me. Your
> example ALTER DEFAULT PRIVILEGES without specifying the FOR ROLE only
> applies to objects created by the same user who executed the ALTER
> DEFAULT PRIVILEGES.
>
> Cheers,
>
> Jeff
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-04-26 19:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-26 16:58 Setting up a database with minimum access rights Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
2020-04-26 17:37 ` Jeff Janes <jeff.janes@gmail.com>
2020-04-26 19:36 ` Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox