Thread: [GENERAL] Access Management question
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.
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} |
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?
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)
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
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