Access Management question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Access Management question

chiru r

Hi All,

I have a question about access management in PostgreSQL , please help me to understand.

I am creating a role dba_admin with superuser role.
after that created a user scott and then granted dba_admin role  to user scott.


postgres=# create role dba_admin superuser;
CREATE ROLE
postgres=# create user scott password 'XXXXXX';
CREATE ROLE
postgres=# grant dba_admin to scott ;
GRANT ROLE
postgres=# \du+ scott
                   List of roles
 Role name | Attributes |  Member of  | Description
-----------+------------+-------------+-------------
 scott     |            | {dba_admin} |

However I create a object after login as scott and the table owner showing as scott.

[postgres@server~]$ psql -U scott -d postgres
postgres=> create table test(id int);
CREATE TABLE
postgres=> \dt
           List of relations
 Schema | Name | Type  |     Owner
--------+------+-------+---------------
 public | test | table | scott
 
(1 rows)


After login I set role dba_admin to scott and created a table,then the table owner showing as dba_admin role.
Is there any reason, why it is showing roles name as owner of table instead of user?
postgres=> set role to dba_admin;
SET
postgres=# create table test2(id int);
CREATE TABLE
postgres=# \dt
           List of relations
 Schema | Name  | Type  |     Owner
--------+-------+-------+---------------
 public | test  | table | scott
 public | test2 | table | dba_admin
 (2 rows)

Why it is not allowed to GRANT SUPERUSER/CREATEDB etc roles Directly to user (using GRANT ,not ALTER USER) ?


Thanks,
Chiru

Reply | Threaded
Open this post in threaded view
|

Re: Access Management question

John R Pierce
On 5/30/2017 2:06 PM, chiru r wrote:
Is there any reason, why it is showing roles name as owner of table instead of user?

'user' is a synonym for 'role' with login permission.

    CREATE USER fred;

is exactly the same as...

    CREATE ROLE fred WITH LOGIN;

when you SET ROLE rolename;   its virtually the same as logging in as rolename

Why it is not allowed to GRANT SUPERUSER/CREATEDB etc roles Directly to user (using GRANT ,not ALTER USER) ?


GRANT is used to grant object related permissions or to grant role membership, its not a 'role' nor do roles inherit special attributes like SUPERUSER, CREATEDB.    



-- 
john r pierce, recycling bits in santa cruz