Postgresql database superuser

Question

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?

user1331131 · Accepted Answer · 2014-03-11 19:12:17Z

4

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;

answered Mar 11, 2014 at 19:12

user1331131

4277 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Wolfe Over a year ago

Why did you need to grant ALL PRIVILEGES on tables/seqs in schema BAR after GRANT ALL ON SCHEMA ?

DrColossos · Accepted Answer · 2014-03-06 13:54:12Z

1

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...

answered Mar 6, 2014 at 13:54

DrColossos

13.1k3 gold badges49 silver badges68 bronze badges

1 Comment

user1331131 Over a year ago

There are a lot of schemas in the database, can it really not be done at the database level

diek · Accepted Answer · 2014-03-17 04:02:14Z

0

Here is an example:

$ sudo su - postgres
postgres@derrick:~$ createuser -P
Enter name of role to add: web_app
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
postgres@derrick:~$

postgres@derrick:~$ createdb --owner web_app dbTest
postgres@derrick:~$ logout

answered Mar 17, 2014 at 4:02

diek

6957 silver badges18 bronze badges

3 Comments

user1331131 Over a year ago

This does not answer the specific permissions requested above. It simply gives ownership to user web_app

diek Over a year ago

In hindsight you are correct. I do have ask, if you have time. What is the scenario for such a complicated setup? How are the databases accessed, via the web?

user1331131 Over a year ago

It is for a web facing app. I always give the minimum access required rather than the max, these requirements represent the base set.

Collectives™ on Stack Overflow

Postgresql database superuser

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related