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.
-
Create the procedure included below in each database where you want to get this information.
-
Call the procedure to generate the output, using on of the following two methods:
-
Call via psql and review output in same session:
psql -d DBNAME -U USERID
CALL all_table_view_permissions();
SELECT * FROM _all_permissions;
\q - 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;
$$