Results 1 to 6 of 6
  1. #1
    Join Date
    May 2014
    Posts
    16

    Unanswered: Finding Users Permissions on tables

    I'm trying to put together a list of all my user in my production database, and see what access they have on certain tables. I found the following script, but I'm not sure how to make out the permissions column it's returning. Can someone explain to me what the how to read column 'relacl', does the '/' represent each user?

    Or does anyone know a different way I can pull all users and see what tables they have permission to, and also see what those permissions are?

    Code:
    SELECT relname as  "Relation"
          ,relacl  as  "Access permissions"
    
    FROM pg_class
    
    WHERE  relkind IN ('r')
    
    AND relname !~ '^pg_'
    
    ORDER BY relname;

  2. #2
    Join Date
    Dec 2012
    Posts
    43
    Sorry, I don't know a different way ...

    ... but I can explain something about "relacl":

    "relacl" is not a simple value but an array (!).

    You can see it by "{" and "}" in the output of your SELECT statement.

    An access cotrol entry is:
    USERNAME=ACCESS_RIGHTS/OWNER

    USERNAME: username (grantee)
    ACCESS_RIGHTS: r=select, w=update, d=delete, a=insert etc
    OWNER: Owner of the object (table)

    Every access control entry is seperated by a ","

    Output for my table "t1" is:
    Code:
    t1                      | {thomas=arwdDxt/thomas,jane=r/thomas,john=arw/thomas}
    "thomas=arwdDxt/thomas": user "thomas" has all rights (of course it's the owner of the table)
    "jane=r/thomas": user "jane" has SELECT right
    "john=arw/thomas": user "john" has SELECT, UPDATE, INSERT rights

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jm_green84 View Post
    I Can someone explain to me what the how to read column 'relacl', does the '/' represent each user?
    Yes, the manual can:
    Quote Originally Posted by the manual
    relacl Access privileges; see GRANT and REVOKE for details
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    May 2014
    Posts
    16
    Thanks definitely helps. I was always studying this PostgreSQL: Documentation: 9.1: pg_class

    Quote Originally Posted by bibjet View Post
    Sorry, I don't know a different way ...

    ... but I can explain something about "relacl":

    "relacl" is not a simple value but an array (!).

    You can see it by "{" and "}" in the output of your SELECT statement.

    An access cotrol entry is:
    USERNAME=ACCESS_RIGHTS/OWNER

    USERNAME: username (grantee)
    ACCESS_RIGHTS: r=select, w=update, d=delete, a=insert etc
    OWNER: Owner of the object (table)

    Every access control entry is seperated by a ","

    Output for my table "t1" is:
    Code:
    t1                      | {thomas=arwdDxt/thomas,jane=r/thomas,john=arw/thomas}
    "thomas=arwdDxt/thomas": user "thomas" has all rights (of course it's the owner of the table)
    "jane=r/thomas": user "jane" has SELECT right
    "john=arw/thomas": user "john" has SELECT, UPDATE, INSERT rights

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jm_green84 View Post
    Thanks definitely helps. I was always studying this PostgreSQL: Documentation: 9.1: pg_class
    Which does contain the link to the GRANT and REVOKE statements I have posted
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  6. #6
    Join Date
    Dec 2012
    Posts
    43
    Hello jm_green84,

    to avoid searching for access rights of all users in all databases there is a better solution:

    Use groups (as ROLEs in postgresql) and grant the users to the roles.

    If a user has access rights anywhere in any database it's not possible to drop this user with SQL statement DROP ROLE ... .
    You have to revoke all access rights before you can drop this user.


    If you use groups instead of access rights for every single user you only have to revoke this user from the groups and then you can drop this user.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •