This article is a Private article. Only a writer and users who know the URL can access it.
Please change open range to public in publish setting if you want to share this article with other users.

More than 1 year has passed since last update.

PostgreSQLでテーブルへのアクセス権限を確認する

PostgreSQL

Posted at 2023-04-27

has_table_privilege を使うのがキモ。

テーブルへのアクセス権限の確認

select
    u.usename,
    t.schemaname || '.' || t.tablename,
    (
        select
            current_user
    ) as check_user,
    has_table_privilege(u.usename, t.tablename, 'select') AS user_has_select_permission,
    has_table_privilege(u.usename, t.tablename, 'insert') AS user_has_insert_permission,
    has_table_privilege(u.usename, t.tablename, 'update') AS user_has_update_permission,
    has_table_privilege(u.usename, t.tablename, 'delete') AS user_has_delete_permission,
    has_table_privilege(u.usename, t.tablename, 'references') AS user_has_references_permission
FROM
    pg_user u
    CROSS JOIN pg_tables t
WHERE
    u.usename = 'usernameを入れる'
    and tablename in ('テーブル名を入れる');

ユーザの一覧

select * from pg_user;

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up