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
