CODEWITHSUNDEEP
postgresqlcentos
willmtemple
willmtemple

Reputation: 13

Why can I not set permissions on fresh install of PostgreSQL

A fresh installation of PostgreSQL 9.3 (according to the YUM Installation manual on the PostgreSQL wiki) on CentOS 6 (64-bit) will not grant permissions to any users.

I log in to the postgres user and open psql, then I create a role for my default user:

CREATE ROLE <name> WITH PASSWORD '<password>';

and then try to grant it privileges on the default postgres database:

GRANT ALL ON DATABASE postgres TO <user>;

which gives the expected output, but the user does not have any permissions on postgres. The output of \dp <user> is quizically empty as well. Additional testing shows that I cannot give any users permissions. However, when I try to drop a role that has been granted these nonexistent permissions, it says

ERROR: role "<user>" cannot be dropped because some objects depend on it
DETAIL: privileges for database postgres

I am at a loss. I did also check to make sure the postgres Linux user has the appropriate file permissions on the PostgreSQL data directory.

Upvotes: 1

Views: 263

Answers (1)

Daniel V&#233;rit&#233;
Daniel V&#233;rit&#233;

Reputation: 61706

Presumably you're expecting too much of GRANT ALL ON DATABASE postgres TO <user>;

ALL in this context means that the command is equivalent to:

GRANT CREATE,CONNECT,TEMPORARY ON DATABASE postgres TO <user>;

And the way you create the ROLE, it cannot login to any database anyway (you can check this with \du). It could if it was created with:

CREATE ROLE name WITH LOGIN PASSWORD 'pass';

or use ALTER ROLE name WITH LOGIN later on.

Starting from this, to give the user permissions to create objects in the database, other forms of GRANT should be used.

Upvotes: 1

Related Questions