Display all tables in a database with user permissions

Kevin Soll
Kevin Soll

The purpose of this article is to show user permissions on objects, including tables, views, and materialized views in a database.

If invoked with a single parameter of 'h' or 'H' generates information on the execution.

  1. Create the procedure included below in each database where you want to get this information.

  2. Call the procedure to generate the output, using on of the following two methods:

  3. Call via psql and review output in same session:

psql -d DBNAME -U USERID 
CALL all_table_view_permissions(); 
SELECT * FROM _all_permissions; 
\q 
  1. Batch via psql and save output to a file
psql -d DBNAME -U USERID -c 'CALL all_table_view_permissions(); SELECT * FROM _all_permissions;' -o REPORT.out 

Sample Output:

| Database | Schema | Name | User | Permissions |
| testdb | | | | **TABLES** | 
| testdb | | | | ** TABLES ** | 
| | | | | | 
| | public | a1 | enterprisedb | ** SUPERUSER/OWNER ** | 
| | public | a1 | usera | select insert update delete | 
| | public | a1 | userb | select insert update delete | 
| | public | first | enterprisedb | ** SUPERUSER ONLY ** | 
| | public | first | testuser | ** OWNER ONLY ** | 
| | sch1 | first | enterprisedb | ** SUPERUSER ONLY ** | 
| | sch1 | first | testuser | ** OWNER ONLY ** | 
| | | | | | 
| | | | | | 
| testdb | | | | ** VIEWS ** | 
| | | | | | 
| | sch1 | first_view | enterprisedb | ** SUPERUSER ** | 
| | sch1 | first_view | testuser | ** OWNER ** | 
| | sch1 | first_view | usera | select | 
| | sch1 | first_view | userc | select insert update delete | 
| | sch1 | second_view | enterprisedb | ** SUPERUSER/OWNER ONLY ** | 
| | | | | | 
| | | | | | 
| testdb | | | | ** MATERIALIZED VIEWS ** | 
| | | | | |
| | sch1 | first_mv | enterprisedb | ** SUPERUSER ** | 
| | sch1 | first_mv | userb | select | 
| | sch1 | first_mv | userc | select insert update delete | 

Procedure

CREATE OR REPLACE procedure all_table_view_permissions(describe_it char(1) default 'n')
language plpgsql
as $$
--
-- procedure creates a temporary table _all_permissions
-- procedure takes a single parameter 
-- describe_it char(1) defaults to n
-- if set to 'h' will output an explanation of the procedure
--
-- if the output table exists, it is dropped and recreated
-- For all tables/views/materialized views in the current database
-- generate the following information and place it in the table
-- database, user, tablename, permissions (select/insert/update/delete)
--
DECLARE
rowcount integer=0;
current_db varchar(25);
BEGIN

-- Did user pass a parmeter of 'h' for help? if yes, then explain procedure
if describe_it = 'h' or describe_it = 'H'
then
raise notice ' ';
raise notice ' procedure all_permissions (describe_it)';
raise notice ' ';
raise notice ' optional parameter describe_it, if specified as ''H'' or ''h'' will display this information';
raise notice ' ';
raise notice ' This procedure generates an output table listing all tables in the database ';
raise notice ' along with each user''s permissions - select/insert/update/delete ';
raise notice ' If Superuser is Also the Owner, that will be displayed';
raise notice ' If Only Superuser and/or Owner has permissions on the object ';
raise notice ' (no user permissions) then ONLY will also show up under permissions ';
raise notice ' ';
raise notice 'sample output:';
raise notice 'select * from _all_permissions';
raise notice ' Database | Schema | Name | User | Permissions ';
raise notice ' testdb | | | | ** TABLES **';
raise notice ' | | | | ';
raise notice ' | public | a1 | enterprisedb | ** SUPERUSER/OWNER **';
raise notice ' | public | a1 | usera | select insert update delete ';
raise notice ' | public | a1 | userb | select insert update delete ';
raise notice ' | sch1 | first | enterprisedb | ** SUPERUSER ONLY **';
raise notice ' | sch1 | first | testuser | ** OWNER ONLY **';
raise notice ' | | | | ';
raise notice ' | | | | ';
raise notice ' testdb | | | | ** VIEWS **';
raise notice ' | | | | ';
raise notice ' | sch1 | first_view | enterprisedb | ** SUPERUSER **';
raise notice ' | sch1 | first_view | testuser | ** OWNER **';
raise notice ' | sch1 | first_view | usera | select '; 
raise notice ' | sch1 | first_view | userc | select insert update delete ';
raise notice ' | sch1 | second_view | enterprisedb | ** SUPERUSER/OWNER ONLY *';
raise notice ' ';
return;
end if;



SELECT current_database INTO current_db;
raise notice 'Generating temporary table _all_permissions with a list of all table/views permissions in database % ', current_db;
raise notice ' The table has following columns: database, user, tablename, permissions';
raise notice ' ';

-- if output table exist drop it
rowcount:= 0;
SELECT count(*) INTO rowcount FROM pg_catalog.pg_tables
WHERE tablename = '_all_permissions' and tableowner = current_user ;
IF rowcount <> 0
THEN drop table _all_permissions;
END IF;

--create temporary tables used by procedure 

CREATE temporary table _all_permissions 
("Database" varchar(25), "Schema" varchar(25), "Name" varchar(25), "User" varchar(15), "Permissions" varchar(30));
CREATE temporary table _atp_non_super
(database varchar(25), schemaname varchar(25), tablename varchar(25), user varchar(15), permissions varchar(30));
CREATE temporary table _atp_super
(database varchar(25), schemaname varchar(25), tablename varchar(25), user varchar(15), permissions varchar(30));
CREATE temporary table _atp_owner
(database varchar(25), schemaname varchar(25), tablename varchar(25), user varchar(15), permissions varchar(30));

CREATE temporary table _show_owner (schemaname varchar(125), tablename varchar(125), owner varchar(125));

INSERT INTO _show_owner 
SELECT substring(schemaname,1,125), substring(tablename,1,125), substring(tableowner,1,125) 
FROM pg_catalog.pg_tables
WHERE schemaname not in ('pg_catalog','sys','information_schema') and schemaname not like '%pg_temp%';


--get list of tables with users that are NOT superusers
--need to check permissions for these users
--
WITH t AS 
( SELECT schemaname,tablename as tablename, usename 
FROM pg_catalog.pg_tables as tablename, pg_user where 
schemaname NOT IN ('information_schema','pg_catalog','sys') 
AND schemaname NOT LIKE 'pg_temp%'
AND pg_user.usesuper != 't'
AND pg_user.usename != tableowner
)
INSERT INTO _atp_non_super
(SELECT current_database() as DATABASE, schemaname AS schemaname, 
tablename as TABLENAME, usename,
(SELECT selparm || insparm || updparm || delparm as permissions FROM 
(SELECT 
CASE WHEN (SELECT has_table_privilege(usename, schemaname ||'.' ||tablename, 'select') = 't' ) 
THEN 'select ' 
ELSE ' '
END selparm,
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||tablename, 'insert') = 't' ) 
THEN 'insert '
ELSE ' '
END insparm,
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||tablename, 'update') = 't' ) 
THEN 'update '
ELSE ' '
END updparm,
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||tablename, 'delete') = 't' ) 
THEN 'delete '
else ' '
END delparm
) -- inner select case statements
) -- outer select concat 
FROM t ORDER BY 1,2,3); 


--get list of tables with users superusers do not need to check permissions for superusers
--for these users, permissions initialized to null
--will be set later
--
WITH t as 
( SELECT schemaname,tablename as tablename, usename 
FROM pg_catalog.pg_tables as tablename, pg_user where 
schemaname NOT IN ('information_schema','pg_catalog','sys') 
AND schemaname NOT LIKE 'pg_temp%'
AND pg_user.usesuper = 't'
)
INSERT INTO _atp_super
(SELECT current_database() as DATABASE, schemaname as schemaname,
tablename , usename, NULL 
FROM t order by 1,2,3); 
--
--get list of tables with owners
--
WITH t as 
( SELECT schemaname,tablename as tablename, tableowner
FROM pg_catalog.pg_tables as tablename, pg_user where 
schemaname NOT IN ('information_schema','pg_catalog','sys') 
AND schemaname NOT LIKE 'pg_temp%'
)
INSERT INTO _atp_owner
(SELECT current_database() as DATABASE, schemaname as schemaname,
tablename , tableowner, NULL 
FROM t ORDER BY 1,2,3); 
--
-- remove tables entries from user list where no permissions are assigned
-- 
DELETE FROM _atp_non_super
WHERE trim(both ' ' from permissions) = '';

-- now deal with superuser 

CREATE temporary table _super_owner as SELECT * FROM _atp_super LIMIT 0;
INSERT INTO _super_owner
SELECT super.database,super.schemaname, super.tablename, super.user, super.permissions
FROM _atp_super super
WHERE EXISTS
(SELECT 1 FROM _show_owner show
WHERE super.tablename = show.tablename
AND super.schemaname = show.schemaname
AND super.user = show.owner);

DELETE FROM _atp_owner owner
WHERE EXISTS
(SELECT 1 FROM _super_owner so
WHERE owner.schemaname = so.schemaname
AND owner.tablename = so.tablename
AND owner.user = so.user);

DELETE FROM _atp_super super
WHERE EXISTS
(SELECT 1 FROM _super_owner so
WHERE super.schemaname = so.schemaname
AND super.tablename = so.tablename
AND super.user = so.user);

CREATE temporary table _t3
as 
SELECT _atps.schemaname, _atps.tablename 
FROM _atp_super _atps
WHERE EXISTS
(SELECT 1 FROM _atp_non_super _atpns
WHERE _atps.schemaname = _atpns.schemaname
AND _atps.tablename = _atpns.tablename
);

UPDATE _atp_super _atps
SET permissions = '** SUPERUSER **'
FROM _t3 t3
WHERE t3.tablename = _atps.tablename 
AND t3.schemaname = _atps.schemaname;

UPDATE _atp_super _atps 
SET permissions = '** SUPERUSER ONLY **'
WHERE permissions is null;
--
-- now owner
--
TRUNCATE table _t3;
INSERT into _t3
SELECT _atpo.schemaname, _atpo.tablename 
FROM _atp_owner _atpo
WHERE EXISTS
(SELECT 1 FROM _atp_non_super _atpns
WHERE _atpo.schemaname = _atpns.schemaname
AND _atpo.tablename = _atpns.tablename
);

UPDATE _atp_owner _atpo
SET permissions = '** OWNER **'
FROM _t3 t3
WHERE t3.tablename = _atpo.tablename 
AND t3.schemaname = _atpo.schemaname;

UPDATE _atp_owner _atpo 
SET permissions = '** OWNER ONLY **'
WHERE permissions is NULL;
--
--
TRUNCATE table _t3;
INSERT INTO _t3
SELECT so.schemaname, so.tablename 
FROM _super_owner so
WHERE EXISTS
(SELECT 1 FROM _atp_non_super _atpns
WHERE so.schemaname = _atpns.schemaname
AND so.tablename = _atpns.tablename
);

UPDATE _super_owner so
SET permissions = '** SUPERUSER/OWNER **'
FROM _t3 t3
WHERE t3.tablename = so.tablename 
AND t3.schemaname = so.schemaname;
--
-- by default any users with permissions null 
-- have no uer permissions on them
-- st to superuser only
--
UPDATE _super_owner
SET permissions = '** SUPERUSER/OWNER ONLY **'
WHERE permissions is NULL;
--
-- insert data into the output table
--
-- sequence field used to order data for inserting into output 

ALTER TABLE _atp_non_super ADD seqnum int default 4;
ALTER TABLE _atp_owner ADD seqnum int default 3;
ALTER TABLE _atp_super ADD seqnum int default 2;
ALTER TABLE _super_owner ADD seqnum int default 1;

CREATE table _dbonly as SELECT * FROM _atp_non_super limit 1;
UPDATE _dbonly
SET schemaname= ' ', tablename = ' ', user = ' ', permissions = ' ',seqnum = 0;

UPDATE _atp_non_super set database = ' ';
UPDATE _atp_owner set database = ' ';
UPDATE _atp_owner set database = ' ';
UPDATE _atp_super set database = ' ';
UPDATE _super_owner set database = ' ';

INSERT INTO _all_permissions
SELECT database, ' ', ' ', ' ', '** TABLES **' FROM _dbonly;

INSERT INTO _all_permissions VALUES (' ', ' ', ' ', ' ', ' ');


INSERT INTO _all_permissions
SELECT a.database, a.schemaname, a.tablename,a.user, a.permissions
FROM 
(
SELECT * FROM _atp_non_super
UNION
SELECT * FROM _atp_owner
UNION
SELECT * FROM _atp_super
UNION
SELECT * FROM _super_owner
ORDER BY database desc, schemaname, tablename, seqnum, user) a;
--
--
-- now generate view output
--
TRUNCATE table _atp_non_super;
TRUNCATE table _atp_super;
TRUNCATE table _atp_owner;
TRUNCATE table _show_owner;

INSERT INTO _show_owner
SELECT substring(schemaname,1,125), substring(viewname,1,125), substring(viewowner,1,125) 
FROM pg_catalog.pg_views
WHERE schemaname NOT IN ('pg_catalog','sys','information_schema') 
AND schemaname NOT LIKE '%pg_temp%';


--get list of views with users (not superusers) and not the view owner
--need to check permissions for these users
WITH t as 
( SELECT schemaname, viewname as viewname, usename 
FROM pg_catalog.pg_views views, pg_user where 
schemaname NOT IN ('information_schema','pg_catalog','sys') 
AND schemaname NOT LIKE 'pg_temp%'
AND pg_user.usesuper != 't'
AND usename != viewowner
)
INSERT INTO _atp_non_super
(SELECT current_database() as DATABASE, schemaname as schemaname, viewname, usename,
(SELECT selparm || insparm || updparm || delparm as permissions FROM 
(SELECT 
CASE WHEN (select has_table_privilege(usename, schemaname ||'.' ||viewname, 'select') = 't' ) 
THEN 'select ' 
ELSE ' '
END selparm,
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||viewname, 'insert') = 't' ) 
THEN 'insert '
ELSE ' '
END insparm,
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||viewname, 'update') = 't' ) 
THEN 'update '
ELSE ' '
END updparm,
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||viewname, 'delete') = 't' ) 
THEN 'delete '
ELSE ' '
END delparm
) -- inner select case statements
) -- outer select concat 
FROM t ORDER BY 1,2,3); 


--get list of view names and superusers do not need to check permissions for superusers
WITH t as 
( SELECT schemaname,viewname, usename 
FROM pg_catalog.pg_views views, pg_user where 
schemaname NOT IN ('information_schema','pg_catalog','sys') 
AND schemaname NOT LIKE 'pg_temp%'
AND pg_user.usesuper = 't'
)
INSERT INTO _atp_super
(SELECT current_database() as DATABASE, schemaname as schemaname, viewname, usename, NULL
FROM t ORDER BY 1,2,3); 


--get list of views with owners
--
WITH t as 
( SELECT schemaname,viewname , viewowner 
FROM pg_catalog.pg_views views , pg_user where 
schemaname NOT IN ('information_schema','pg_catalog','sys') 
AND schemaname NOT LIKE 'pg_temp%'
)
INSERT INTO _atp_owner
(SELECT current_database() as DATABASE, schemaname as schemaname,
viewname, viewowner, NULL 
FROM t ORDER BY 1,2,3); 


--
-- remove view entries for views that have no defined permissions 
DELETE FROM _atp_non_super
WHERE trim(both ' ' from permissions) = '';
--
-- now deal with superuser 
--
--
TRUNCATE table _super_owner;
INSERT INTO _super_owner
SELECT super.database,super.schemaname, super.tablename, super.user, super.permissions
FROM _atp_super super
WHERE EXISTS
(SELECT 1 FROM _show_owner show
WHERE super.tablename = show.tablename
AND super.schemaname = show.schemaname
AND super.user = show.owner);

DELETE FROM _atp_owner owner
WHERE EXISTS
(SELECT 1 FROM _super_owner so
WHERE owner.schemaname = so.schemaname
AND owner.tablename = so.tablename
AND owner.user = so.user);

DELETE FROM _atp_super super
WHERE EXISTS
(SELECT 1 FROM _super_owner so
WHERE super.schemaname = so.schemaname
AND super.tablename = so.tablename
AND super.user = so.user);


--set permissionss for superuser entries

CREATE temporary table _t3a
as 
SELECT _atps.schemaname, _atps.tablename
FROM _atp_super _atps
WHERE EXISTS
(SELECT 1 FROM _atp_non_super _atpns
WHERE _atps.schemaname = _atpns.schemaname
AND _atps.tablename = _atpns.tablename
);

-- if one user record exists for view
-- only post superuser
-- otherwise post superuser only 
--
UPDATE _atp_super _atps
SET permissions = '** SUPERUSER **'
FROM _t3a t3a
WHERE t3a.tablename = _atps.tablename
AND t3a.schemaname = _atps.schemaname; 

UPDATE _atp_super _atps
SET permissions = '** SUPERUSER ONLY **'
WHERE permissions is NULL;

--
-- now owner 
--
TRUNCATE table _t3a;
INSERT INTO _t3a
SELECT _atpo.schemaname, _atpo.tablename
FROM _atp_owner _atpo
WHERE EXISTS
(SELECT 1 FROM _atp_non_super _atpns
WHERE _atpo.schemaname = _atpns.schemaname
AND _atpo.tablename = _atpns.tablename
);

UPDATE _atp_owner _atpo
SET permissions = '** OWNER **'
FROM _t3a t3a
WHERE t3a.tablename = _atpo.tablename
AND t3a.schemaname = _atpo.schemaname;

UPDATE _atp_owner _atpo
SET permissions = '** OWNER ONLY **'
WHERE permissions is NULL;

TRUNCATE table _t3a;
INSERT INTO _t3a
SELECT so.schemaname, so.tablename
FROM _super_owner so
WHERE EXISTS
(SELECT 1 FROM _atp_non_super _atpns
WHERE so.schemaname = _atpns.schemaname
AND so.tablename = _atpns.tablename
);

-- where owner is also superuser
--
UPDATE _super_owner so
SET permissions ='** SUPERUSER/OWNER **'
FROM _t3a t3a
WHERE t3a.tablename = so.tablename
AND t3a.schemaname = so.schemaname;

UPDATE _super_owner so
SET permissions = '** SUPERUSER/OWNER ONLY **'
WHERE permissions is NULL or permissions = ' ';

TRUNCATE table _dbonly;
INSERT INTO _dbonly select * FROM _atp_non_super LIMIT 1;

UPDATE _dbonly
SET schemaname= ' ', tablename = ' ', user = ' ', permissions = '** VIEWS **' ,seqnum = 0;

UPDATE _atp_non_super set database = ' ';
UPDATE _atp_owner set database = ' ';
UPDATE _atp_super set database = ' ';
UPDATE _super_owner set database = ' ';


INSERT INTO _all_permissions
VALUES (' ', ' ', ' ', ' ', ' '), (' ', ' ', ' ', ' ', ' ');

INSERT INTO _all_permissions 
SELECT database, ' ', ' ', ' ', '** VIEWS **' FROM _dbonly;
INSERT INTO _all_permissions
VALUES (' ', ' ', ' ', ' ', ' ');
INSERT INTO _all_permissions
SELECT a.database, a.schemaname, a.tablename,a.user, a.permissions
FROM 
(
SELECT * FROM _atp_non_super
UNION
SELECT * FROM _atp_owner
UNION
SELECT * FROM _atp_super
UNION
SELECT * FROM _super_owner
ODER BY database desc, schemaname, tablename, seqnum, user) a;

--
-- now materialized views
--
TRUNCATE table _atp_non_super;
TRUNCATE table _atp_super;
TRUNCATE table _atp_owner;
TRUNCATE table _t3a;
TRUNCATE table _super_owner;
TRUNCATE table _dbonly;
TRUNCATE table _show_owner;

INSERT INTO _show_owner
SELECT substring(schemaname,1,125), substring(matviewname,1,125), substring(matviewowner,1,125) 
FROM pg_catalog.pg_matviews
WHERE schemaname NOT IN ('pg_catalog','sys','information_schema') 
AND schemaname NOT LIKE '%pg_temp%';


--get list of materialized view with users notincluding superusers or view owner
--need to check permissions for these users
WITH t as 
( SELECT schemaname, matviewname as viewname, usename 
FROM pg_catalog.pg_matviews views, pg_user where 
schemaname NOT IN ('information_schema','pg_catalog','sys') 
AND schemaname NOT LIKE 'pg_temp%'
AND pg_user.usesuper != 't'
AND usename != matviewowner
)
INSERT INTO _atp_non_super
(SELECT current_database() as DATABASE, schemaname as schemaname, 
viewname, usename,
(select selparm || insparm || updparm || delparm as permissions from 
(SELECT 
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||viewname, 'select') = 't' )
THEN 'select ' 
ELSE ' '
END selparm,
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||viewname, 'insert') = 't' ) 
THEN 'insert '
ELSE ' '
END insparm,
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||viewname, 'update') = 't' ) 
THEN 'update '
ELSE ' '
END updparm,
CASE WHEN (select has_table_privilege(usename, schemaname || '.' ||viewname, 'delete') = 't' ) 
THEN 'delete '
ELSE ' '
END delparm
) -- inner select case statements
) -- outer select concat 
FROM t ORDER BY 1,2,3); 


--get list of mat views with users superusers do not need to check permissions for superusers
WITH t as 
( SELECT schemaname,matviewname as matviewname, usename 
FROM pg_catalog.pg_matviews views, pg_user WHERE 
schemaname nNOT IN ('information_schema','pg_catalog','sys') 
AND schemaname NOT LIKE 'pg_temp%'
AND pg_user.usesuper = 't'
)
INSERT INTO _atp_super
(SELECT current_database() as DATABASE, schemaname as schemaname, matviewname, usename, NULL
FROM t ORDER BY 1,2,3); 

--get list of with view owners do not need to check permissions for owners
WITH t as 
( SELECT schemaname, matviewname as matviewname, matviewowner as viewowner
FROM pg_catalog.pg_matviews views WHERE 
schemaname NOT IN ('information_schema','pg_catalog','sys') 
AND schemaname NOT LIKE 'pg_temp%'
)
INSERT INTO _atp_owner
(SELECT current_database() as DATABASE, schemaname as schemaname, matviewname, viewowner, NULL
FROM t ORDER BY 1,2,3);
--
-- remove matialized view entries that have no defined permissions 
--
DELETE FROM _atp_non_super
WHERE trim(both ' ' from permissions) = '';

-- now deal with superuser 

TRUNCATE table _super_owner;
INSERT INTO _super_owner
SELECT super.database,super.schemaname, super.tablename, super.user, super.permissions
FROM _atp_super super
WHERE EXISTS
(SELECT 1 FROM _show_owner show
WHERE super.tablename = show.tablename
AND super.schemaname = show.schemaname
AND super.user = show.owner);

DELETE FROM _atp_owner owner
WHERE EXISTS
(SELECT 1 FROM _super_owner so
WHERE owner.schemaname = so.schemaname
AND owner.tablename = so.tablename
AND owner.user = so.user);

DELETE FROM _atp_super super
WHERE EXISTS
(SELECT 1 FROM _super_owner so
WHERE super.schemaname = so.schemaname
AND super.tablename = so.tablename
AND super.user = so.user);

TRUNCATE table _t3;
INSERT INTO _t3
SELECT _atps.schemaname, _atps.tablename 
FROM _atp_super _atps
WHERE EXISTS
(SELECT 1 FROM _atp_non_super _atpns
WHERE _atps.schemaname = _atpns.schemaname
AND _atps.tablename = _atpns.tablename
);

UPDATE _atp_super _atps
SET permissions = '** SUPERUSER **'
FROM _t3 t3
WHERE t3.tablename = _atps.tablename 
AND t3.schemaname = _atps.schemaname;


UPDATE _atp_super _atps 
SET permissions = '** SUPERUSER ONLY **'
WHERE permissions is NULL;
--
-- now owner
--
TRUNCATE table _t3;
INSERT INTO _t3
SELECT _atpo.schemaname, _atpo.tablename 
FROM _atp_owner _atpo
WHERE EXISTS
(SELECT 1 FROM _atp_non_super _atpns
WHERE _atpo.schemaname = _atpns.schemaname
AND _atpo.tablename = _atpns.tablename
);

UPDATE _atp_owner _atpo
SET permissions = '** OWNER **'
FROM _t3 t3
WHERE t3.tablename = _atpo.tablename 
AND t3.schemaname = _atpo.schemaname;

UPDATE _atp_owner _atpo 
SET permissions = '** OWNER ONLY **'
WHERE permissions is NULL;

--
-- by default any users with permissions null 
-- have no uer permissions on them
-- set to superuser only
--
--
TRUNCATE table _t3;
INSERT into _t3
SELECT so.schemaname, so.tablename 
FROM _super_owner so
WHERE EXISTS
(SELECT 1 FROM _atp_non_super _atpns
WHERE so.schemaname = _atpns.schemaname
AND so.tablename = _atpns.tablename
);

UPDATE _super_owner so
SET permissions = '** SUPERUSER/OWNER **'
FROM _t3 t3
WHERE t3.tablename = so.tablename 
AND t3.schemaname = so.schemaname;

--
-- by default any users with permissions null 
-- have no uer permissions on them
-- st to superuser only
--
UPDATE _super_owner
SET permissions = '** SUPERUSER/OWNER ONLY **'
WHERE permissions is NULL;


TRUNCATE table _dbonly;
INSERT INTO _dbonly (SELECT * FROM _atp_super LIMIT 1);
UPDATE _dbonly 
SET schemaname = ' ', tablename= ' ', user = ' ', 
permissions = '** MATERIALIZED VIEWS **',seqnum = 0;


UPDATE _super_owner SET seqnum = 1;
UPDATE _atp_super SET seqnum = 2;
UPDATE _atp_owner SET seqnum = 3;
UPDATE _atp_non_super SET seqnum = 4;

UPDATE _atp_non_super SET database = ' ';
UPDATE _atp_owner SET database = ' ';
UPDATE _atp_owner SET database = ' ';
UPDATE _atp_super SET database = ' ';
UPDATE _super_owner SET database = ' ';

INSERT INTO _all_permissions
VALUES (' ', ' ', ' ', ' ', ' '), (' ', ' ', ' ', ' ', ' ');
INSERT INTO _all_permissions
SELECT database, ' ', ' ', ' ', permissions FROM _dbonly;
INSERT INTO _all_permissions
VALUES ( ' ', ' ', ' ', ' ',' ');
INSERT INTO _all_permissions
SELECT a.database, a.schemaname, a.tablename ,a.user, a.permissions
FROM 
(
SELECT * FROM _atp_non_super
UNION
SELECT * FROM _atp_owner
UNION
SELECT * FROM _atp_super
UNION
SELECT * FROM _super_owner
ORDER BY database desc, schemaname, tablename, seqnum, user) a;
--
-- drop work tables
DROP table _atp_non_super;
DROP table _atp_super;
DROP table _atp_owner;
DROP table _t3;
DROP table _t3a;
DROP table _show_owner;
DROP table _super_owner;
DROP table _dbonly;
raise notice ' ';
raise notice 'Processing complete';
raise notice ' To see the results issue ''SELECT * FROM _all_permissions''';
END; 

$$

Was this article helpful?

0 out of 0 found this helpful