I am attempting to secure a database made up of multiple schemas as follows:-
-public
-foo
-bar
-foobar
I want to create a user who can access any schema for read, can create tables in bar and can inert/update/delete in foo,bar and foobar
I would prefer to create user as a database superuser and then remove privileges as required.
I thought:-
CREATE USER test_superuser;
GRANT ALL on DATABASE test to test_superuser;
Would do this, but after these commands test_superuser cannot access the schema.
How can I create a user that has the permissions of postgres superuser but only on a named database?
Turned out this needed a lot of tinkering to achieve:-
CREATE ROLE test_database_superuser
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL '2020-03-06 00:00:00';
CREATE ROLE test_user LOGIN
ENCRYPTED PASSWORD 'md52b250919b406b707999fffb2b9f673fb'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL '2020-03-06 00:00:00';
GRANT test_database_superuser TO test_user;
--DATABASE LEVEL PRIVELEGES
GRANT ALL PRIVILEGES ON DATABASE test to test_database_superuser;
--SCHEMA LEVEL
GRANT ALL ON SCHEMA bar TO GROUP test_database_superuser;
GRANT USAGE ON SCHEMA foo TO GROUP test_database_superuser;
GRANT USAGE ON SCHEMA foobar TO GROUP test_database_superuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO GROUP test_database_superuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA foo TO GROUP test_database_superuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA bar TO GROUP test_database_superuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA bar TO GROUP test_database_superuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foobar TO GROUP test_database_superuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA foobar TO GROUP test_database_superuser;
--PUBLIC
GRANT USAGE ON SCHEMA public TO GROUP test_database_superuser;
Allow usage of the schema
GRANT { { CREATE | USAGE } [,...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]
Something like:
GRANT USAGE ON SCHEMA your_schame TO test_superuser;
By the way, this is not a "super user", just a user with lots of permissions...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With