Postgresql – Error when assigning a role to user

permissionspostgresqlrolesystem-databasestable

I have created a role called "role1" with these steps :

postgres=# CREATE ROLE role1;
CREATE ROLE
postgres=# GRANT CONNECT ON DATABASE paybills TO role1;
GRANT
postgres=# \c paybills
You are now connected to database "paybills".
paybills=# GRANT SELECT, UPDATE, INSERT, DELETE ON myeg_users TO role1;
GRANT

The steps above are all successful.

However, when I run the command below :

paybills=# GRANT role1 TO surentran;
ERROR:  role "surentran" does not exist

As you can see, an error is thrown. I am in the db called "paybills", and the user details are stored in a table called "myeg_users" within this database.

How do I add all the users in the "myeg_users" table into the role "role1"?

Best Answer

You can only GRANT to a PostgreSQL role. If you have created your own role system at the application level, storing the data about them in the database, PostgreSQL has no idea that you did that and certainly isn't going to grant PostgreSQL privileges to these roles.

You either need to convert the users listed in "myeg_users" into PostgreSQL users/roles, or you need your app server to connect as PostgreSQL user "role1" and then to voluntarily do only those things the app-side role it is implementing is allowed to do.

Related Solutions

Oracle: how to set only some roles as not default for a user (no GUI)

You have to use the GRANT command to grant roles (with admin option if required), then use ALTER USER to set roles to default (or not). So, the order of commands is,

grant roles (with admin option as needed)
alter user to set all roles to non-default
alter user to set only required roles to default.

A way to generate sql using sql is as follows, you can run this sql in instanceA, and actually execute the generated script in instanceB by replacing username if required. Make sure to test though,

with x as (select 'USER1' as usr from dual)
select cmd from (
select 1 as ord, 'grant ' || granted_role || ' to ' || grantee || case when admin_option = 'YES' then ' with admin option' end  || ';' as cmd
from dba_role_privs, x where grantee = x.usr 
union all
select distinct 2 as ord, 'alter user ' || grantee || ' default role none;' as cmd from dba_role_privs, x where grantee  = x.usr
union all
select 3 as ord, 'alter user ' || grantee || ' default role ' || granted_role || ';' as cmd from dba_role_privs, x where grantee  = x.usr and default_role = 'YES')
order by ord

Postgresql – How to create a role that cannot create or alter a table

The privilege to create tables is granted to new roles automatically. You need to REVOKE the role's CREATE privilege on the schema (not the database):

REVOKE CREATE ON SCHEMA myschema FROM manager;

Per documentation on GRANT:

CREATE

For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this privilege for the containing schema.

For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that have the tablespace as their default tablespace. (Note that revoking this privilege will not alter the placement of existing objects.)

And REVOKE all direct privileges any role might have in respective schemas. By default, plain roles only have privileges for the schema public and schemas they create themselves.

Best Answer

Related Solutions

Oracle: how to set only some roles as not default for a user (no GUI)

Postgresql – How to create a role that cannot create or alter a table

Related Question