Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table permessions

Table permessions

From
salah jubeh
Date:
Hello,

I have create a table from another table such as

CREATE TABLE tmp_XXX AS SELECT * FROM XXX; 


The  tmp_XXX  tables has no permissions assigned to it and I  want to assign it with the same owner and access privileges  of XXX  table. I had a look on pg catalog tables  http://www.postgresql.org/docs/8.4/static/catalogs.html to create a stored procedure to do this job for me. I have found some tables which might be useful to get the original table iformation. For example, I can use  pg_table to get the owner of the original table.  Also, I have found pg_roles. However, I was not able to find where the access privileges are stored.

Is there a better way to do this task than extracting the access privileges from pg catalog ?  If not, where I can find the access privileges information ?


Thanks in advance



 


Re: Table permessions - solved

From
salah jubeh
Date:
I found this view

information_schema.table_privileges


Regards

 


From: salah jubeh <s_jubeh@yahoo.com>
To: pgsql <pgsql-general@postgresql.org>
Sent: Wednesday, January 18, 2012 2:48 PM
Subject: [GENERAL] Table permessions

Hello,

I have create a table from another table such as

CREATE TABLE tmp_XXX AS SELECT * FROM XXX; 


The  tmp_XXX  tables has no permissions assigned to it and I  want to assign it with the same owner and access privileges  of XXX  table. I had a look on pg catalog tables  http://www.postgresql.org/docs/8.4/static/catalogs.html to create a stored procedure to do this job for me. I have found some tables which might be useful to get the original table iformation. For example, I can use  pg_table to get the owner of the original table.  Also, I have found pg_roles. However, I was not able to find where the access privileges are stored.

Is there a better way to do this task than extracting the access privileges from pg catalog ?  If not, where I can find the access privileges information ?


Thanks in advance



 




Re: Table permissions

From
"A.M."
Date:
On Jan 18, 2012, at 8:48 AM, salah jubeh wrote:

> Hello,
>
> I have create a table from another table such as
>
> CREATE TABLE tmp_XXX AS SELECT * FROM XXX;
>
>
> The  tmp_XXX  tables has no permissions assigned to it and I  want to assign it with the same owner and access
privileges of XXX  table. I had a look on pg catalog tables  http://www.postgresql.org/docs/8.4/static/catalogs.html to
createa stored procedure to do this job for me. I have found some tables which might be useful to get the original
tableiformation. For example, I can use  pg_table to get the owner of the original table.  Also, I have found pg_roles.
However,I was not able to find where the access privileges are stored. 
>
> Is there a better way to do this task than extracting the access privileges from pg catalog ?  If not, where I can
findthe access privileges information ? 
>

You are looking for pg_catalog.pg_class.relacl. Just copy that from the original table to the duplicate (and perhaps
relowner,depending on your situation), and you will have duplicate permissions. 

Cheers,
M

Re: Table permissions

From
salah jubeh
Date:
Hello,

Thanks for the info, I have already solved this by writing the following function. Also, i think it is better than changing the schema tables directly

Regards
 

CREATE OR REPLACE FUNCTION grant_permissions  (org_tbl TEXT  , new_tbl TEXT , change_owner BOOLEAN) RETURNS VOID
AS
$$
    DECLARE
        tblOwner      text  := '';
        roleName      text  := '';
        privilegeType      text  := '';
        grantSql       text  := '';
    BEGIN
        -- Some checks
        IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = $1) THEN
            RAISE EXCEPTION 'The relation % does not exists', $1;
        ELSIF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = $2) THEN
            RAISE EXCEPTION 'The relation % does not exists', $2;
        ELSIF  NOT EXISTS (SELECT 1 FROM information_schema.table_privileges WHERE table_name = $1) THEN
            RAISE EXCEPTION 'No privileges assigned to the relation %', $1;
        END IF;

        -- Change the table owner
        IF  (change_owner) THEN
            SELECT tableowner INTO  tblOwner  FROM pg_tables;
            grantSql = 'ALTER TABLE ' ||  quote_ident ($2) || ' OWNER TO ' || tblOwner || E';\n' ;
        END IF;

        -- Grant the privileges
        FOR roleName IN SELECT DISTINCT grantee FROM information_schema.table_privileges WHERE table_name = $1 LOOP
            -- 6 = DELETE, INSERT, UPDATE, SELECT, TRIGGER, REFERENCE
            IF (SELECT count(*) FROM information_schema.table_privileges WHERE table_name = $1 AND grantee = roleName)::INTEGER = 6 THEN
                grantSql = grantSql || 'GRANT SELECT ON TABLE ' ||quote_ident ($2) || ' TO ' || roleName ||E';\n' ;
            END IF;

            FOR privilegeType IN SELECT privilege_type FROM information_schema.table_privileges WHERE table_name = $1 AND grantee = roleName LOOP
                grantSql = grantSql || 'GRANT ' || privilegeType ||' ON TABLE ' || quote_ident ($2)  ||' TO '|| roleName || E';\n' ;
            END LOOP;
             
        END  LOOP;

        --Execute ALL
        EXECUTE grantSQL;
    END
$$ LANGUAGE 'plpgsql' ;



 

From: A.M. <agentm@themactionfaction.com>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Wednesday, January 18, 2012 5:44 PM
Subject: Re: [GENERAL] Table permissions


On Jan 18, 2012, at 8:48 AM, salah jubeh wrote:

> Hello,
>
> I have create a table from another table such as
>
> CREATE TABLE tmp_XXX AS SELECT * FROM XXX; 
>
>
> The  tmp_XXX  tables has no permissions assigned to it and I  want to assign it with the same owner and access privileges  of XXX  table. I had a look on pg catalog tables  http://www.postgresql.org/docs/8.4/static/catalogs.html to create a stored procedure to do this job for me. I have found some tables which might be useful to get the original table iformation. For example, I can use  pg_table to get the owner of the original table.  Also, I have found pg_roles. However, I was not able to find where the access privileges are stored.
>
> Is there a better way to do this task than extracting the access privileges from pg catalog ?  If not, where I can find the access privileges information ?
>

You are looking for pg_catalog.pg_class.relacl. Just copy that from the original table to the duplicate (and perhaps relowner, depending on your situation), and you will have duplicate permissions.

Cheers,
M
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Re: Table permissions

From
"A.M."
Date:
On Jan 18, 2012, at 12:25 PM, salah jubeh wrote:

> Hello,
>
> Thanks for the info, I have already solved this by writing the following function. Also, i think it is better than
changingthe schema tables directly  
>
> Regards
>

It doesn't look like the procedure handles grant options (WITH GRANT OPTION), so the output ACL will not be the same as
theinput ACL. 

Cheers,
M